Excel for the financial analyst

Last updated November 202614 sectionsabout 30 min read

Financial Modelling and Data Analytics is sat at a computer. Section I is twenty one-mark multiple choice questions, and Sections II and III are practicals worth 80 marks between them, answered in an Excel workbook on worksheets named after the entity in each question. The examiner opens that workbook and reads it cell by cell: a correct figure typed in as a number earns less than the same figure produced by a formula that can be traced back to the inputs. Working quickly, laying the sheet out so a marker can follow it, and knowing the tools without hunting through the ribbon are therefore worth marks in their own right.

Section I has examined the environment directly. April 2024 asked for the shortcut that opens Format Cells; August 2024 for the shortcut that inserts a new worksheet; December 2024 for the shortcut that opens the Macro dialog; December 2025 for the key that makes a reference absolute, for the colour convention that separates inputs from formulas, and for the reason analysts use named ranges. April 2025 and April 2026 both asked which feature summarises a large dataset (the PivotTable), and the April 2026 practical asked for a dashboard built from PivotTables and slicers.

This chapter sets up the analyst's workbench: the exam workbook, the keyboard shortcuts, references and names, number formats for financial reports, colour conventions, Excel Tables, sorting and filtering, PivotTables, auditing, printing and the differences between Excel versions. The functions themselves follow in the chapter on functions for financial models, and the what-if tools in the chapter on what-if analysis tools.

Key terms

The exam workbook

Every practical in this paper opens with the same instruction: create Excel worksheets with the name of the entity in the question. April 2024 Q21 therefore lives on a sheet called Makupa Cement, December 2024 Q22 on Beacon Bank, April 2025 Q24 on Safeguard Investments. Double-click the sheet tab (or right-click and choose Rename), type the name and press Enter before you type a single number. A sheet named Sheet3 forces the marker to guess which answer it holds.

Lay each sheet out in three blocks that run down the page: the inputs the question gives you, the calculations, and the outputs and interpretation. Label every block and every question part in column A, so the marker can find (b)(ii) without scrolling sideways. Copy the question's data into the input block once and make every calculation read from it; never retype a given figure inside a formula. When the question supplies a data workbook, as the 2024-2026 sittings did for price histories and financial statements, keep the given cells where they are and build beside them, because the question's own cell references (such as dates in B10:B33) then still point to the right place.

• Sheet names Up to 31 characters; none of the characters \ / ? * [ ] or the colon; not blank; and History is reserved. Makupa Cement is fine, Makupa Cement Ltd: Forecast is not because of the colon.
• One question, one sheet If a question has two entities (Mwananchi and Taifa in the August 2024 merger question), use the acquirer's or the question's main entity as the sheet name and label both inside, unless the question asks for two sheets.
• Save as you go Ctrl+S every few minutes. Save in the format and location the invigilator gives; keep the default .xlsx, which cannot carry macros and opens on any marker's machine.
• No hidden work Do not hide rows that hold workings. A marker who cannot see a working cannot award the method mark for it.

Keyboard shortcuts the paper expects

The syllabus names keyboard shortcuts as a skill, and Section I tests them in most sittings, as listed above. In the practicals, shortcuts are simply speed: an analyst who selects a 60-row schedule with Ctrl+Shift+Down and fills it with Ctrl+D finishes the part while another is still dragging the fill handle. Learn the tables below by purpose.

Navigation and selection

Editing and entry

Formatting

Formulas, auditing and calculation

Worksheets, workbooks and tools

Alt key sequences

Pressing Alt shows a letter over every ribbon tab; keep typing the letters shown to reach any command without the mouse. The sequences below reach the tools this paper uses most. They are typed one key after another, not held together.

Relative, absolute and mixed references

When a formula is copied, Excel moves every reference by the distance the formula moved, unless a dollar sign fixes the column, the row or both. The whole point of a model is that one formula, written once, can be filled across five forecast years or down sixty loan periods; the dollar signs decide whether that works. A tax rate or a discount rate held in one input cell must be absolute in every formula that reads it, or the copied formulas drift off to empty cells and return zero.

Press F4 while the cursor is in a reference to cycle it: B4 becomes $B$4, then B$4, then $B4, then back to B4. December 2025 Q11 asked which key creates an absolute reference; the answer is F4. A related MCQ in April 2026 asked which revenue forecast formula best keeps a model flexible when revenue sits in B5 and the growth rate in B2: =B5*(1+B2), because it reads the growth input. =B5*1.08 buries the rate in the formula and =12000000*(1+8%) buries both figures, so neither updates when the assumption changes.

