Have you ever stared at your spreadsheet and wondered how to find the answer to a “what-if” scenario? Let’s start with the goal in mind. In this tutorial, you’ll learn how to use Goal Seek in Excel to answer these and other analysis questions. (Includes video and example spreadsheet.)
What is Goal Seek in Excel?
Goal seek is a built-in function in Microsoft Excel that allows you to see which data item in a formula affects another. You might look at these as “cause and effect” scenarios. It’s sometimes called sensitivity analysis. The “what-if” analysis tool is often used in finance or sales scenarios, such as determining a monthly payment or bonus calculations. There are other uses for it, too.
For example, you might be looking at your local election results and see:
| Decision | Votes | % of Votes |
|---|---|---|
| YES | 4478 | 63.90 * |
| NO | 2530 | 36.10 |
| Total | 7008 | 100 |
* Needs approval from 2/3 of the voters
In our example, the YES votes are a majority but shy of the required 2/3 approval to win the election. People quickly realize they were close, but which item do they change to find out how close. What would’ve made a difference?
Using Goal Seek, we can change the input value of one cell and see how the results change. This would allow you to answer these types of questions.
- How many more YES votes were needed to win the election?
- If 500 more people voted, could the YES voters have won?
The goal of each of these questions is to change one data value to see if the YES percentage went over that two-thirds mark, or 67%. Then, rather than haphazardly changing original values to see the results, Goal Seek can find the answer. It works in the background doing iterative calculations.
While you might think this feature can be used with the Excel formula bar, it can’t. There is no such thing as an Excel Goal Seek formula. Instead, there is a dedicated dialog box with defined fields.
Keyboard Shortcut: Alt + D + W + G
How to Use Excel Goal Seek
Time needed: 5 minutes.
This tutorial will show you how to invoke the Goal Seek dialog box and fill in the values for your “what-if” scenario. In this example, I will change the YES%.
- Create an Excel spreadsheet with your data or download the practice worksheet.
In the snapshot below, the green cells have formulas to calculate the percentage and totals.
- Click the cell you want to change.
This cell is called the “Set Cell“. In my example, it will be cell D4.
- From the Data tab, select the What if Analysis… button.
- Select Goal Seek from the drop-down menu.
- In the Goal Seek dialog box, enter the new “what if” amount in the To value: field.
In this example, we’re asking Excel to replace the contents of cell D4, which is 0.64, with 0.67. This is the percentage needed to win the election.
- Click the cell you wish to change. Since we wanted to know the number of “Yes” votes needed, we’ll click cell C4. This is our input cell.
- Click the OK button. Excel will overwrite the previous cell value with the new one.
- If you wish to accept the new value, click OK. Otherwise, click Cancel.
Goal Seek Requirements
As with many simple tools, there are limitations. To start, the tool only works on desktop versions, so if you’re using the web version or on an Ipad, you’re out of luck.
Excel also requires that the Set cell be a formula cell. In my case, I have =C4/C6 to get a percent.
Excel only allows you to change one variable input. The other requirement is that the cell you change in Step 6 (By changing cell:) can’t contain a formula. Instead, it must be a typed value.
Approximation Issues & Formulas
This tool is fine for many scenarios, but it presents a problem here. If you look at the latest figures, you’ll see it also increased the TOTAL vote count. Ideally, I would want to add a constraint and not have my TOTAL vote count go over the current count.
You may have noticed, that I ran into the approximation issue. Goal Seek rounded down my percentage to below 67%.
Just as I used a formula in my set cell, I also used an Excel formula cell for TOTAL in B6. This was the SUM of my YES and NO votes. Since this TOTAL cell was a formula, my TOTAL count automatically adjusted when Goal Seek changed the YES cell value.
The solution is to remove my formula and type “7008” in cell C6. This is a more desirable outcome.
While it doesn’t happen often, the tool can sometimes present puzzling results. I’ve seen reports, where some Add-on, has interfered in the calculation. If that happens to you, I would close Excel and open it in Safe Mode.
As this example shows, Goal Seek is a useful “What if” analysis tool that quickly finds the answers for different scenarios. If you’re working with complicated spreadsheets containing many different variables and formulas, then using an add-on called the Excel Solver may be a better solution. However, Goal Seek is a fast solution to lots of scenarios with items such as annual interest rates or sales goals.
Excel Goal Seek Video
Please click the image below to be taken to the video tutorial page and transcription.
