How to use Goal Seek in Excel for “What-If” analysis
What is Goal Seek?
Goal Seek is a tool in Excel that you can use to find out what input value you should put in one cell in order to achieve a desired output in another cell. You can think of it as Excel doing the leg work for you by running a series of “trial and error” scenarios until it reaches the right answer. It’s most frequently used when a calculation is too unwieldy to find an exact answer mathematically.
Example. Let’s say you have the sales figures for the last three quarters and you want to know how many sales you need to make in the final quarter to reach an overall £100,000 annual net profit. You can use Goal Seek to find the answer.
Tip. Goal Seek can process only one input value at a time. If your forecast model has multiple input values, you can use Excel’s Solver add-in to find the optimal solution (see The next step ).
How to use Goal Seek
Step 1. Select the Goal Seek command. There are two ways to access Goal Seek. You can navigate up to the Data tab in the ribbon menu and select Goal Seek from the What-If Analysis drop-down menu. Alternatively, it can be accessed through the Tools menu (Tools/Goal Seek).
Step 2. Input the desired values. Once you have selected Goal Seek, a box will open which requires input for three options:
Set cell. This is the address of the output cell you want to achieve a specific result. In order for this process to work, there should be a formula in this cell (but don’t worry, Goal Seek won’t change the formula itself). In our example, this would be the cell that calculates the total net profit for the year (D8) (see The next step ).
To value. Here you need to input the desired value you want to see in the place identified in Set cell. This should be a number, not a cell reference. In our example, this would be £100,000.
By changing cell. This is the input cell you want to change to achieve the result you are looking for. This cell should be a normal number cell not a formula and should feed into the Set cell either directly or through other formulae. In our example, this would be the sales figure for quarter 4 (B7).
Tip. If you want to fix the cell references, then Excel will automatically put in the dollar signs if you click on the appropriate cells rather than manually entering the cell references.
Step 3. Click OK and Goal Seek will show the Goal Seek Status screen. You will see that this has adjusted the “Set cell” and the “By changing cell”. If you want to change your cells to these values, select OK on the status screen. If not, click Cancel.
Our scenario is a very simple example to illustrate the tool, but Goal Seek also works with complex models involving multiple sheets and many interlinked formulae.
Solver add-in install instructions
Goal Seek example
Create your profile to unlock this advice and many more.