Is analyzing different spreadsheet scenarios a nightmare for you? Excel Goal Seek simplifies this process. Just tweak one variable, and see how it impacts other data points. In this guide, I’ll provide easy, step-by-step instructions on how to effectively use Excel Goal Seek to enhance your analyses. Plus, access a handy downloadable practice file and instructional video.
Knowledge You’ll Gain
- Understand Goal Seek: Grasp the basics and limitations in Excel.
- Preparation Steps: Learn to set up your data for Goal Seek.
- Simple Instructions: Follow easy steps to use the tool.
- Real-World Example: See how changes impacted election scenario
- Learning Resources: Access a practice file and video tutorial.
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 monthly payments or bonus calculations. It is also part of several “what if analysis” forecasting tools that include: Scenario Manager and Data Tables.
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 |
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 to winning, but which item do they change to find out how close? What would’ve made a difference?
Using Excel 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?
Each question aims to change one data value to see if the YES percentage exceeded 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 until it finds an answer.
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 Function
Time needed: 5 minutes.
This tutorial will show you how to use 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 at the bottom of this article. 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, I’m asking Excel to replace the contents of cell D4, which is 63.90%, with 0.67. This is the percentage needed to win the election. The reason I used 0.67 instead of 67% is because Excel stores percentages as a decimal. If you try to enter 67%, Excel will not find a solution.
- 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.
Excel Goal Seek Requirements
As with many simple tools, there are limitations. 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. However, if I were to change to 63.9%, Microsoft would provide an error.
Approximation Issues & Formulas
This tool is fine for many scenarios but presents a problem here. Did you catch it? 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%. That won’t work either.
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.
A better solution is to remove my formula and type “7008” in cell C6. This produces a more desirable outcome.
However, it’s not perfect. If you add up the % column values I exceeded 100%. One limitation of Excel Goal Seek is you can change only one cell value and can’t add any constraints such as don’t exceed 7008 votes.
While it doesn’t happen often, the tool can sometimes present puzzling results. I’ve seen reports where some Add-on has interfered with the calculation. If that happens to you, I would close Excel and open it in Safe Mode.
As you’ve seen, Excel Goal Seek can be an excellent tool for ‘what-if’ analysis, adept at addressing a range of scenarios from elections to sales goals. While it provides a quick solution for many scenarios, our Excel Solver tutorial may be better suited for more complex data involving multiple variables.
To reinforce your understanding and see Excel Goal Seek in action, watch our informative video. It offers a visual walkthrough, enhanced by our downloadable practice file. Download the spreadsheet now, and begin exploring the full potential of Excel Goal Seek today.
Excel Goal Seek Video
Please click the image below to be taken to the video tutorial page and transcription.
