When working with financial models or complex formulas in Microsoft Excel, you often know the final result you want, but you don’t know the exact input required to achieve it. For example, you might know your target monthly mortgage payment, but you need to figure out the maximum home price you can afford. Instead of manually guessing numbers through trial and error, Excel’s Goal Seek tool automates the process by calculating backward.
What is Goal Seek?
Goal Seek is part of Excel’s “What-If Analysis” suite. It works by taking a formula cell (the goal) and adjusting a single input cell until the formula produces the exact target value you specify.
How to Use Goal Seek
Let’s use a business scenario: You are calculating the total revenue for a product. Your formula in cell B3 multiplies the “Units Sold” (cell B1) by the “Price per Unit” (cell B2).
Currently, you sell 1,000 units at $50 each, yielding $50,000 in revenue. Your target revenue for the next quarter is $75,000, and you cannot change the price. You need to know exactly how many units you must sell.
- Navigate to the Data tab on the Excel ribbon.
- In the “Forecast” group, click the What-If Analysis button.
- Select Goal Seek… from the drop-down menu. The Goal Seek dialog box will appear, requiring three inputs.
- Set cell: This is the cell containing the formula you want to resolve. Click on your total revenue cell (
B3). - To value: This is your target number. Type in
75000. - By changing cell: This is the variable Excel is allowed to adjust. Click on the “Units Sold” cell (
B1). - Click OK.
Reviewing the Results
Excel will instantly run through hundreds of calculations and update the spreadsheet. The “Goal Seek Status” window will appear, confirming that it found a solution.
In this example, cell B1 will update from 1,000 to 1,500, showing that you must sell exactly 1,500 units to hit your $75,000 revenue target.
If you click OK, Excel will keep the new calculated value in the cell. If you click Cancel, Excel will revert the spreadsheet back to your original numbers, allowing you to use Goal Seek purely for exploratory analysis without permanently altering your financial model.