Every model has a moment of dishonesty: the growth rate says 5%, and everyone in the room knows it might be 2% or 8%. The forwards answer (“£412k”) is precise about an assumption that isn’t. Goal Seek asks one backwards question; this lesson’s tool answers the braver one — what does the answer look like across the whole range of the assumption?

A Data Table (Data → What-If Analysis → Data Table — no relation to proper Tables, an unfortunate naming collision) reruns your entire model once per candidate value and lays the answers out as a grid.

One variable: the sensitivity column

Take the pension model: growth feeds a pot_at_60 result. Down a column, type the candidate rates: 2%, 3% … 8%. In the cell one up and one right of the first rate, reference the result: =pot_at_60. Select the block, Data Table, Column input cell: growth, OK.

Excel now runs the whole model seven times — once per rate, restoring everything afterwards — and fills the column: the pot at every growth assumption, on one screen. The honest answer to “what will it be worth?” turns out to be a shape, not a number — and now you can show the shape.

Two variables: the grid

The famous version. Rates down the left, years across the top, and in the corner cell where they meet, the reference to the result. Select the whole block → Data Table → Row input: years, Column input: growth:

pot at retirement — growth × years, every combination =pot_at_60 15 yrs 20 yrs 25 yrs 3% 5% 7% £248k £311k £384k £293k £396k £528k £347k £507k £742k the model ran nine times; the grid is the honest answer — a range you can plan around, not a point estimate to defend
The corner holds the formula; the headers hold the candidates; Excel fills the middle. "It depends" — quantified, on half a screen.

Nine model runs, one grid, and the conversation changes: instead of defending 5%, you point at the row where the plan still works at 3% — or concede that it doesn’t, which is worth knowing now. This grid under the overpay-vs-invest comparison (rate × return) or a break-even model (price × volume) is the single most persuasive screen a spreadsheet produces — assumptions on the table, literally.

Add a colour scale across the grid — this is the one place the green-to-red gradient genuinely earns its living, because the comparison across a smooth surface is exactly what you’re asking the reader to see.

The fine print

Data Tables have personality; know three things. The input cells must be on the same sheet as the table — a quirk, and the practical answer is to build sensitivity grids on the model’s sheet or mirror the input locally. The results are array magic, not formulas you can edit — {=TABLE(...)} fills the cells; change the candidates or the model and it recalculates; to remove it, delete the whole block. Big tables can be slow — each cell is a full model run, and a 20×20 grid on a heavy workbook is 400 recalculations (the classic cure if it drags: Formulas → Calculation Options → Automatic Except for Data Tables, then F9 when you want the grid refreshed — a switch worth knowing exists before the performance lesson formalises it).

And the architecture rule, one more time, because this tool depends on it completely: a Data Table can only vary an input cell that exists. Models with named, single-home assumptions get sensitivity analysis for free; models with rates buried in formulas get nothing. Every lesson in this stage keeps landing on the same door: put the knobs where hands can reach them.

One formula, two headers, one fill. The difference between “my model says £396k” and “here’s how the answer moves when the world does” — and the second one is the only version a careful reader trusts.