A spreadsheet formula is a one-way function, with inputs on the left and an answer on the right. =A1*A2 takes A1 and A2 and produces a value. You can't type into the result and have Excel work out what A1 must have been. Goal Seek is Excel's tool for running that function backwards, for the special case of one unknown input. You tell it three things. First comes the cell whose value you want to control, the Set cell, which must contain a formula. Next is the To value you want that cell to reach. Last comes the single By changing cell, an input the Set cell depends on. Goal Seek then searches numerically for the input value that drives the output to your target. Under the hood it's a root-finder.
Think of a thermostat. You choose the temperature you want, and the system adjusts the furnace's gas flow until the room matches. Goal Seek is that controller for one cell of a model.
Goal Seek imposes two structural conditions, and both are common failure points:
- The Set cell must contain a live formula that ultimately depends on the changing cell. If the Set cell is a hardcoded number, there's nothing to solve. If it depends on the changing cell only through a broken or missing link, Goal Seek will churn and tell you it "may not have found a solution". You need a formula chain from the changing cell to the target.
- You may change exactly one cell. Goal Seek is one-dimensional. Two unknowns need Solver, which generalises Goal Seek to many changing cells and adds constraints.
Advertisement
What is Goal Seek used for?
Three uses come up again and again. Break-even asks what unit volume makes profit zero. Implied assumption asks what discount rate makes this DCF equal the asking price, and Worked example 1 does that one. Target pricing asks what price delivers a target profit.
Take a simple cost-volume-profit model. You sell 50,000 units at a variable cost of $12, with $300,000 of fixed cost, and you want $200,000 of profit. Profit = units × (price − variable) − fixed, with price in the changing cell. Goal Seek the profit cell to 200000 by changing price and it returns $22.00, because 50,000 × (22 − 12) − 300,000 = 500,000 − 300,000 = 200,000. Sure, you could rearrange this algebra by hand. Goal Seek is worth having for the day the algebra is a tangle of nested formulas you can't invert on paper.
How do you run Goal Seek, and what are its limits?
You'll find Goal Seek at Data ▸ Forecast group ▸ What-If Analysis ▸ Goal Seek, or press Alt, A, W, G in sequence. Fill in Set cell with the formula output and By changing cell with the single input. To value takes a typed number, and it can't be a cell reference. Click OK. Excel shows the value it found and lets you Accept or Cancel. Goal Seek overwrites the changing cell with the solution, so if you want to keep your base input, note it first or run Goal Seek on a copy.
Goal Seek comes with one caution built in. It stops when it's "close enough" by its own convergence criteria. It can return 10.1003% where the true answer is a rounder number, or stop slightly short on a flat model. For a high-stakes number, tighten precision (File ▸ Options ▸ Formulas ▸ Maximum Change) or plug the result back in to check it. It also finds only one solution. If a model has two inputs that hit the target, as a quadratic payoff can, Goal Seek returns whichever it reaches from your starting point.