Advanced Excel · Interactive lesson 8 of 12
What-if analysis
Instead of guessing and re-typing numbers until a formula hits a target, Excel can work backward from the target itself — or lay out every combination of two inputs at once, so you see the shape of the answer, not just one point on it.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialWhat-If Analysis in Excel: Goal Seek, Scenarios and Data TablesSkill check
Try these first:
- Use Goal Seek to find the input a formula needs to hit a target output.
- Build a one-variable data table showing profit at five different prices.
- Set up named scenarios (Best/Base/Worst case) with Scenario Manager.
- Explain what Solver adds beyond what Goal Seek can do.
All familiar? Skip ahead to A9 — Dashboard Design.
Concept teach
What you're learning
- Goal Seek
- Data → What-If Analysis → Goal Seek — tell Excel which cell to change, and what value another cell should reach. It solves backward by trial and error, automatically.
- One- and two-variable data tables
- A one-variable table sweeps a single input across a row or column of outputs; a two-variable table sweeps two inputs at once into a grid — profit by price and volume, in one view.
- Scenario Manager
- Saves named sets of input values — Best Case, Base Case, Worst Case — so you can switch the whole model between them with one click instead of retyping each input.
- Solver basics
- Goal Seek changes one input to hit one target. Solver changes several inputs at once, under constraints, to maximize or minimize something — the tool for "what's the best combination," not just "what single number."
- Sensitivity analysis layout
- Lay results out so the reader can see which input the outcome is most sensitive to — usually the input worth double-checking first.
Where to find it in Excel
Data›What-If Analysis›Goal Seek
| A | B | C | D | E | F | G | H | ||
|---|---|---|---|---|---|---|---|---|---|
| 1 | Model | ||||||||
| 2 | Units | 1,200 | |||||||
| 3 | Price | 25.00 | |||||||
| 4 | Costs | 27,500 | |||||||
| 5 | Profit | 2,500 | |||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 | |||||||||
| 11 | |||||||||
| 12 |
Swipe sideways to see the whole window.
- 1Data tab.
- 2What-If Analysis in the Forecast group.
- 3Goal Seek works backward from a target. Scenario Manager and Data Table are in the same list.
- 4Set cell is the formula, To value the target, By changing cell the input to adjust.
- 5Solver (Data › Analyze) handles several inputs and constraints at once.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided build
A simple profit model: Price × Volume − (Cost × Volume). Try each tool against it.
Independent extension
Model your own decision
Build a small profit or budget model with three inputs. Use Goal Seek to find the input value that hits a specific target, save two named scenarios (optimistic and pessimistic), and lay out a two-variable data table for the two inputs you're least sure about.
Spot a mistake or have an idea for this lesson? Send feedback