Skip to content
Controllingblog

Home❧Software & Data❧Goal Seek, data tables and more: Excel's what-if tools

Goal Seek, data tables and more: Excel's what-if tools

Which price gives a result of zero? How strongly does profit react to volume and price? Excel has dedicated tools for this – only ten, but they save a lot of trial and error.

Software & Data10 min readUpdated 14 Nov 2026
Illustration: laptop with dashboard, database and cloud +12% 4.2 M 98%

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.

Set cell: $B$7 To value: 0 By changing cell: $B$2

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.

{=TABLE(B2,B3)}

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.

=FORECAST.ETS(A25, B2:B24, A2:A24)
Try itOur break-even calculator works out the break-even point in the browser without Goal Seek – handy for cross-checking your Excel file.

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.