Goal Seek in Excel: work a formula backwards

In short: Data > Forecast > What-If Analysis > Goal Seek. Point Set cell at the formula, type the result you want into To value, and pick the input cell in By changing cell. Goal Seek adjusts exactly one input. For several inputs, use Solver.

Goal Seek is a ribbon command, and the practice sheet on this page does not reproduce it. The exercises therefore answer the same questions with a formula instead. That has an upside: a formula updates on its own when the inputs change, while Goal Seek has to be run again every time.

Try it now: units needed for a profit target

Each unit leaves $30 after variable costs, and fixed costs are $84,000. How many units must you sell to end up with $30,000 profit?

Task How many units do you have to sell to hit the target profit in B4 exactly? Goal Seek would keep changing the quantity until the profit matches. Work it out directly with a formula in B6.

	A	B
1	Price per unit $	50
2	Variable cost per unit $	20
3	Fixed costs per year $	84000
4	Target profit $	30000
5
6	Units needed

How to use Goal Seek, step by step

Here is the same question solved with Goal Seek. You need a sheet where profit is calculated from quantity by a formula:

Cell	Content
B1	Price per unit: 50
B2	Variable cost per unit: 20
B3	Fixed costs: 84,000
B5	Quantity: 3,000 (any starting value)
B6	Profit: =B5*(B1-B2)-B3, which gives 6,000

1. On the Data tab, in the Forecast group, click What-If Analysis, then Goal Seek. On a Mac there is no Forecast group; What-If Analysis sits directly on the Data tab.
2. Fill in the three boxes as shown in the table below.
3. Click OK. Excel tries quantities and shows a status box. B5 now holds 3,800 and B6 shows 30,000.
4. Click OK to keep the new value, or Cancel to put the original value back.

Box	What goes in	In the example
Set cell	The cell with the formula whose result you want to control	B6
To value	The result you want, typed as a number. A cell reference is not accepted here.	30000
By changing cell	The input cell Excel may adjust. The formula in Set cell has to depend on it.	B5

Practice: the rate needed for a savings goal

Microsoft's own Goal Seek tutorial uses a loan: which interest rate fits a given monthly payment? For rate questions, though, Excel has RATE, a function that runs exactly that search inside a formula.

Task You deposit the amount in B1 at the end of every year. What annual interest rate do you need to reach the savings goal in B3 after the term in B2? Instead of using Goal Seek, calculate it in B5 with RATE.

	A	B
1	Savings per year $	2400
2	Term in years	10
3	Savings goal $	30000
4
5	Rate needed per year

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

Most finance functions have a counterpart like this: RATE solves for the rate, NPER for the number of periods, PMT for the payment and PV for the starting amount. The CAGR and loan payments lesson covers PMT, FV and PV in detail, including mortgage payments.

Goal Seek or a formula?

If the formula is easy to rearrange for the unknown, as with the unit count, the rearranged formula wins: it stays on the sheet and keeps recalculating. Goal Seek earns its place when the model is too tangled to rearrange, for example profit that runs through several cells of discount tiers and commissions. It saves you from guessing by hand.

Goal Seek limits, and when to use Solver

• One changing cell only. To adjust price and quantity together, you need Solver.
• The result is a fixed value. If fixed costs change later, the old quantity stays put until you run Goal Seek again.
• One answer, not all of them. If a formula has several solutions, the starting value decides which one Excel lands on. If the target cannot be reached, Excel tells you it may not have found a solution.
• An approximation. Goal Seek closes in on the target step by step, so the last decimal places may be slightly off. Round unit counts up with ROUNDUP.

Solver is an add-in that ships with Excel. Turn it on under File > Options > Add-ins: choose Excel Add-ins in the Manage box, click Go, and tick Solver. It then appears on the Data tab in the Analyze group. Solver can change several cells at once, respect constraints such as "quantity no more than 5,000", and look for a maximum or minimum instead of a fixed value.

Other what-if analysis tools

The same menu holds two relatives. A Data Table runs a formula for a whole list of inputs, for example profit at prices from $40 to $60, which makes it a simple sensitivity analysis. Scenario Manager saves complete sets of inputs, such as cautious, expected and optimistic, and shows them side by side.

Common Goal Seek errors

Set cell has no formula

Set cell must contain a formula. If it holds a typed number, there is nothing for Excel to work backwards from.

By changing cell holds a formula

Goal Seek only adjusts cells with plain values. If the changing cell contains a formula itself, Excel stops with a message.

The formula does not depend on the changing cell

If Set cell does not refer to the changing cell, directly or through other cells, the result never moves and Excel finds no solution.

Goal Seek in German Excel

English	German
What-If Analysis > Goal Seek	Was-wäre-wenn-Analyse > Zielwertsuche
Set cell, To value, By changing cell	Zielzelle (in current Microsoft help: Zelle festlegen), Zielwert, Veränderbare Zelle
Data Table, Scenario Manager, Solver	Datentabelle, Szenario-Manager, Solver
=RATE(B2,-B1,0,B3)	=ZINS(B2;-B1;0;B3)

Which Excel versions have Goal Seek?

Microsoft documents Goal Seek for Microsoft 365 and Excel 2016, 2019, 2021 and 2024, on Windows and Mac. RATE works in every current version.

FAQ