You find the what-if tools under Data → What-If Analysis (in some versions in the Forecast group). Unlike pivot tables or Power Query, there are not 30 features here – Excel ships exactly three what-if tools, plus Solver and the forecast sheet. Here are all those that make sense in controlling.
Back to the overview: Excel for controllers: the 30 most important functions
Goal Seek
1. Goal Seek
Calculates backwards: you set the desired result and Excel finds the matching input. Break-even example: set cell = result, to value = 0, by changing cell = price.
2. Limits of Goal Seek
It changes exactly one cell, and the set cell must contain a formula that depends on it. The result is entered as a fixed value – with new assumptions you have to run it again.
Data table
3. One-variable data table
Shows the result for many values of one assumption at once, e.g. profit at prices from 9 to 15. Values in a column, the reference to the result cell above and to the right, select the range → Data Table → column input cell = price cell.
4. Two-variable data table
The classic sensitivity matrix: prices in the column, volumes in the row, the reference to the result in the top-left corner. Excel fills the whole grid.
5. Calculation: automatic except for data tables
Large data tables slow the file down. With Formulas → Calculation Options → Automatic Except for Data Tables they only recalculate on F9.
Scenarios
6. Scenario Manager
Saves whole sets of assumptions under a name – best case, base case, worst case – and switches between them. A good fit with scenario analysis.
7. Scenario summary
Click Summary in the Scenario Manager: Excel creates a sheet with all scenarios and their results side by side – ready for the presentation.
Beyond what-if analysis
8. Solver
Goal Seek's big sister: changes several cells at once and respects constraints, e.g. “machine time at most 160 hours”. Enable via File → Options → Add-ins → Excel Add-ins → Solver.
9. Forecast Sheet
Data → Forecast Sheet turns a time series into a forecast with confidence band and chart. Good for a first revenue or volume projection.
10. FORECAST.ETS and FORECAST.LINEAR
The functions behind the forecast sheet. FORECAST.ETS takes seasonality into account, FORECAST.LINEAR fits a trend line.
A rule of thumb: one assumption, one target – Goal Seek. One or two assumptions, many values – data table. Compare whole sets of assumptions – Scenario Manager. Several levers with limits – Solver.
Frequently asked questions
Why does my data table not recalculate?
Calculation is probably set to “Automatic Except for Data Tables”. Press F9, or switch back to Automatic under Formulas → Calculation Options.
Can Goal Seek change several cells?
No, only one. Solver is designed for several changing cells and constraints.
Can I edit the formula in a data table?
Not individually. The TABLE formula belongs to the whole table. To change it, delete the result values and create the data table again.