How to use Goal Seek in Excel
to do WhatIf Analysis

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

‘Goal Seek’ is an awesome ‘What-If Analysis’ tool in Excel.

What-If Analysis helps you see how changing a cell’s value can affect the overall outcome of an associated formula.

There are three available ‘What-If Analysis’ tools: 

  • Scenarios
  • Goal Seek
  • Data Tables

In this tutorial, we’ll talk about the ‘Goal Seek’ tool in Excel and we’ll show you how you can use this tool to solve real-life problems.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get this started!

What is the Goal Seek tool?

‘Goal Seek’ is a built-in tool in Excel that would help you find the right value to get your desired result.

You can think of it as some sort of a reverse tool wherein you have the (desired) result but you don’t have the right value to get at that result. Normally, the result is what you have to find from the data set.

Here are a few examples of real-life problems that can be solved by the Goal Seek tool:

  • Finding out the passing grade
  • Needed votes for the election
  • Finding the affordable loan amount (or interest rate)

Don’t worry. We’ll run through all of them later.

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

How to use Goal Seek in Excel

The Goal Seek tool is simple to use.

There are three parameters you should know about:

  • Set cell (the formula of the result)
  • To value (the desired result of the formula)
  • By changing cell (the value you want to adjust)

Now, let’s apply them in a real-life scenario.

This student whose grades are shown below would like to know how much he needs to score on the last quiz so he’ll get at least an average grade of 75. Currently, his average stands at a failing grade, 71.50.

a data set with 5 quiz grades with their average where the quiz 5 is blank

The first thing to do is to click ‘Data’ on the tab list.

data from the tab list

From there, click the ‘What-If Analysis’ icon on the ‘Forecast’ group. Select ‘Goal Seek’ from the dropdown.

the goal seek dropdown option under what-if analysis

After that, the ‘Goal Seek’ window will open where you input the needed parameters.

the goal seek window with its 3 required parameters

From the data set, enter the appropriate reference and value on the window.

  • Set cell: B7 (the location of the formula)
  • To value: 75 (the desired result)
  • By changing cell: B6 (the value you want to change or adjust)
the quiz grades data set with a goal seek window open filled with correct parameters

Then, hit ‘Enter’ and watch the magic of ‘Goal Seek’ unfold!

running goal seek on the quiz grades data set yielding the value 89

The student needs to score at least 89 on the last quiz if he hopes to pass. 😊

Kasper Langmann, Co-founder of Spreadsheeto

Goal Seek on election analysis

‘Goal Seek’ can also be used to calculate the number of votes needed to win.

Let’s say you’re running for a local position where you need two-thirds (66.7%) of the majority to win. As of now, you have 256 voters out of 850 voters.

How many votes do you need to win (get at least 66.7% votes out of the total votes)?

election results data set with the yes and total votes as well as their percentages

Here’s what you should enter on the ‘Goal Seek’ window:

  • Set cell: C2 (the location of the formula)
  • To value: 0.667 (the desired result)
  • By changing cell: B2 (the value you want to change or adjust)
the election results data set with an opened goal seek window filled with the right parameters

Then, press ‘Enter’ to run the calculation.

running goal seek on the election results data set with 567 as the result

You need 567 votes to win.

Using Goal Seek in Payments

First off, there’s actually a way you can calculate your periodic loan payment in Excel through the PMT function.

But what if you’re still evaluating how much you should loan or at what interest depending on your income?

For example, you can only afford to pay $5,000 annually for 5 years with an 8% interest. Now, you want to know how much money you can borrow from those conditions.

(Don’t worry about the current ‘Annual Payment’. It’s automatically calculated based on the values above it.)

data set for loan payment

Here’s what you should enter on the ‘Goal Seek’ window:

  • Set cell: B5 (the location of the formula)
  • To value: -5000 (the desired result)
  • By changing cell: B4 (the value you want to change or adjust)

B5 contains the PMT equation: =PMT(rate, nper, pv, [fv], [type])

The value is negative to be in line with the cash flow model. The value is what you’re supposed to ‘pay’ annually.

loan payments data set with the goal seek window opened filled with correct parameters

Hit ‘Enter’ to run the tool.

running goal seek on loan payment resulting to 21,647

The maximum amount you can loan if you want to stay within the budget is $21,647 (rounded to the nearest integer).

But what if it’s about the interest rate? Like you have already decided to loan $20,000 payable in 10 years and you would like to pay $3,000 yearly.

Kasper Langmann, Co-founder of Spreadsheeto

You now like to know the annual interest rate to make this arrangement possible.

(Don’t worry about the current ‘Annual Payment’. It’s automatically calculated based on the values above it.)

data set for loan payment finding out the best interest

Here’s what you should enter on the ‘Goal Seek’ window:

  • Set cell: B5 (the location of the formula)
  • To value: -3000 (the desired result)
  • By changing cell: B2 (the value you want to change or adjust)
data set for loan payments interest with a goal seek window filled with the right parameters

Hit ‘Enter’ to see the results!

running goal seek to find out the best interest for loan payment

The answer is 8%.

Error: Not have found a solution

As with any other functions or tools in Excel, it’s possible that something could go wrong.

There are times when ‘Goal Seek’ returns a ‘not have found a solution’. This could be due to the fact that a solution doesn’t really exist.

Kasper Langmann, Co-founder of Spreadsheeto
error: not have found a solution

However, if you’re sure that a solution does exist, there are a few things you can check:

1. Check the parameters and values

Every time your handling a function and a tool that includes references, make sure your reference is accurate. If not, whatever adjustment you make, you’ll always get the wrong answer.

2. Change iterative calculation

There’s a setting in Excel where you can adjust the number of possible solutions Excel will calculate as well as its accuracy.

To change them, click ‘File’ from the tab list.

file from the tab list

Then, click ‘Options’ at the bottom.

select options from the file tab

On the ‘Excel Options’ window, click ‘Formulas’ at the left-hand sidebar of the window.

the formula option of excel

There are 2 settings you can tweak to adjust the iterative calculation:

  • Maximum Iterations (the number of possible solutions; the higher the number the more iterations)
  • Maximum Change (accuracy; the lower the number the higher the accuracy)
iterative calculation options

3. No circular reference

A circular reference occurs when a formula refers back to its own cell directly or indirectly. ‘Goal Seek’ doesn’t work when there are co-dependent formulas involved.

Conclusion

What-If Analysis tools like ‘Goal Seek’ is one of the most useful features in Excel yet also one of the least used and unpopular.

Learning how to use such a tool will save you time doing trial and error trying to come up with the right value to get the desired result.

Kasper Langmann, Co-founder of Spreadsheeto