Have you ever been stuck at work, staring at your spreadsheet and wondering how to find the answer to various “what-if” scenarios? Maybe you reached for a calculator or your phone. But what if there was an easier way? In this tutorial, I’ll show you how to use goal seek in Excel. (Includes video and example spreadsheet.)
The days after an election, people often ask “what-if” type questions. Some races are close, and counting can go on for days. In other cases, people want to know how many votes would’ve been needed to win. Learn how to use Goal Seek in Excel to answer these and other forecasting questions. And while this is a helpful feature, it comes with some limitations.
Article Contents
What is Goal Seek?
Goal Seek is a built-in Excel tool that allows you to see how one data item in a formula impacts another. You might look at these as “cause and effect” scenarios. It’s useful to answer “what if” type questions because you can adjust one cell entry to see how the results change. The tool is often used in finance, sales, and forecasting scenarios, but there are other uses.
For example, you might be looking at your local election results and see:
| 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 value of one variable and see how the results vary. 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 team 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 cell values to see the results, Goal Seek can find the answers.
While you might think this Excel feature can be used with the formula bar, it can’t. There is no such thing as an Excel Goal Seek formula. Instead, there is a dedicated dialog. And sadly, there is no goal seek shortcut.
How to Use Excel Goal Seek
Time needed: 5 minutes.
- Create a spreadsheet in Excel that has your data. In the example below, the green cells have formulas to calculate the percentage and sum totals.
- Click the cell you want to change. This is called the “Set cell.” In my example, this will be D4.
- From the Data tab, select the What if Analysis… button.
- Select Goal seek… from the drop-down menu.
- In the Goal Seek dialog, enter the new “what if” amount in the To value: text box.
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. Technically, we need 66.7% to win, but since Goal Seek has an approximation algorithm, I don’t want to run the risk of Excel rounding down. - We also need to tell Excel which cell to change. Since we wanted to know the number of YES votes, we’ll click C4.
- Click OK. Excel will overwrite the previous cell value with the new one.
- If you wish to accept the new value, click OK.
Goal Seek Requirements & Issues
As with many simple tools, there are limitations. This is true of Goal Seek. Excel only allows you to change one variable. This may be fine for many scenarios, but it presents a problem here. If you look at the latest figures, you’ll see it also increased other input values, such as the total vote count. Ideally, I would want to add a constraint and not have my total vote count go over the current count.
Excel also requires that the Set cell be a formula cell. In my case, I have =C4/C6 to get a percent.
I also noticed that I ran into two additional issues when I formatted column D as a percentage. The first was I had to add the % sign in the To value:. Otherwise, the tool would go off and do many calculations and give a bogus answer. Moreover, I ran into the approximation issue, and 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 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. So, for example, if I typed “7008” in cell C6, only the YES cell value would have changed.
The other requirement is the cell you change in Step 6 can’t contain a formula. Instead, it must be a typed value.
As this example shows, Goal Seek is a nice tool that quickly finds the answers to different situations. Although this was a simple example, you can’t use the same tool for complicated spreadsheets containing many variables and formulas. For that, you should use Excel Solver, which is an Add On. However, it’s a nice simple tool when you need to find the solution to what-if scenarios with items such as annual interest rates or sales goals.
Show Me How Video
Please click the image below to be taken to the video tutorial and transcription.
