• Skip to main content
  • Skip to secondary menu
Productivity Portfolio logo

Productivity Portfolio

Valuing your time & technology

  • HOME
  • Tutorials
    • Excel
    • Google
    • Word
    • Outlook
    • Terms
  • Quick Tips
    • Email
    • Security
    • Web
    • Windows
    • Internet of Things
    • Cell
  • Reviews
    • Health
    • Services
    • Software
    • Hardware
  • About
    • About Me
    • Contact Page
    • Privacy Policy
  • Newsletter
  • Resources
    • Practical Gift Ideas
    • Books
    • Favorite Podcasts
    • Online Courses
    • Online Services
  • SEARCH
You are here: Home › Tutorials › Excel › Excel Goal Seek Offers Quick Answers

Excel Goal Seek Offers Quick Answers

LAST UPDATED: November 7, 2020 By Anne Hennegar

TwitterPinterestLinkedInRedditFacebookPocketEmail

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 answering these and other forecasting questions.

Article Contents

  • What is Goal Seek?
  • How to Use Excel Goal Seek
  • Goal Seek Requirements & Gotchas

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 the result. 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
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 value of one variable 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 team have won?

In each of these questions, the goal is to change one data value to see if the YES percentage went over that two-thirds mark or 67%. Rather than haphazardly changing cell values to see the results, Goal Seek can find the answers.

How to Use Excel Goal Seek

  1. 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.
  2. Election results spreadsheet.Pin
    Final election results
  3. Click the cell you want to change. This is called the “Set cell”. In my example, this will be D4.
  4. From the Data tab, select the What if Analysis… button.
  5. Select Goal seek… from the drop down menu.
  6. In the Goal Seek dialog, enter the new “what if” amount in the To value: text box.
  7. Goal Seek dialog with Set Cell and new To value.Pin
    Goal Seek input dialog

    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.

  8. We also need to tell Excel which cell to change. Since we wanted to know the number of YES votes, we’ll click C4.
  9. Goal Seek dialog with change cell reference.Pin
    Changing the percentage value to see the impact
  10. Click OK. Excel will overwrite the previous cell value with the new one.
  11. Goal Seek dialog with matching Target and Current values.Pin
    Goal Seek returns new values
  12. If you wish to accept the new value, click OK.

Goal Seek Requirements & Gotchas

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 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. In my case I have =C4/C6 to get a percent.

Goal seek error when set sell has no formula.Pin
Set cell error for missing formula

I’ve also noticed that when I formatted column D as a percentage, I ran into 2 additional issues. The first was I had to add the % sign in the To value:. Otherwise, the tool would go off and do a large number of calculations and give a bogus answer. Moreover, I ran into the approximation issue and Goal Seek rounded down my percentage to below 67%.

Goal Seek dialog with current value less than target value.Pin
Using % brought Current value to less than Target value

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. 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. It must be a typed value.

As this example shows, Goal Seek is a nice tool that can quickly find 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.

You Might Also Like These Excel Tutorials

  • Beginner’s Guide to XLOOKUP
  • How to Use Excel VLOOKUP
  • How to Use Excel Auto Filter
Excel 2019 All-in-One For Dummies
Harvey, Greg (Author); English (Publication Language); 816 Pages - 10/26/2018 (Publication Date) - For Dummies (Publisher)
$18.99

Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-01-14

TwitterPinterestLinkedInRedditFacebookPocketEmail

Filed Under: Excel, Tutorials

© 2021 · Productivity Portfolio | PO BOX 117361 Burlingame, CA 94011

I use cookies to ensure that I give you the best experience on our website. If you continue to use this site I'll figure that you are happy with that. You can find more details in the privacy policy.OKNoPrivacy policy