How to Use Goal Seek in Excel: Step-by-Step Guide (2024)
Excel’s goal seek function enables you to quickly simulate the input needed to get to a specific output.
This is much faster than changing the input values manually ⏳
Sounds complicated, right?
It’s not that bad, though. At least not after reading this guide 👍🏼
Download this sample Excel workbook to tag along.
Table of Contents
What is goal seek in Excel?
The goal seek function is a built-in what-if analysis tool in Microsoft Excel.
Goal seek helps to get one correct input value when only the desired value for the output is known.
The function essentially back-solves the problem by feeding in guesses until it finds the solution.
Here are some common examples where you can use goal seek.
- Change the number of units sold to get the target revenue.
- Change the loan amount to get the desired monthly payment for a given interest rate
- Find the minimum score to get the required overall grade.
How to use goal seek in Excel
Let’s learn how to use the goal seek function in Excel 👍🏻
- Go to the data tab.
- Expand the What-if analysis tools list in the forecast group.
- Click Goal seek to open the goal seek dialog box.
- Select the formula cell that you want to get the desired result into the set cell box.
- Enter the desired output in the “to value” cell box.
- Select the variable input cell for the “By changing cell” box.
- Click OK to get the goal seek status dialogue box.
The goal seek status will show whether it found a solution or not.
It will also show you, the new input value to get the given value for the target cell.
There are times when the goal seek function will not find a solution 😒
Then, the goal seek feature will get the closest value and show the status as “may not have found a solution.”
If you want to accept the goal seek solution as the new value, click OK. Otherwise, click “cancel”.
Pro Tip:
Are you sure there is a solution but still gets “may not have found a solution”? 🤔
Quickly check these…
- Check all the parameters and values
It means to check whether the cell reference of the input value is used for the formula result in the target cell.
It can be directly or indirectly.
- Change the iteration settings.
Follow the below path to go to that section.
File tab > Options > Formulas (from Excel options)
Then you have 2 options to change iteration settings.
-
- Maximum iterations – This is the number of possible solutions. Increase this number to get more possible solutions.
- Maximum change – Minimize this value to increase the possible solution’s accuracy. For example, you can change the default value of 0.001 to 0.000001.
- No circular reference
A circular reference happens when a formula of a cell has a direct or indirect cell reference for its own cell.
If the target cell has a circular reference, the Excel goal seek function is not working.
When to use goal seek
The goal seek Excel function performs what-if analysis for a single input cell.
If you want to do goal seek analysis with multiple input values and complex problems, you can use Excel Solver 🤹🏻
Below each example will show how to use goal seek in Excel.
Example 1
This example vote needs 66.67% in favor to pass ❎
This is the scenario:
Now, you need to find the minimum number of votes to pass a resolution.
After you select goal seek, fill in the details below.
- Set cell – C2 (The number of favorable votes percentage formula)
- To value – 66.67% (The required percentage)
- By changing cell – B2 (The number of favorable votes count)
When you click “Ok”, you will get the result.
Now you can see that to pass the resolution, there should be more than 100 favorable votes. If you want to get the solution to cell B2, click OK. If you want to keep the original value, click “Cancel”.
Example 2
You have the following template to find the revenue.
Now, you need to find the number of units you have to sell to achieve a revenue of $3,000 💰
After you select goal seek, fill in the details below.
- Set cell – C3 (The revenue cell)
- To value – 3,000 (The required revenue is $3,000)
- By changing cell – B2 (The number of units sold)
Click “Ok” to get the result.
Goal seek is showing that you need to change the quantity sold to 300 in order to get $3,000 in revenue.
That’s it – Now what?
Now you know how to use goal seek in Excel.
Well done! 🥳
But keep in mind that it is really important to build your formulas accurately to get the correct solutions 🦺
It is crucial to be proficient with the IF, SUMIF, and VLOOKUP functions because they are frequently used in financial modeling and what-if analysis.
Click here to access my free 30-minute online course where you can learn about IF, SUMIF, and VLOOKUP if you haven’t already.
Other resources
If you need to change more than 1 variable to achieve the expected result, read our article about the Excel Solver.
You can read our circular reference article to fix circular references before you apply the goal seek function in Excel.
Read Excel’s scenario manager article and data table Excel article to learn about Excel’s other what-if analysis tools.