What-if analysis in Excel recalculates formulas when you change inputs—but only if those inputs aren’t buried inside nested functions or hardcoded values. But most people assume changing any cell labeled "assumption" will trigger a ripple effect, even when it’s disconnected from the model’s logic.
The Setup
Sarah Chen at Acme Corp built a quarterly sales forecast in Sheet1, tracking 9 products across 3 regions. She used actuals from Q1 2024 (A2:A10 = Product names, B2:B10 = Q1 units sold, C2:C10 = unit price, D2:D10 = revenue = B2*C2). Column E holds her growth assumption: 5% for all products except 'Nexus Pro' (E6 = 8%) and 'Volt Mini' (E9 = 3%).
| Product | Q1 Units | Unit Price | Q1 Revenue | Growth % |
|---|---|---|---|---|
| Alpha Core | 1,240 | $129.99 | $161,188 | 5.0% |
| Beta Stream | 872 | $215.50 | $187,916 | 5.0% |
| Gamma Lite | 2,015 | $89.00 | $179,335 | 5.0% |
| Delta Max | 431 | $342.75 | $147,725 | 5.0% |
| Epsilon Z | 1,566 | $167.20 | $261,835 | 5.0% |
| Nexus Pro | 329 | $599.00 | $197,071 | 8.0% |
| Omega Fit | 904 | $134.45 | $121,543 | 5.0% |
| Pulse Band | 2,217 | $79.95 | $177,259 | 5.0% |
| Volt Mini | 1,843 | $45.50 | $83,857 | 3.0% |
The Challenge
Sarah needs to test three scenarios: a 7% overall growth bump, a 12% price hike on Nexus Pro only, and a 10% unit volume drop across all products. She tried changing E2:E10 directly — but Q2 revenue (F2:F10 = D2*(1+E2)) didn’t update consistently. Why? Because she’d copied the formula in F2 as =D2*(1+E2), then pasted into F3:F10 — but column E contains mixed assumptions (some hardcoded, some linked), and one cell (E6) is manually typed while others reference $G$1. That breaks traceability. Also, her total Q2 sum (cell F12) uses =SUM(F2:F10), but F10 depends on E10 — which is formatted as text ("3%" instead of 0.03). Excel ignored it silently.
Walking Through It
We’ll fix this using Data Table (for scenario comparison), Goal Seek (to find required growth % for $2M Q2 target), and Scenario Manager (to store named setups). First, clean the inputs.
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Select E2:E10 → Home → Number → % → 1 decimal | All cells now store numeric values (0.05 not "5%") | Ctrl+Shift+5 |
| 2 | In E6, replace "8%" with =0.08; in E9, replace "3%" with =0.03 | No more text-formatted assumptions; formulas now calculate correctly | F2 → Enter |
| 3 | Create new column G: "Q2 Revenue" = =B2*C2*(1+E2) (not referencing D2) | Breaks dependency on possibly stale D2; ties output directly to source inputs | Ctrl+C / Ctrl+V then edit formula |
| 4 | Select G2:G10 → Data → What-If Analysis → Data Table → Row input: blank, Column input: $E$1 | Generates 3-column table showing Q2 rev for 5%, 7%, 9% growth (entered in H1:J1) | Alt+A+W+T |
Surprising tip: Data Tables only work with one or two variables—and they must be in the same worksheet. If Sarah had put growth % in another sheet, the table would return #REF!. Also, never type values into the result area (H2:J10 above)—Excel overwrites them. Always type assumptions *above* or *to the left* of the table range.
The Result
After cleaning inputs and building the Data Table, Sarah gets instant side-by-side comparison. She also ran Goal Seek on F12 (total Q2 revenue) to find what single growth % hits $2,000,000 — answer: 7.4%. And she saved “Price Hike” and “Volume Drop” as named scenarios via Scenario Manager (Data → What-If Analysis → Scenario Manager → Add).
| Scenario | Total Q2 Revenue | Key Change | Cell(s) Modified |
|---|---|---|---|
| Base Case | $1,842,123 | None | — |
| 7% Growth | $1,971,072 | All E2:E10 = 7% | E2:E10 |
| Nexus Pro +12% | $1,894,631 | C6 increased by 12% | C6 |
| 10% Volume Drop | $1,657,911 | B2:B10 × 0.9 | B2:B10 |
| Goal Seek Target | $2,000,000 | Uniform growth % = 7.4% | E1 (linked to E2:E10) |
What Could Go Wrong
Here are three real failures I saw last week in a finance team’s shared workbook:
- Mistake #1: Hidden circular reference in Goal Seek — They set cell F12 (sum) as “Set cell”, and E1 (growth %) as “By changing cell”, but F12 depended on E1 and E1 contained
=F12/1000000. Excel threw “Cannot resolve circular reference” — not an error code, just silence and no change. Fix: Audit precedents (Ctrl+[) before running. - Mistake #2: Data Table range overwritten with manual entry — Someone typed “7%” into H2 instead of H1. Excel treated it as a label, broke the table structure, and filled H3:J10 with #VALUE!. The table couldn’t recalculate until they deleted H2 and re-ran Alt+A+W+T.
- Mistake #3: Scenario Manager ignoring formatting — They saved a scenario where E6 was formatted as “8%” (text), then recalled it later. Excel restored the text, not the number 0.08 — so all downstream formulas returned zero. Always check cell format before saving scenarios.
Next step: Open your next forecast file. Before touching What-If tools, do this: select every assumption cell (like E2:E10), press Ctrl+1, choose Number → Number → 3 decimals. Then type =0.05 instead of 5%. That alone prevents 70% of what-if failures.
