Summary

Goal Seek answers the question a formula normally cannot: not "what does this work out to", but "what number do I need to put in to get the answer I want". This article covers the three boxes in the dialog, a worked loan example, how to accept or discard the result, and what to do when Excel reports it cannot get there.

In this guide

• 1. Understand What Goal Seek Does
• 2. Set Up the Sheet Goal Seek Can Work With
• 3. Run Goal Seek and Fill the Three Boxes
• 4. Work Through a Loan Payment Example
• 5. Accept or Cancel the Result
• 6. Handle a Solution Goal Seek Cannot Find
• 7. Use Solver When One Changing Cell Is Not Enough

Before You Start

• Excel for Windows or Excel for Mac. What-If Analysis is not in Excel for the web — click Editing > Open in Desktop App to run it.
• A sheet where the number you want to control is produced by a formula, and the number you are willing to change is typed in.
• Edit permission on the workbook. Goal Seek writes into a cell, so it does nothing in a file opened read-only.
• No admin rights required.

Instructions

1. Understand What Goal Seek Does

A formula runs forwards: you supply the inputs, Excel produces the answer. Goal Seek runs the same formula backwards. You supply the answer you want, and Excel finds the input that produces it.

Everything else about the feature follows from that one sentence. The questions it settles are the ones people otherwise answer by typing a number, looking at the result, adjusting, and typing again:

• "The repayment has to come in under 1,500 a month. How much can we borrow?"
• "I need a 35% gross margin. What does the unit price have to be?"
• "How many units do we have to sell before this product breaks even?"
• "What discount can we offer and still clear 50,000 in profit?"

Excel does the guessing for you, hundreds of times a second, and stops when it lands on the number.

Goal Seek changes exactly one cell and aims at exactly one target. That limit is the deciding factor between it and Solver, covered in Step 7.

2. Set Up the Sheet Goal Seek Can Work With

Goal Seek has one hard requirement, and a sheet that ignores it produces an error rather than an answer.

• The Set cell must contain a formula. Point it at a typed number and Excel returns Cell must contain a formula. and refuses to run.
• The changing cell must contain a typed value, not a formula. Point it at another formula and Excel returns Cell must contain a value. Goal Seek works by overwriting that cell with trial numbers, and it will not overwrite a calculation.
• The changing cell has to feed the Set cell. There must be a path — direct or through other formulas — from the input you are changing to the result you are targeting. No path means no amount of changing makes any difference.

In practice this means laying out inputs and calculations separately: typed figures in their own labelled block, formulas below or beside them. Here is a loan sheet built that way, on a tab named Loan:

Row	A	B	What is in B
2	Loan amount	285,000	Typed
3	Annual interest rate	6.20%	Typed
4	Term in months	360	Typed
5	Monthly payment	1,745.62	=PMT(B3/12,B4,-B2)

Rows 2 to 4 hold values. Row 5 holds a formula that depends on all three. That is a sheet Goal Seek can work with in any direction.

Tip: A model with a rate or a total buried inside a formula — =B4*0.062 instead of =B4*B3 — gives Goal Seek nothing to change. Pull every assumption out into its own labelled cell before you start. Naming those cells makes the formulas readable too; see How to Use Named Ranges in Excel.

3. Run Goal Seek and Fill the Three Boxes

1. Click the cell holding the formula you want to control — B5 on the Loan tab. Selecting it first saves typing, because Excel pre-fills the first box.
2. Click the Data tab on the ribbon.
3. In the Forecast group, towards the right of the ribbon, click What-If Analysis. In Excel 2016 and earlier this button sits in the Data Tools group.
4. Select Goal Seek from the dropdown. A small dialog titled Goal Seek opens with three boxes.
5. Fill each box, pressing Tab to move between them:

Box	What goes in it	In this example
Set cell	The cell holding the formula whose answer you want to control. One cell, and it must hold a formula.	B5
To value	The answer you want that formula to return. Type a plain number — this box takes no formulas and no cell references.	1500
By changing cell	The single input cell Excel is allowed to overwrite while it searches. Must hold a typed value.	B2

6. Click OK. Excel starts adjusting B2 and watching B5, and the numbers on the sheet flicker as it works.

