Goal Seek
Excel Guides / What-If Analysis
What-If Analysis
Work backwards from the answer you want to the input that produces it.
Updated 12 Oct 2026 Ā· 7 min read Ā· By Sky Dashboards
Most formulas run forwards: you type the inputs and Excel works out the result. Goal Seek runs the other way. You tell it the result you want, point it at one input, and it keeps adjusting that input until the formula lands on your target. It is the quickest answer to questions like "how many do I need to sell?" or "what price gets me to that margin?"
In this guide
How Goal Seek works
Goal Seek needs three things, and they map straight onto the three boxes in its dialog:
- Set cell: the result you care about. It must contain a formula, such as a profit or margin calculation.
- To value: the number you want that result to show, typed in.
- By changing cell: the input Excel is allowed to adjust. It must hold a typed number, not a formula, and the Set cell must depend on it, directly or through other formulas.
Run Goal Seek step by step
- Build the model so the result cell is a formula that depends on the input you want to solve for.
- Click the result cell. Doing this first means Goal Seek fills in Set cell for you.
- On the Data tab, in the Forecast group, click What-If Analysis and choose Goal Seek...
- In To value, type the target, for example
3000. - Click in By changing cell, then click the input cell on the sheet.
- Click OK. The Goal Seek Status box reports whether a solution was found and shows the target and current values.
- Click OK to keep the new input, or Cancel to put the original back.
āØĀ Open Goal Seek
Alt ā A ā W ā G
Press the keys one after another, not together.
Press the keys one after another. Alt activates the ribbon, A opens Data, W opens What-If Analysis and G picks Goal Seek.
Note
Key tips are a Windows feature. On a Mac, use Data > What-If Analysis > Goal Seek on the ribbon.
Example: how many candles to hit a profit target
A candle shop sells each candle for $28. Wax, wick, jar and label cost $11 a candle, and rent plus studio bills come to $2,450 a month. At 250 candles a month the profit is $1,800. The owner wants $3,000. How many candles is that?
| A | B | ||
|---|---|---|---|
| 1 | Item | Value | |
| 2 | Price per candle | $28.00 | |
| 3 | Cost per candle | $11.00 | |
| 4 | Fixed costs per month | $2,450.00 | |
| 5 | Candles sold | 250 | |
| 6 | Revenue | $7,000.00 | |
| 7 | Total costs | $5,200.00 | |
| 8 | Profit | $1,800.00 |
The model before Goal Seek. B2 to B5 are typed inputs; B6, B7 and B8 are formulas.
Revenue is =B2*B5, total costs is =B4+B3*B5 and profit is =B6-B7. Select B8, press Alt A W G, type 3000 in To value and pick B5 for By changing cell.
| A | B | ||
|---|---|---|---|
| 1 | Item | Value | |
| 2 | Price per candle | $28.00 | |
| 3 | Cost per candle | $11.00 | |
| 4 | Fixed costs per month | $2,450.00 | |
| 5 | Candles sold | 320.5882353 | |
| 6 | Revenue | $8,976.47 | |
| 7 | Total costs | $5,976.47 | |
| 8 | Profit | $3,000.00 |
After Goal Seek: 320.59 candles gives a profit of exactly $3,000.
You can check it by hand. Each candle adds $17 of margin ($28 minus $11), and the shop needs $2,450 + $3,000 = $5,450 of margin, so 5,450 / 17 = 320.59. You cannot sell part of a candle, so round up to 321, which gives a profit of $3,007. At 320 candles it would fall short at $2,990.
For the break-even point, run Goal Seek again with To value set to 0. The answer is 144.12, so the shop needs 145 candles a month before it makes any money. At 144 it is still $2 down.
Turn the answer into a live formula
Goal Seek is a one-off. If the price or costs change, the candle count does not update. When the maths is simple, write it as a formula instead: =ROUNDUP((B4+3000)/(B2-B3),0) returns 321 and recalculates whenever an input changes.
The rules: a formula to set, a constant to change
Most Goal Seek errors come from pointing it at the wrong kind of cell:
- The Set cell holds a typed number. Excel stops with a message that the cell must contain a formula. Pick the cell that does the calculation, not one of its inputs.
- The By changing cell holds a formula. Excel refuses this too, because it will not overwrite a formula with a guess. Point it at the typed input the formula is built on. If that input is itself calculated, follow the chain back to the first typed number.
- The result does not depend on the input. Goal Seek runs, nothing changes and it reports that it may not have found a solution. Formulas > Trace Precedents shows which cells the result really uses.
- More than one input needs to move. The By changing cell box takes a single cell. See Solver below.
Why the answer is approximate
Goal Seek does not solve the equation algebraically. It guesses, measures the gap and guesses again, the way you would home in on a number by trial and error, only much faster. Two settings under File > Options > Formulas, in the Calculation options section, decide when it stops:
- Maximum Iterations (default 100): the most attempts it makes before giving up.
- Maximum Change (default 0.001): how close the result must get to the target to count as a hit.
For a straight-line model like the candle shop, Goal Seek usually lands on the target almost exactly. For curved models, such as compound growth or a loan payment from PMT, it can stop at something like $2,999.9996 instead of $3,000. That is within the tolerance, so Excel calls it a solution.
For a tighter answer, tick Enable iterative calculation to unlock the two boxes, lower Maximum Change to 0.000001, run Goal Seek again, then untick the box if your workbook has no deliberate circular references.
Note
Goal Seek starts from the value already in the changing cell. Curved models can have more than one answer, and it tends to find the one nearest its starting point. If it fails or returns something odd, type a more sensible starting guess and run it again.
Keep or undo the result
When the status box appears, the sheet already shows the new input. You have a choice:
- OK keeps the new value in the changing cell.
- Cancel restores the original value. Use it when you only want to read the answer without changing the model.
- If you clicked OK by mistake, press Ctrl + Z straight away. If nothing comes back, type the original value in again.
Watch out
Goal Seek overwrites the input with a hard-coded number. Before running it on a model other people use, copy the base input into a cell labelled "Original" next to it, so getting back to the base case is a matter of retyping one number.
Limitations, and when to use Solver
- One input only. To find a price and a volume together, or to split a budget across channels, you need Solver.
- One target only. You cannot ask for a profit of $3,000 and a margin above 40% at the same time.
- No constraints. It will happily return a negative number of hours or 0.4 of a staff member. Sanity-check the answer yourself.
- Not live. The result is a typed number. Change an assumption and you have to run it again.
- The target must be typed. To value does not accept a cell reference, so you cannot point it at a target stored on the sheet.
Solver handles several changing cells, constraints such as "units must be whole numbers" or "price no higher than $35", and finding the largest or smallest possible result instead of an exact value. It comes with Excel but is switched off by default. Go to File > Options > Add-ins, choose Excel Add-ins in the Manage box, click Go, tick Solver Add-in and click OK. It then appears on the Data tab in the Analyze group.
If you want to see how the result changes across a range of inputs, rather than solving for one, use a data table.
Frequently asked questions
Where is Goal Seek in Excel?
On the Data tab, in the Forecast group, under What-If Analysis. On Windows you can press Alt, A, W, G one after another to open it.
Why does Goal Seek say the cell must contain a formula?
The Set cell holds a typed number. Goal Seek needs a formula there so it has something to recalculate. Pick the cell that calculates the result.
Can Goal Seek change more than one cell?
No. It adjusts a single input. Use the Solver add-in when several inputs need to change or the answer has to respect constraints.
Why is the Goal Seek result not exact?
It works by repeated guesses and stops once the result is within the Maximum Change tolerance, 0.001 by default. Lower that value under File > Options > Formulas for a tighter answer.
Want the finished dashboard instead?
My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.
Browse templates ā