How to do what-if analysis in Excel
What-if analysis asks "if this input changed, what happens to the result?" Excel has three built-in tools — Data Tables, Scenario Manager and Goal Seek. Here's when to use each, and the one-click alternative.
When what-if analysis is the right tool
When the model already works and the real question is how the answer moves as the inputs do.
Sensitivity
How much does profit swing if price or cost moves 10%?
Targets
What sales do we need to hit a £50k profit? (Goal Seek)
Scenarios
Compare best / base / worst cases side by side.
How to do it in Excel
All three tools live under Data › What-If Analysis — and all three need a clean, formula-driven model underneath.
Build a model first
A cell that calculates the result (e.g. profit) from input cells (price, volume, cost).
Goal Seek for a target
Data › What-If Analysis › Goal Seek. Set the result cell to a target by changing one input.
Data Table for a range
Lay out input values along a row/column, reference the result, then Data › What-If Analysis › Data Table.
Scenario Manager for named cases
Data › What-If Analysis › Scenario Manager to save and compare best/base/worst input sets.
The three tools answer different questions: Goal Seek finds one input for a target, Data Tables sweep one or two inputs across a range, and Scenario Manager stores named bundles of inputs. They all depend on a clean, formula-driven model — if your result cell is a hard-typed number instead of a formula, none of them will work.
The faster way — skip the setup
Three separate tools, slow on big models, re-run by hand for each question. DataHub Pro turns it into a slider.
Three separate tools to learn
- Wire up a formula-driven model
- Pick the right what-if tool each time
- Data Tables are slow on big models
- Re-run by hand for each question
Move a slider, see the result
- Upload the model — inputs detected automatically
- Sweep ranges and compare scenarios instantly
- Sensitivity ranked from biggest to smallest impact
- Export the scenario comparison to a report
FAQ
What is what-if analysis in Excel?
It's a set of tools — Data Tables, Scenario Manager and Goal Seek under Data › What-If Analysis — that show how changing input cells changes a formula's result, so you can test scenarios and sensitivities.
What is the difference between Goal Seek and a Data Table?
Goal Seek works backwards: you set a target result and it finds the single input that achieves it. A Data Table works forwards: it sweeps one or two inputs across a range and shows the result for every combination.
Why is Goal Seek not working?
Goal Seek needs the result cell to be a formula that depends on the cell you're changing. If the result is a typed-in number, or doesn't reference the changing cell, Goal Seek has nothing to solve.
Keep exploring
More analysis you can run on your own data — no sign-up needed to read.
Test every scenario in seconds
Upload your model and DataHub Pro sweeps the inputs, ranks the sensitivities and compares scenarios for you. Free to try, no card.
Start free →