B5:  =1/(1+B$4)^$A5      copied to B5:D9
D9:  =1/(1+D$4)^$A9      returns 0.519369
Wrong: =1/(1+B4)^A5      copied right, the year drifts to B5, C5...
Wrong: =1/(1+$B$4)^$A5   copied right, every column uses 10%

Named ranges and the Name Manager

A name replaces a cell address with a word. =PMT(Rate/12,Term*12,-Loan) explains itself to a reviewer in a way that =PMT(B4/12,B5*12,-B3) does not, and a name is always absolute, so it never drifts when copied. December 2025 Q17 asked the main reason for using named ranges; the answer is that they improve readability and reduce formula errors. They do not shrink the file, speed calculation or protect cells.

Names are best used sparingly, for the handful of key drivers a model turns on: the discount rate, the tax rate, the loan amount. August 2024 Q15 asked for the best way to make a model easier to audit and offered 'name all cells within the model' as a distractor; the correct answer was to separate assumptions from formulas. A model with hundreds of names is as hard to trace as one with none, because the reader must open the Name Manager to find where each name points.

• Name Box Select the cell, click in the Name Box at the left of the formula bar, type the name, press Enter.
• Create from Selection (Ctrl+Shift+F3) Select a block of labels and values (A3:B5), choose Left column, and every value is named after its label in one step.
• Name Manager (Ctrl+F3) Lists every name with its value, the range it refers to and its scope; edit or delete names here.
• Paste Name (F3) While typing a formula, lists the names so you can insert one.

A name must start with a letter, an underscore or a backslash, contain no spaces (use Tax_rate or TaxRate), and must not look like a cell address: TAX1 and KE2026 are rejected because columns TAX and KE exist. Names are not case sensitive. Each has a scope: workbook scope (usable from any sheet, the default) or a single sheet's scope, which lets two entity sheets each hold their own Rate.

Number formats for financial reports

A financial report is read in thousands or millions, with negatives in brackets and percentages to one decimal. Number formats do this without touching the value stored in the cell, which is exactly what a model needs: the figure keeps its full precision for later calculations, and only the display changes. Open Format Cells with Ctrl+1, choose Custom and type the code. A custom code has up to four sections separated by semicolons: positive; negative; zero; text.

State the unit in the heading of every block (Sh. million) when you scale with commas, because the cell shows 12.3 while holding 12,345,678. Excel's own Accounting format (Ctrl+1, Accounting, symbol None) also aligns figures and shows zero as a dash, but brackets for negatives need a custom code.

Colour conventions and cell styles

A reviewer must see at a glance which cells may be changed and which are calculated. The convention used across investment banking and corporate finance models is blue font for hard-coded inputs, black font for formulas and green font for links to other sheets or workbooks. December 2025 Q9 asked for the best practice in distinguishing inputs from formulas; the answer was blue font for inputs and black for formulas. Bold, italics and fills are used for structure (totals, headings, checks), not to mark inputs.

Excel ships with built-in cell styles that follow the same idea (Home > Cell Styles): Input, Calculation, Output, Check Cell, Linked Cell, Note and Warning Text. Applying the Input style to every assumption cell, and nothing else, is a quick way to make the structure visible. More on checks, alerts and model review is in the chapter on model design, checks and review.

Excel Tables and structured references

Select any cell in a list with headers and press Ctrl+T. The range becomes an Excel Table with a name (rename it on the Table Design tab), banded rows and filter buttons. Three things make Tables valuable in a model. A formula typed in one row of a new column fills the whole column automatically (a calculated column). The Table grows when you type a new row beneath it, so every formula and chart that reads it includes the new row. And formulas can refer to columns by name instead of by address.

A column name with a space or special character is wrapped in a second pair of brackets, as [@[Clean price]] shows. One limitation matters for this paper: a data table (Data > What-If Analysis > Data Table) and a dynamic array formula cannot be placed inside an Excel Table, so keep sensitivity tables and spilled results on the open grid beside it.

Sorting, filtering and subtotals

Sorting and filtering are the quickest ways to interrogate a list of securities or loans without writing a formula. Data > Sort sorts by up to 64 levels (sector, then market capitalisation, largest first); the filter buttons (Ctrl+Shift+L) hide the rows that fail a condition, such as P/E under 15 or days in arrears over 90. Number Filters offer greater than, between and Top 10; Text Filters offer contains and begins with.

