How to Use Goal Seek in Excel (Work Backwards to an Answer)

Most of the time you give Excel the numbers and it works out the answer. But sometimes you know the answer you want and need to find the number that gets you there. What mark do I need in the final exam to average 70%? How many units do we need to sell to break even? Goal Seek answers these questions in a few clicks.

Goal Seek needs three things:

1. Set cell: a cell containing a formula (the result).
2. To value: the result you want.
3. By changing cell: an input cell that the formula depends on.

Excel then tries different values in the input cell, very quickly, until the formula gives your target.

You've scored 62 and 71 in two tests (B2 and B3). The final test goes in B4, which is empty for now. B5 contains:

=AVERAGE(B2:B4)

You want an average of 70.

1. Go to Data > What-If Analysis > Goal Seek (in the Forecast group).
2. Set cell: B5
3. To value: 70
4. By changing cell: B4
5. Click OK.

Excel puts 77 in B4. That's the mark you need. Click OK to keep it, or Cancel to put things back as they were.

You sell a product for £25 (B1) with a cost of £10 per unit (B2), and fixed costs of £3,000 a month (B3). Units sold is in B4. Profit in B5 is:

=B4*(B1-B2)-B3

To find how many units you need to sell to make zero profit:

• Set cell: B5
• To value: 0
• By changing cell: B4

Goal Seek gives 200 units. Change "To value" to 1000 to see what it takes to make £1,000 profit.

You can afford £350 a month. The interest rate is in B1 (6%), the term in years is in B2 (5) and the loan amount is in B3. The monthly payment in B4 is:

=PMT(B1/12, B2*12, -B3)

• Set cell: B4
• To value: 350
• By changing cell: B3

Goal Seek tells you the loan amount that gives a £350 monthly payment, around £18,100.

• The Set cell must contain a formula, and the By changing cell must contain a plain value (or be empty), not a formula.
• The formula must actually depend on the changing cell, directly or through other formulas.
• Goal Seek changes one input only. For problems with several inputs or constraints, use the Solver add-in (File > Options > Add-ins > Solver Add-in).

"Goal Seeking with Cell B5 may not have found a solution." Your target might be impossible, like an average that would need a mark above 100, or the formula doesn't depend on the changing cell. Check the chain of formulas.

The answer has lots of decimal places. Goal Seek finds a very close answer, like 76.99999. Round it yourself, or increase accuracy under File > Options > Formulas > Maximum Change.

"Cell must contain a formula." You selected the input cell as the Set cell. Swap them round.

Goal Seek is greyed out. You're editing a cell. Press Esc first.

When you know the answer but not the input, go to Data > What-If Analysis > Goal Seek. Set the formula cell, type your target, choose the input, and let Excel do the trial and error.