Goal Seek in Excel: Work Backwards to a Target

Goal Seek in Excel works backwards: you tell it the answer you want, and it finds the input that produces it. It's the quickest way to answer questions like "how much growth do we need to hit 60,000?" or "what loan can I afford at 450 a month?" without rearranging any maths yourself. This guide shows how to use it step by step, its limits, and when a simple formula does the same job better.

What Goal Seek does#

Goal Seek changes one input cell until a formula cell shows the value you ask for. It's one of Excel's what-if analysis tools, which let you test "what if this number were different?".

Every Goal Seek needs three things:

Excel then tries value after value in the input cell, watching the formula cell, until it reaches the target. It shows the result in a Goal Seek Status box, where you choose OK to keep it or Cancel to go back.

How to use Goal Seek: step by step#

Here is a small sales forecast. Current sales are 48,000 and the forecast adds a growth rate:

Your manager wants 60,000. What growth rate gets you there?

1. Go to Data > What-If Analysis > Goal Seek. In Windows Excel, What-If Analysis is in the Forecast group. The path is the same in Excel for Mac.
2. In Set cell, enter B4, the forecast formula.
3. In To value, type 60000. This box takes a number, not a cell reference.
4. In By changing cell, enter B3, the growth rate.
5. Click OK. Excel reports that it found a solution, and B3 now shows 25%.
6. Click OK to keep the new value, or Cancel to restore 10%.

You can check the answer yourself: 48,000 × 1.25 = 60,000. Going from 48,000 to 60,000 is a 25% increase, the same percent change formula covered in the percent change lesson.

Goal Seek with PMT: the loan you can afford#

Goal Seek shines when the formula is hard to rearrange, such as a loan payment. Set up the inputs:

You can pay 450 a month. How much can you borrow?

1. Open Data > What-If Analysis > Goal Seek.
2. Set cell B4, To value 450, By changing cell B3.
3. Click OK.

B3 becomes about 23,276.50. The minus sign before B3 in the PMT formula makes the payment show as a positive number; the guide to the PMT function explains why. To see how that loan is paid off month by month, build a loan amortization schedule.

Excel also has a function that answers this directly: =PV(B1/12,B2,-450) returns the same 23,276.50. PV is the reverse of PMT, so for loans you often don't need Goal Seek at all. For savings, the same idea finds the monthly amount for a goal; see the savings goal calculator.

Goal Seek for a profit target#

Goal Seek also finds the price you need for a profit goal. Say fixed costs are 12,000, each unit costs 18 to make, and you expect to sell 1,500 units:

=B3*(B4-B2)-B1

Here B1 is fixed costs, B2 the cost per unit, B3 the units and B4 the price. Use Goal Seek with this profit formula as the Set cell, 9000 as the To value and B4 as the By changing cell. Excel returns a price of 32: 1,500 × (32 − 18) − 12,000 = 9,000.

Set the To value to 0 instead and Goal Seek finds the break-even price, 26. The guide to break-even analysis in Excel shows the formula version of this with a full profit table. The same trick works for discounts: set the final price as the target and let Goal Seek find the percentage, or use the direct formulas in how to calculate a discount.

Rules and limits of Goal Seek#

Goal Seek is simple, but it is strict about how the sheet is set up:

• The Set cell must contain a formula. If it holds a typed number, Goal Seek stops with an error.
• The By changing cell must contain a value, not a formula. It must also feed into the Set cell, directly or through other formulas. If it doesn't, nothing Goal Seek tries can change the result.
• One input only. Goal Seek can't change two cells at once, such as price and units together. For that, or for limits like "price no higher than 40", use the Solver add-in (turn it on under the add-ins settings, then find it on the Data tab).
• The answer is fixed. Goal Seek types a value into the input cell once. If current sales change tomorrow, the growth rate doesn't update. Run it again.
• The answer may be very close, not exact. Goal Seek stops when it gets near the target, so you may see 24.99999% instead of 25%. Round the result, or retype it.

If you see "Goal Seeking with Cell B4 may not have found a solution", check that the input really feeds the formula, that the target is possible, and try a different starting value. Undo with Ctrl+Z on Windows or ⌘Z on Mac if you clicked OK by mistake.

Goal Seek or a reverse formula?#

When you can rearrange the maths, a formula is usually the better choice, because it updates by itself. The sales example is easy to reverse:

=B5/B2-1

Target divided by current sales, minus 1, gives 25%, and it changes the moment B2 or B5 changes. Use this rule of thumb:

Being able to explain both is a good sign in a job test: Goal Seek shows you know Excel's tools, and the formula shows you understand the numbers. Budget questions like these come up often, which is why the budget maths lesson practises them.

Practise it#

Try the exercise on this page: work out the growth rate Goal Seek would find, using one formula. Then practise percent change in a live spreadsheet and work through budget maths step by step. You type each formula, and ExcelReady checks it instantly.