When working with financial models or complex calculations in Microsoft Excel, you typically start with a set of variables to calculate a final result. However, there are many scenarios where you know the exact result you want, but you are unsure which variable will get you there.
For example, you might know you want a final exam grade of 85%, but need to know what score you must achieve on the final test. Or you know you can only afford a $500 monthly car payment, and need to calculate the maximum vehicle price you can afford. Instead of manually changing numbers and guessing until you hit your target, you can use Excel’s powerful “Goal Seek” feature to instantly reverse-engineer the math.
Understanding How Goal Seek Works
Goal Seek is a “What-If Analysis” tool. It works by taking a formula cell, forcing it to reach a specific target value, and automatically adjusting one of the input cells connected to that formula until the math balances out perfectly.
To use Goal Seek, your spreadsheet must meet three strict conditions:
- You must have a cell containing a formula.
- You must have a target value you want that formula to equal.
- You must have a single variable cell (which the formula relies on) that Excel is allowed to change.
How to Use Goal Seek (A Practical Example)
Let us imagine you are calculating the profit margin for a new product. You have the Cost Price ($50) in cell A2, the Selling Price ($70) in cell B2, and the Profit ($20) calculated in cell C2 using the formula =B2-A2.
Your boss tells you that the business absolutely needs to make exactly $35 profit per unit. You need to know what the Selling Price must be changed to in order to hit that $35 goal.
- Click on the Data tab in the main Excel ribbon.
- In the “Forecast” group, click on What-If Analysis.
- Select Goal Seek… from the drop-down menu.
A small dialogue box will appear asking for three parameters.
1. Set cell:
This must be the cell containing the formula you want to reach a specific number. In our example, click on cell C2 (the Profit cell).
2. To value:
This is the exact number you are aiming for. Type 35 into the text box.
3. By changing cell:
This is the variable cell Excel is allowed to modify to make the math work. In our example, click on cell B2 (the Selling Price cell).
Executing the Calculation
Once you have filled in all three boxes, click the OK button.
Excel will rapidly test hundreds of numbers in the background. Within a fraction of a second, a “Goal Seek Status” box will pop up announcing it has found a solution. Look at your spreadsheet: Cell B2 (Selling Price) will have automatically changed to $85, and Cell C2 (Profit) will now equal exactly $35.
If you are happy with the new calculation, click OK to keep the changes. If you want to revert to your original numbers, click Cancel.
Limitations of Goal Seek
While Goal Seek is incredibly fast, it is a relatively simple tool. It can only adjust one variable cell at a time. If you need to hit a target profit but want Excel to adjust the Selling Price, the Cost of Materials, and the Shipping Cost simultaneously based on specific constraints, Goal Seek cannot handle the complexity. For those multi-variable problems, you will need to activate the more advanced “Solver” add-in within Excel.