Note: To value is the box people fill wrongly. It wants the number you are aiming at, not the cell that holds it. Clicking a cell on the sheet while the cursor is in that box types the address in as text, and Goal Seek reads it as garbage. Type 1500, not B9.

The same three boxes answer every question in Step 1:

The question	Set cell	To value	By changing cell
What price gives a 35% margin?	The margin formula	0.35	The unit price cell
How many units to break even?	The profit formula	0	The units sold cell
What discount still clears 50,000 profit?	The profit formula	50000	The discount rate cell

4. Work Through a Loan Payment Example

Using the Loan tab from Step 2. The monthly payment on 285,000 at 6.2% over 30 years is 1,745.62, and the budget stops at 1,500. The question is how much can be borrowed.

1. Click B5, the payment formula.
2. Click Data > What-If Analysis > Goal Seek.
3. Set cell already reads B5. Press Tab.
4. Type 1500 into To value. Press Tab.
5. Click cell B2 on the sheet. The By changing cell box fills in as $B$2.
6. Click OK.

The Goal Seek Status dialog appears reading Goal Seeking with Cell B5 found a solution., with Target value: 1500 and Current value: 1500.00 below it. Behind the dialog, B2 now reads about 244,914 and B5 reads 1,500.00.

The answer to the question is that the budget supports a loan of roughly 244,900 — about 40,000 less than the original figure.

Verification: Read the Current value line before you click anything. It shows what the formula actually returns now. When it matches Target value to the decimal places you care about, the answer is good. When it sits visibly short — 1502.31 against a target of 1500 — Step 6 explains why.

Run it again from the other direction to test a different assumption. Put B5 in Set cell, 1500 in To value, and B4 (the term) in By changing cell, and Excel reports how many months it takes to bring the original 285,000 loan down to a 1,500 payment. One model, several questions, no formula rewriting.

5. Accept or Cancel the Result

The Goal Seek Status dialog is a decision point, and both buttons are useful.

• OK keeps the result. The trial number Excel landed on stays in the changing cell, permanently replacing what you typed there, and every formula depending on that cell keeps its new answer.
• Cancel puts everything back. The changing cell returns to its original value and the sheet looks exactly as it did before you opened the dialog.

Two more buttons sit alongside them for watching the search happen: Step advances the calculation one iteration at a time so you can see the value converging, and Pause halts it mid-run. Neither is needed for everyday use.

1. Read Current value against Target value.
2. Click OK to keep the answer, or Cancel to discard it.
3. If you clicked OK and want the original back, press Ctrl + Z. Undo restores the changing cell and the formulas that recalculated from it.

Warning: Clicking OK overwrites the number you typed in the changing cell, and Excel keeps no record of what was there. On a live model, note the original figure somewhere first, or right-click the sheet tab, choose Move or Copy, tick Create a copy, and run Goal Seek on the duplicate.

Watch for side effects. If the changing cell also feeds calculations elsewhere in the workbook — a summary tab, a chart, a dashboard — those change too when you accept the result. Goal Seek shows you only the one target cell, not everything downstream of the input it altered.

6. Handle a Solution Goal Seek Cannot Find

Sometimes the dialog reads Goal Seeking with Cell B5 may not have found a solution. The Current value line tells you how close it got, and that number points at the cause.

Current value sits close but not exact — 1500.0004 against 1500. This is the precision limit, not a failure. Goal Seek stops once a pass moves the answer by less than Maximum Change, which defaults to 0.001. For money that is a tenth of a penny and no adjustment is needed. To tighten it:

1. Click File > Options, then select Formulas in the left sidebar. On a Mac, click Excel > Preferences > Calculation.
2. Under Calculation options, set Maximum Change to a smaller number, such as 0.0000001.
3. Raise Maximum Iterations from 100 to 1000 to give the search more passes.
4. Click OK and run Goal Seek again.

Note: These are the same two boxes that control Enable iterative calculation for circular formulas. Changing them affects both features across the whole workbook — see How to Fix Circular Reference Warnings in Excel for what that setting does on its own.

Current value is nowhere near the target, or has not moved at all. The setup is blocking the search. Work through these in order:

