Goal Seek in Excel works a formula backwards: you state the result you want, name the one input cell Excel may change, and it finds the input value that produces that result. In this lesson you will learn how to use Goal Seek in Excel to find the quantity that delivers a target profit, how to read the result, and when Goal Seek is the wrong tool.
What Goal Seek does and when to use it
Goal Seek is one of the What-If Analysis tools on the Data tab. It solves one equation with one unknown by trial and error, adjusting the input cell until the formula cell reaches the value you typed, to within Excel’s iteration tolerance. Typical questions: how many units must we sell to reach a profit of 5,000, what interest rate gives a monthly payment of 12,000, what score is needed in the final exam to average 70. Goal Seek needs a formula cell, a target value and exactly one input cell that the formula depends on; for several inputs or constraints use Solver.
Prepare the worksheet
The model below has three columns: Particular, Value and Comments. The inputs are Price (20), Variable Costs per unit (5), Fixed Costs (200) and Quantity Sold, which is currently blank. The formulas are Total Costs = Fixed Costs + Quantity Sold x Variable Costs, Total Revenue = Price x Quantity Sold, and Profit = Total Revenue – Total Costs, which shows -1,005 while quantity is empty.
The structure matters: Profit in B8 must be a formula that ultimately depends on Quantity Sold in B6. If B8 were a typed number, Goal Seek would have nothing to work with.
How to use Goal Seek in Excel (step by step)
The goal is a profit of 5,000 by changing only the quantity sold.
- Select the formula cell whose value you want to set, B8 (Profit).
- On the Data tab, in the Forecast group, click What-If Analysis and choose Goal Seek.
- In the Goal Seek dialog confirm Set cell is B8.
- Type 5000 in To value.
- Click in By changing cell and select B6 (Quantity Sold). This cell must contain a value or be empty, never a formula.
- Click OK.
- Excel iterates and shows the Goal Seek Status box: “found a solution” with the target and current values. Click OK to keep the new quantity or Cancel to restore the original value.
Quantity Sold now shows the units required: profit per unit is 20 – 5 = 15, so (5,000 + 200) / 15 gives about 346.67 units. Goal Seek returns the exact decimal; round it up in practice because you cannot sell a fraction of a unit.
More ways to use Goal Seek
- Loan payments: set the PMT cell to the affordable payment by changing the loan amount or the rate.
- Break-even: set Profit to 0 by changing Quantity.
- Pricing: set Profit to a target by changing Price instead of Quantity.
- Grades: set the average formula to 70 by changing the last exam cell.
- Reverse percentages: set the after-tax total to a figure by changing the pre-tax amount.
Tips and common mistakes
- “May not have found a solution” means the target is unreachable or the formula is not continuous. Check the logic or supply a closer starting value in the changing cell.
- Only one changing cell. Goal Seek cannot vary two inputs; use Solver for that.
- Results are approximate. Increase precision under File > Options > Formulas > Maximum Change (for example 0.0001) if the answer looks off.
- Save first or use Cancel. Goal Seek overwrites the input cell; Cancel in the status box or Ctrl+Z restores it.
- Record a macro while running Goal Seek to automate repeated targets:
Range("B8").GoalSeek Goal:=5000, ChangingCell:=Range("B6").
Practice and real-world use
Using the practice file, set Profit to 0 to find the break-even quantity, then set Profit to 5,000 by changing Price with quantity fixed at 300. Finance teams use Goal Seek daily for pricing, break-even, loan sizing and target-setting.
Watch the step-by-step video tutorial
Click here to download the practice file.
Related lessons
Frequently asked questions
Where is Goal Seek in Excel?
On the Data tab, in the Forecast group, click What-If Analysis and choose Goal Seek. It is available in every desktop version from Excel 2016 to Microsoft 365 and in Excel for Mac.
Why does Goal Seek not find a solution?
The target may be impossible with the given formula, the changing cell may not actually feed the set cell, or the changing cell contains a formula instead of a value. Check the dependency chain and try a different starting value.
Can Goal Seek change more than one cell?
No. Goal Seek varies a single input cell. To adjust several inputs at once, or to add constraints, enable the Solver add-in under File > Options > Add-ins.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.