Yes, Excel lets you run what-if scenarios. But if you’re still manually changing inputs and writing down results in a notebook, you’ve missed the entire point of Excel’s built-in scenario engine.
Quick Answer
Excel has three native what-if tools: Scenario Manager (for named, saved input combinations), Data Tables (for automatic output grids across one or two variables), and Goal Seek (for reverse-engineering a single input to hit a target). None require formulas to be rewritten — and all update instantly when source data changes.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Scenario Manager | Data > What-If Analysis > Scenario Manager > Add → name inputs (e.g., B2, C5), enter values, OK | Comparing 3–7 distinct business cases (e.g., Budget Cut, Growth Plan, Reorg) | Can’t handle >32 changing cells; no visual dashboard; summary report is static |
| One-Variable Data Table | Enter formula referencing input cell (e.g., =B10), list inputs vertically (D2:D12), select D1:E12, Data > What-If Analysis > Data Table → Column input: $B$2 | Testing how profit changes as discount % varies from 5% to 25% | Only works with formulas that reference exactly one input cell directly |
| Two-Variable Data Table | Formula in top-left (F1), row inputs left-to-right (G1:K1), column inputs top-to-bottom (F2:F8), select F1:K8, Data > What-If Analysis > Data Table → Row input: $B$3, Column input: $B$2 | Pricing matrix: profit vs. unit price AND volume sold | Formula must depend on exactly two inputs; can’t mix row/column references mid-table |
| Goal Seek | Data > What-If Analysis > Goal Seek → Set cell (e.g., B10), To value (e.g., 125000), By changing cell (e.g., B4) | Finding breakeven sales volume, required markup %, or loan term | Only solves for one variable at a time; fails silently if no solution exists |
| FORMULATEXT + OFFSET trick | =OFFSET($B$2,0,0,1,1) wrapped in IF() or CHOOSE(), then use Data Validation dropdown to switch between inputs | Live dashboards where users pick scenarios from a menu (no ribbon clicks needed) | Requires careful cell locking; breaks if sheet structure changes |
Method 1 Deep Dive
Let’s say Sarah Chen (Finance, Acme Corp) needs to compare three hiring plans: No Hire, Mid-Level Only, and Senior + Mid. Her model lives in Sheet1, with salary assumptions in B2 (base salary), B3 (bonus %), and B5 (headcount).
She opens Data > What-If Analysis > Scenario Manager. Clicks Add. Names it "No Hire", selects B2,B3,B5 as changing cells, and enters 85000, 8%, 0. Repeats for "Mid-Level Only" (92000, 10%, 2) and "Senior + Mid" (135000, 15%, 4).
Here’s the surprise: When she clicks Summary, Excel auto-generates a new sheet listing all three scenarios side-by-side — but it pulls results from whatever formula is in B10. So if B10 contains =B2*(1+B3)*B5, the summary shows total comp cost for each case. No manual copy-paste.
Pro tip: Right-click any scenario name in the manager → Edit → change the Result cells field to include B10, B11 (total benefits), and B12 (turnover risk score). That way, one click gives her a full 3-row × 4-column comparison — not just one number.
Method 2 Deep Dive
Now imagine Rajiv Patel (Sales Ops) wants to see how quarterly revenue shifts if he adjusts both discount rate (B2) and units sold (B3). His revenue formula is in B10: =B1*B2*(1-B3) (list price × units × (1 − discount)).
He sets up a grid: In F1 he puts =B10. In G1:K1 he types 5%, 10%, 15%, 20%, 25%. In F2:F6 he types 100, 200, 300, 400, 500. He selects F1:K6, hits Alt + A + W + T, enters Row input cell: $B$2, Column input cell: $B$3, clicks OK.
The table fills instantly. Each cell shows revenue for that combo — no dragging, no macros. And because it’s a true Excel table (not pasted values), if he changes B1 (list price) from $1,299 to $1,349, the whole grid recalculates.
Counterintuitive tip: You can use Data Tables with non-adjacent inputs — just make sure your base formula references them directly. If B10 referenced =VLOOKUP(B2,Discounts!A:B,2,0)*B3, the table still works. But if it used =INDEX(Discounts!B:B,MATCH(B2,Discounts!A:A,0))*B3, Excel will throw #REF! — stick to VLOOKUP or direct math in the base cell.
Cheat Sheet
| Tool | Shortcut | Key Cell Reference | One-Liner Tip |
|---|---|---|---|
| Scenario Manager | Alt + A + W + S | B2, C5, D7 (up to 32 cells) | Name scenarios after outcomes (“$1.2M EBITDA”, not “Case 3”) |
| One-Variable Table | Alt + A + W + T | Column input: $B$2 | Always leave top-left cell blank — it holds the formula, not a label |
| Two-Variable Table | Alt + A + W + T | Row input: $B$3, Column input: $B$2 | Input ranges must be in same orientation as table layout — no transposing mid-process |
| Goal Seek | Alt + A + W + G | Set cell: $B$10, To value: 125000 | If it says “Solution not found”, try widening the input cell’s numeric range first |
| Live Dropdown Switch | Alt + D + L | B2 = CHOOSE($E$1,85000,92000,135000) | Pair with Data Validation list in E1 — users change scenarios without touching formulas |