• The changing cell does not feed the Set cell. Click the formula cell, then click Formulas > Trace Precedents and check that an arrow leads back to the cell you nominated. No arrow means no connection.
• No input produces that answer. A payment on a 285,000 loan cannot reach 200 a month at any term. Ask whether the target is reachable before assuming Excel is at fault.
• The formula moves in steps rather than smoothly. ROUND, INT, CEILING, and IF create jumps, and Goal Seek can straddle a step without landing on the target. Remove the rounding from the model and apply it to the display instead, using Home > Decrease Decimal.
• The starting value is far from the answer. Goal Seek searches outward from whatever is in the changing cell. Type a rough estimate in first — an order of magnitude is enough — then run it again.
• The formula contains a volatile function. RAND, RANDBETWEEN, NOW, and TODAY change value on every recalculation, so the target moves while Excel chases it. Replace the volatile result with a fixed number for the duration of the exercise.

Click Cancel on the status dialog before changing anything, so the failed attempt does not leave a stray trial value in your input cell.

7. Use Solver When One Changing Cell Is Not Enough

Goal Seek stops at one input and one target with no rules attached. Reach for the Solver add-in when the question has any of these shapes:

• More than one thing can change. Price and volume together, or a budget spread across four departments.
• The answer must obey constraints. Units have to be whole numbers, headcount cannot go negative, spend has to stay under a ceiling.
• You want the best result rather than a specific one. Maximise profit, minimise cost, rather than hit exactly 50,000.

Solver ships with Excel and is switched off by default:

1. Click File > Options, then select Add-ins in the left sidebar.
2. At the bottom of the window, set the Manage dropdown to Excel Add-ins and click Go. The Add-ins dialog opens.
3. Tick Solver Add-in and click OK.
4. Solver now appears on the Data tab, in the Analyze group at the far right of the ribbon.

If Solver Add-in is missing from that list or the tick refuses to stick, your Office installation was deployed without it. Send IT a ticket saying: "Please enable the Solver add-in for Excel on my machine — it is not in File > Options > Add-ins > Excel Add-ins." It is a standard component of Office, so this is a short request rather than a software purchase.

Tip: Try Goal Seek first even on a problem you think needs Solver. Fixing all but one of the variables and running Goal Seek on the last one answers a surprising number of questions in thirty seconds, with none of the setup Solver asks for.

Troubleshooting

TIP

Duplicate the sheet before you run Goal Seek on anything that matters. Right-click the sheet tab, choose Move or Copy, tick Create a copy at the bottom of the dialog, and click OK. Goal Seek overwrites your input cell with no record of the original, and a spare copy of the tab costs nothing next to reconstructing a model from memory.

Symptom / Error	Potential Cause	Solution
Cell must contain a value.	By changing cell points at a formula	Point it at the typed input feeding that formula instead. Goal Seek only overwrites values (Step 2).
Cell must contain a formula.	Set cell points at a typed number	Point Set cell at the calculation you want to control, not at one of its inputs (Step 2).
Goal Seeking ... may not have found a solution.	No reachable answer, or the model steps rather than flows	Read Current value to see how close it got, then work through the causes in Step 6.
What-If Analysis is missing from the Data tab	The file is open in Excel for the web	Click Editing > Open in Desktop App. Goal Seek is a desktop-only feature.
The result is a long decimal such as 244913.8712	Default precision of 0.001	Accept it and round the input yourself, or tighten Maximum Change in File > Options > Formulas (Step 6).
Goal Seek overwrote a number I needed	OK commits the trial value	Press Ctrl + Z immediately. Copy the sheet before running it next time (Step 5).
Numbers changed on other sheets after clicking OK	The changing cell feeds more than the target formula	Select the changing cell and click Formulas > Trace Dependents to see everything downstream before you accept a result.

Related Articles

• How to Use Named Ranges in Excel
• How to Fix Circular Reference Warnings in Excel
• How to Calculate Percentages in Excel
• How to Use IF and IFS Functions in Excel
• How to Fix Common Excel Formula Errors
• How to Use the Analyze Data Feature in Excel
• Understanding Excel Features — A Quick-Reference Guide to What Excel Can Do

Did this solve your problem?