• Sort the whole list Select one cell in the list, not one column, or Excel sorts that column alone and scrambles the rows.
• Filtered totals =SUBTOTAL(109,D5:D16) sums only the visible rows; =SUM(D5:D16) still sums the hidden ones. AGGREGATE(9,5,D5:D16) does the same, and AGGREGATE(9,7,D5:D16) also skips error values.
• Advanced Filter Data > Advanced copies the rows that meet a criteria range (headers plus conditions; conditions on one row are AND, on separate rows OR) to another place on the sheet. It is static: rerun it when the data changes.
• The formula alternative FILTER returns the matching rows as a live result that updates on its own; December 2025 Q1 asked for the best way to find shares with P/E below 15 and P/B below 2, and the answer was the FILTER function. It is taught in the chapter on functions for financial models.

PivotTables for quick analysis

A PivotTable summarises a list by dragging fields rather than writing formulas. April 2025 Q19 asked which feature creates summary reports and cross-tabulations, and April 2026 Q17 which feature summarises large datasets quickly; both answers were PivotTables. December 2025 Q2 added that PivotTables with slicers give a dynamic KPI dashboard with drill-down, and the April 2026 practical asked for exactly that dashboard on cash flows and NPV outcomes.

1. Convert the source list to an Excel Table (Ctrl+T) so the PivotTable picks up new rows on refresh.
2. Insert > PivotTable, choose New Worksheet or a cell on the entity sheet.
3. Drag a category field (Branch) to Rows, another (Product) to Columns, and the amount (Balance) to Values, where it is summed by default.
4. Right-click a value > Value Field Settings to change Sum to Count, Average, Max or Min; Show Values As gives % of Grand Total, % of Row Total or Difference From.
5. PivotTable Analyze > Insert Slicer adds clickable filter buttons; a slicer can drive several PivotTables at once through Report Connections.
6. Refresh (Alt+F5, or Data > Refresh All) after the source data changes. A PivotTable does not recalculate on its own.

Development loans are 84% of the book (5,420,000 of 6,450,000), so the Sacco's credit risk sits almost entirely in one product. Add Days in arrears to Filters and pick the loans over 90 days, and the PivotTable shows the non-performing book; the chapter on functions for financial models reproduces the same figures with SUMIFS, which is the right tool when the answer must feed a ratio in a live model. A formula that reads a PivotTable should use GETPIVOTDATA, which Excel writes for you when you click a pivot cell while building a formula, so the link survives the layout changing. Dashboard design is covered in the chapter on data visualisation and dashboards.

Freeze panes, grouping and printing

• Freeze panes Click the cell below the header row and to the right of the label column (B5 on a schedule with labels in column A and headers in row 4), then View > Freeze Panes (Alt, W, F, F). Labels and years stay visible while you scroll a 60-row amortisation schedule.
• Split View > Split divides the window into panes that scroll separately, useful for watching an input at the top while editing a calculation far below.
• Group rather than hide Data > Group (Alt+Shift+Right) collapses detailed workings behind a plus button; the marker can expand them, which hiding does not invite.
• Print area and fit Page Layout > Print Area > Set Print Area, then Fit to 1 page wide in Scale to Fit. Print Titles repeats the header rows on every page. Page Break Preview shows where pages split.
• Printing formulas Ctrl+` then print to produce a formula listing for review; switch back before saving so the marker sees results.

Auditing a worksheet

Before you leave a question, spend a minute auditing it. Formulas > Trace Precedents (Alt, M, P) draws arrows from the cells a formula reads; Trace Dependents shows what reads it; Evaluate Formula steps through a long formula one calculation at a time; the Watch Window keeps an eye on an output while you edit inputs elsewhere. Go To Special > Constants, with only Numbers ticked, selects every hard-coded number on the sheet, which should be the input block and nothing else.

A circular reference arises when a formula depends, directly or through a chain, on its own result. December 2024 Q20 asked what Excel's circular reference warning indicates: that a formula depends on its own output. Some finance models are deliberately circular, such as interest charged on the average cash balance, which itself depends on the interest; these need File > Options > Formulas > Enable iterative calculation and a circuit-breaker switch. The chapter on model design, checks and review treats them properly; in an exam answer, avoid them unless the question requires one.

Excel versions and the exam machine

The syllabus asks candidates to have Excel 2010 or later, and examination centres do not all run the same version. Excel 2021 is the default throughout this guide. Several functions that newer guides take for granted do not exist in older versions, and typing one on an older machine returns #NAME?. Learn the older equivalent for anything you rely on.

Try it: setting up the Simba Cement sheet