See How to Get Fast Answers With Excel Goal Seek

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:

DecisionVotes% of Votes
YES447863.90 *
NO253036.10
Total7008100

* 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%.

  1. 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.

Election results spreadsheet.Pin
  1. Click the cell you want to change.

    This cell is called the “Set Cell“. In my example, it will be cell D4.

  2. From the Data tab, select the What if Analysis… button.
  3. Select Goal Seek from the drop-down menu.
  4. 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.

Goal Seek dialog with Set Cell and new To value.Pin
Goal Seek input dialog
  1. 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.
Goal Seek dialog with change cell reference.Pin
  1. Click the OK button. Excel will overwrite the previous cell value with the new one.
Goal Seek dialog with matching Target and Current values.Pin
Goal Seek returns new values
  1. 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 error when set sell has no formula.Pin
Set cell error for missing formula

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%.

results showing current value less than target value.Pin
Excel didn’t quite reach 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.

Better results when formula removed from TOTAL cell.Pin
TOTAL value stayed constant.

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.

Pin

Goal Seek Example Spreadsheet