What-If Analysis is the set of Excel tools (on the Data tab) for asking questions the other way round from a normal formula: instead of “what does this formula give me?”, they answer “what input would give me the answer I want?”, or “how does the answer change across a whole range of inputs?” The three built-in tools are Goal Seek, Data Tables and Scenario Manager, and a fourth, more powerful tool, Solver, is a free add-in that has to be switched on separately.
This guide runs each of the three built-in tools on a small pricing and loan example, and shows a Goal Seek that fails, which is worth seeing once before it happens to you for real. Every result was produced by running the tool in Microsoft Excel.
In this guide
The short version
- Goal Seek changes one input cell until one formula cell hits a target you set. Data, What-If Analysis, Goal Seek.
- A Data Table recalculates one formula for a whole list of possible inputs at once, without copying the formula anywhere.
- Scenario Manager stores several complete sets of input values (a whole what-if situation) under a name, so you can switch between them.
- Solver goes further than Goal Seek: it can adjust several cells at once, subject to constraints, to maximise, minimise or hit a target.
Goal Seek: work backwards from an answer
Goal Seek (Data, What-If Analysis, Goal Seek) answers “what value in this cell gives me that result in this other cell?” It needs a formula cell (the Set cell), a target value, and one input cell to change (By changing cell), which the formula must depend on. A small profit model: 100 units at 50 each, minus a fixed cost of 2,000:
| A | B | |
|---|---|---|
| 1 | Units | 100 |
| 2 | Price | 50 |
| 3 | Fixed cost | 2000 |
| 4 | Profit | 3000 |
Asking Goal Seek for a profit of 5,000, changing the number of units, finds the units needed and updates the sheet in place:
| A | B | |
|---|---|---|
| 1 | Units | 140 |
| 2 | Price | 50 |
| 3 | Fixed cost | 2000 |
| 4 | Profit | 5000 |
Goal Seek only ever changes one cell. If the answer depends on more than one input, or on limits like “units cannot exceed 500”, you need Solver instead.
When Goal Seek cannot find an answer
Some targets are impossible. A1 holds 5 and B1 holds =A1^2, which can never be negative for a real number. Asking Goal Seek for -10 fails, and Excel shows a message saying it may not have found a solution. Crucially, it still leaves a value in the cell, usually a wild one from its last attempt, rather than putting the original number back:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | -3.47607E+14 | 1.20831E+29 | FALSE |
Always check the result (or read the confirmation dialog) after a Goal Seek. If it failed, undo (Ctrl + Z) to restore the original input rather than leaving whatever value it landed on.
One-variable data tables
A Data Table (Data, What-If Analysis, Data Table) recalculates a formula for a whole column or row of substitute values in one step, without a helper formula in every row. List the input values down a column, put a reference to the formula in the cell one row up and one column to the right of the first value, select the whole block, and tell Excel which cell those values should replace. Here five interest rates are tried against the same loan payment formula:
| A | B | |
|---|---|---|
| 1 | 0.1 | |
| 2 | 36 | |
| 3 | 200000 | |
| 4 | ₹ -6,453.44 | |
| 5 | ₹ -6,453.44 | |
| 6 | 0.08 | -6267.27 |
| 7 | 0.09 | -6359.95 |
| 8 | 0.1 | -6453.44 |
| 9 | 0.11 | -6547.74 |
| 10 | 0.12 | -6642.86 |
Because the values run down a column, the input cell is given as the Column Input Cell in the dialog (Excel substitutes each value into B1, recalculates, and drops the result into B6:B10). If the values ran across a row instead, the same input cell would go in the Row Input Cell box instead.
Two-variable data tables
A two-variable table varies two inputs at once, one down the rows and one across the columns, and the formula must sit in the single cell where that row and column meet (here A5). Rates run down column A, the number of months runs across row 5:
| A | B | C | D | |
|---|---|---|---|---|
| 5 | ₹ -6,453.44 | 24 | 36 | 48 |
| 6 | 0.08 | -9045.46 | -6267.27 | -4882.58 |
| 7 | 0.1 | -9228.99 | -6453.44 | -5072.52 |
| 8 | 0.12 | -9414.69 | -6642.86 | -5266.77 |
This grid is built from a single formula in A5; the rest is generated by the Data Table feature (Row Input Cell: B2, Column Input Cell: B1), which is why every cell in a data table shows {=TABLE(...)} if you look at its formula, and why you cannot edit or delete just one cell of the result.
Scenario Manager
Scenario Manager (Data, What-If Analysis, Scenario Manager) saves a named set of input values, so you can switch a whole what-if situation on with one click, rather than changing several cells by hand each time. Two scenarios, Best case and Worst case, each set both Units and Price:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Units | 50 | 10000 | |
| 2 | Price | 40 | 0 | |
| 3 | Fixed cost | 2000 | ||
| 4 | Profit | 0 |
Showing a scenario overwrites the actual input cells with its stored values, so the workbook is genuinely changed, not just previewed. Use the Summary button in the Scenario Manager dialog to build a report comparing every scenario side by side without switching between them one at a time.
Solver: several inputs and constraints
Solver is a free add-in, not part of the ribbon by default: turn it on once through File, Options, Add-ins, Manage Excel Add-ins, tick Solver Add-in. Once enabled, it appears on the Data tab. It solves the same kind of “what input gives this result” question as Goal Seek, but handles what Goal Seek cannot:
| Goal Seek | Solver |
|---|---|
| Changes exactly one cell | Can change many cells at once |
| Hits an exact target value | Can hit a target, or maximise, or minimise |
| No limits on the changing cell | Supports constraints, such as “Units ≤ 500” or “Units are whole numbers” |
A typical use: given a fixed budget split across several products, find the mix of quantities that maximises profit, where each product also has a minimum and a maximum you are allowed to make. That needs several changing cells and several constraints at once, which is exactly Solver’s job and beyond what Goal Seek or a data table can do.
Common mistakes
Mistake 1: not noticing a failed Goal Seek
A failed attempt still changes the cell, to whatever value it tried last. Read the confirmation message, or check the result, before trusting the new number.
Mistake 2: mixing up Row Input Cell and Column Input Cell
If your substitute values run down a column, the reference goes in Column Input Cell; if they run across a row, it goes in Row Input Cell. Swapping them gives a table of errors or repeated values.
Mistake 3: trying to edit one cell of a data table
Every result cell is part of one array formula ({=TABLE(...)}). You can only clear or rebuild the whole table, not a single cell inside it.
Mistake 4: reaching for Solver when Goal Seek is enough
If there is exactly one input and one exact target, with no constraints, Goal Seek is simpler and needs no add-in.
Cheat sheet
| Task | Tool |
|---|---|
| Find the input for one exact target | Goal Seek (Data, What-If Analysis) |
| See a result across many possible inputs, one variable | One-variable Data Table |
| See a result across many possible inputs, two variables | Two-variable Data Table |
| Save and switch between whole sets of inputs | Scenario Manager |
| Change several inputs at once, with limits, to hit or optimise a target | Solver (enable it first in Add-ins) |
Try it yourself
Work out each answer first, then open the solution.
1. 100 units sell at 40 each, giving revenue of 4,000. Use Goal Seek to find the price needed for revenue of 6,000, keeping units at 100.
Show solution
| A | B | |
|---|---|---|
| 1 | Units | 100 |
| 2 | Price | 60 |
| 3 | Revenue | 6000 |
Set the revenue cell to 6000 by changing the price cell: the price needed is 60.
2. You want to see a loan’s monthly payment for five different interest rates, without retyping the PMT formula five times. Which tool, and which value goes in Column Input Cell?
Show solution
A one-variable Data Table, with the rates listed down a column and the interest rate cell as the Column Input Cell.
3. You need to compare “optimistic” and “pessimistic” versions of a budget, each changing five different cells, and switch between them in a meeting. Which tool fits best?
Show solution
Scenario Manager: define each version once, by name, and show either one with a click, rather than retyping five cells each time.
4. You need to choose how many units of three products to make, within a shared budget and a maximum for each product, to make the most profit. Can Goal Seek do this?
Show solution
No. Goal Seek changes only one cell and has no idea of constraints. This needs Solver, which can change all three quantities at once while respecting the budget and the maximums.
Frequently asked questions
What is the difference between Goal Seek and Solver?
Goal Seek changes exactly one input cell to hit one exact target, with no constraints. Solver can change several cells at once, can maximise or minimise instead of only hitting an exact value, and supports constraints such as upper and lower limits.
Why did Goal Seek give a strange result?
The target you asked for may be impossible for the formula to reach. Goal Seek still leaves a value in the cell even when it fails; check the confirmation message, and undo if it did not succeed.
What is the difference between a one-variable and a two-variable data table?
A one-variable table varies a single input, listed down a column or across a row. A two-variable table varies two inputs at once, one down the rows and one across the columns, with the formula in the corner cell where they meet.
Why can’t I edit one cell of a Data Table result?
The whole block of results is one array formula. Excel only lets you change or clear the entire table, not individual cells inside it.
What does Scenario Manager actually do to my worksheet?
Showing a scenario overwrites the real input cells with the values saved in that scenario. It is not a simulation or a preview; the workbook’s values genuinely change.
How do I turn on Solver in Excel?
File, Options, Add-ins, at the bottom choose Excel Add-ins and click Go, then tick Solver Add-in. It then appears as a button on the Data tab.
Can Solver change more than one cell at once?
Yes, that is one of its main advantages over Goal Seek: it can adjust several changing cells together to satisfy constraints and reach the best result.
Do I need Solver for a simple ‘what input gives this output’ question?
No. If there is one input, one target, and no constraints, Goal Seek is quicker and does not need an add-in.
Test yourself
Timed questions on What-If Analysis and Solver, with an explanation for every answer.
