In plain English
Excel is still where consulting work is delivered. Speed comes from never touching the mouse, and credibility comes from a model someone else can follow without asking you what a cell means.
The advanced view
Treat a workbook as software: separate inputs, calculations and outputs; keep one formula per row consistent across all columns; avoid hard-coded constants inside formulas; and make every assumption traceable to a single cell. A model that cannot be audited will not be trusted, however correct it is.
Layout first. Three zones: inputs (blue font, one cell per assumption, units labelled), calculations (black, left to right in time), outputs (the two or three numbers the client actually reads). Never repeat an input in two places. Never mix a constant into a formula — 'Revenue × 1.03' hides a growth assumption nobody can find. One formula, copied cleanly across the row, is both faster to build and far easier to check.
Keyboard navigation that saves hours
Ctrl + arrows / Ctrl+Shift+arrows
Jump to the edge of a data block, or select to it. The core movement of fast modelling.
F2, F4, F9
Edit in place; cycle absolute/relative references; evaluate the selected part of a formula.
Ctrl + [ and Ctrl + ]
Trace precedents and dependents to find where a number comes from.
Alt + = , Alt + ;
AutoSum, and select visible cells only when rows are filtered or grouped.
Ctrl+Shift+{ and F5 → Special
Select all precedents, or jump to constants, formulas and blanks to audit a sheet.
Alt + N + V / Alt + H + 0
Ribbon key sequences: build a pivot, insert a row. Learn the four you use daily.
The functions that matter
INDEX(range, MATCH(key, keys, 0)) — robust two-way lookup XLOOKUP(key, keys, values, "n/a") — modern replacement, exact-match by default VLOOKUP breaks when columns move; INDEX/MATCH and XLOOKUP do not SUMIFS / COUNTIFS / AVERAGEIFS — aggregate by criteria instead of nested IFs IFERROR(value, "check") — trap errors without hiding them OFFSET / INDIRECT — powerful, volatile, and unauditable: avoid Data → What-If → Data Table — one- and two-way sensitivity on any output Named ranges: WACC, TaxRate, ExitMultiple — formulas read like the model, not like coordinates
Worked example: a two-way sensitivity table
Step 1 of 10
- 1Model output: equity value per share in cell B40 =
Circular references are the classic modelling trap: interest depends on average debt, debt depends on cash flow, cash flow depends on interest. Three ways out. Use opening-balance interest, which is simple, defensible and almost always accurate enough. Or enable iterative calculation, which resolves it but makes the model fragile and hard to audit. Or break the loop with a small manual convergence — a copy-paste of the interest figure. Say which one you chose and why; interviewers ask.
Must know cold
- ✓Inputs blue, formulas black, links to other sheets green — the standard colour convention.
- ✓One formula per row, consistent across every column. Inconsistency is the top source of model error.
- ✓No hard-coded numbers inside formulas; every assumption is its own labelled cell.
- ✓F9 on a selected fragment evaluates just that fragment — the fastest debugging tool in Excel.
- ✓Label units everywhere: SEKm, %, x, days. Unit confusion causes more errors than bad maths.
- ✓Build a check row: assets − liabilities − equity = 0, and cash flow ties to the balance-sheet cash movement.
Common pitfalls
- ×VLOOKUP with an approximate match left on by default, returning silently wrong values.
- ×Wrapping everything in IFERROR so genuine broken links look fine.
- ×Merged cells, which break sorting, selection and every keyboard shortcut.
- ×Hard-coding a plug to make the balance sheet balance instead of finding the error.
- ×Sending a workbook with iterative calculation on and no note explaining the circularity.
Essential vocabulary
- Named range
- A label assigned to a cell or range so formulas read =EBITDA*ExitMultiple.
- Data table
- Built-in one- or two-way sensitivity analysis that re-runs the model per cell.
- Volatile function
- A function like OFFSET or INDIRECT that recalculates constantly and defeats dependency tracing.
- Check row
- A formula that must equal zero; the model's own unit test.
- Plug
- A hard-coded number inserted to force a balance. Always a bug, never a solution.
Strategy connection
The model is the argument. A clean workbook with named assumptions lets a client challenge one number and see the consequence immediately, which turns a debate about your conclusion into a debate about their assumptions — the position you want to be in.
Exercises
Try each one on paper before revealing the worked solution.
Exercise 1
You inherit a workbook where the balance sheet is out by 14 in year 3 only. Give the diagnostic sequence.
Exercise 2
Rewrite VLOOKUP(A2, Data!A:F, 5, TRUE) safely, and say what it was doing wrong.
Exercise 3
Structuring exercise: you have four hours to build a model that answers whether a client should close one of three plants. What do you build?