Goal Seek in Excel: find the number that hits your target

Goal Seek in Excel finds the input value that makes a formula return the result you want. You name the formula cell, the target and the one input Excel may change, and Excel adjusts that input until the formula hits the target. With a contribution of $18.00 a unit and fixed costs of $180,000, Goal Seek finds that 15,000 units deliver a $90,000 profit.

What Goal Seek does

Most spreadsheet work runs forwards: inputs in, result out. Goal Seek runs backwards. In Microsoft's words, it "takes a result and determines possible input values that produce that result" (Introduction to What-If Analysis).

It has one hard limit: "Goal Seek works only with one variable input value" (Microsoft Support, Goal Seek). One input, one target, no constraints.

The worksheet you need

Two things: an input cell holding a constant, and a formula that depends on it, directly or through other cells. The profit model used throughout:

B5 holds the formula:

=B1*(B2-B3)-B4

12,000 × (45 − 27) − 180,000 = 36,000. The difference between price and variable cost, $18.00, is the contribution per unit; as a share of price it is the contribution margin ratio, 40%.

Step by step

1. Click the formula cell, B5.
2. On Windows: Data tab > Forecast group > What-If Analysis > Goal Seek. On a Mac: Data > What-If Analysis > Goal Seek. From the keyboard on Windows, press Alt, then A, W, G, following the ribbon key tips.
3. In Set cell, enter B5, the formula you want to control.
4. In To value, type the target number. It must be a number, not a cell reference.
5. In By changing cell, enter the input Excel may change. Microsoft notes that this cell "must be referenced by the formula in the cell that you specified in the Set cell box."
6. Click OK. The Goal Seek Status box shows the result. Click OK to keep the new input, or Cancel to put the original back.

Worked: units, price and break-even

Three runs on the same model, each starting from the values in the table above.

Run 1 says the target needs 3,000 more units than the plan. Run 2 holds units at 12,000 and asks what price closes the gap instead: $49.50, a 10% increase on $45.00. Run 3 sets profit to zero, the break-even point. Before run 2, put B1 back to 12,000; Goal Seek leaves its last answer in the cell.

Check the answer

Goal Seek is iterative: it tries values and narrows in. The algebra is exact, so use it to check each result.

Units for a target profit = (Fixed costs + Target profit) / (Price − Variable cost)

Price for a target profit = Variable cost + (Fixed costs + Target profit) / Units

Break-even units = Fixed costs / (Price − Variable cost)

The same checks as Excel formulas, which should match the Goal Seek results:

=(B4+90000)/(B2-B3)
=B3+(B4+90000)/B1
=B4/(B2-B3)

Then put each answer back into the worksheet and confirm B5 shows the target. That last step is the one that catches a wrong cell reference; five checks before you trust a spreadsheet total covers the rest.

Goal Seek vs Solver vs Data Tables

Excel's what-if tools answer different questions:

Solver is an add-in: enable it once under File > Options > Add-ins, choose Excel Add-ins in the Manage box and click Go. It then appears on the Data tab, and its objective can be set to Max, Min or Value Of. A Data Table, by contrast, takes "only one or two variables" but many values for each, so a price-by-volume grid of profit is a Data Table job.

Several cells at once

Goal Seek cannot change two inputs in one run. There are two ways round it.

Solver, when the inputs interact: for example, change both price and units to reach $90,000 profit with price no higher than $48.00 and units no higher than 14,000. Solver handles the constraints; Goal Seek cannot.

A short VBA loop, when you need the same Goal Seek on many rows, such as the units each product line needs to hit its own target. With the profit formula in column E and units in column A:

Sub GoalSeekEachRow()
    Dim r As Long
    For r = 2 To 20
        Range("E" & r).GoalSeek Goal:=Range("F" & r).Value, ChangingCell:=Range("A" & r)
    Next r
End Sub

Column F holds each row's target. Run it on a copy, because each row's input is overwritten.

Where it goes wrong

• The By changing cell holds a formula. Goal Seek needs a constant there. Point it at the underlying input instead.
• Close but not exact. Goal Seek stops once the result is within a small tolerance, so the input it leaves can be a fraction off the exact answer. Round the input, or check with the algebra. Microsoft groups Goal Seek with Solver as tools that "use iteration in a controlled way" (recalculation and iteration settings), and the Maximum Iterations and Maximum Change boxes are under File > Options > Formulas.
• More than one answer. A non-linear formula can have two inputs that hit the target. Goal Seek returns one, usually the one nearest the starting value, so try a different start.
• Overwriting the plan. Clicking OK replaces the input. Keep the original in another cell, or cancel and type the answer in yourself.
• The wrong tool. Several inputs, a limit on any of them, or a best possible result is Solver's job, not Goal Seek's.

For more spreadsheet techniques on commercial data, see sales analytics in Excel, and for working backwards from a target to the effort it needs, capacity is arithmetic.

Beyond one workbook

Goal Seek answers "what would it take" for one model in one workbook. Covirage's tools compute the same backward-solved figures, such as the volume needed to hit a margin target, directly from your own ledger and plan files, and show the algebra behind each one; the external AI model explains the result and never does the arithmetic. See FP&A reporting for targets, actuals and the gap between them every month, and quota setting from potential for how targets get set in the first place. To test several inputs at once, see scenario analysis in Excel.