Excel Goal Seek Function Guide: Definition, How-to Steps and Examples

In this article, we’ll try to explain how to use Goal Seek function in Excel with step-by-step guiding. So, once you have the basic understanding of this amazing tool, you’ll be able to make complex analysis in seconds.

Table Of Content

1. What’s Goal Seek Function in Excel?
2. How to use Goal Seek Function in Excel?
3. Examples of Using Goal Seek in Excel
4. Why is Goal Seek not working in Excel?
5. Conclusion

Let’s start our guide for goal seek analysis tool.

1. What’s Goal Seek Function in Excel?

Firstly, the Goal Seek function is one of the pre-built What-if analysis tools in Excel.

In general, Microsoft Excel provides users three important What-if Analysis tools:

• Scenario Manager
• Goal Seek
• Data Table

So, among these tools, the Goal Seek function helps you to calculate your variable values to attain your target. In summary, you input your end goal, and it finds out the changing variables to match your target.

2. How to use Goal Seek Function in Excel?

Firstly, Goal Seek is highly straightforward analysis tool. As long as you understand the basics of this tool, you can conduct complex analysis easily.

Let’s start our step-by-step guide to better understand this tool.

We’ll use a simple example: We’ll suppose that we want to reach $50.000 profit with the variables of unit cost, unit price, number of units sold. The profit is simply calculated as follow:

Step 1: Select your Goal Cell

Firstly, select your Goal cell. This is the profit calculation in our case.

Step 2: Open your Goal Seek tool

Secondly, go to Data > What-Is Analysis > Goal Seek.

Step 3: Configure your analysis values

Then, in the Goal Seek dialog box, you’ll find three areas to configure.

What are Excel Goal Seek Parameters?

• Set Cell: This is your Goal cell. This will be your target value.
• To Value: This is your target value.
• By changing cell: This is your variable cell. This will be calculated by the tool.

Thus, in our example, the set cell will be our formulated profit cell. Our To Value parameter will be 50.000 as we target to reach this value in profits. And our changing value might be the # of units sold.

Here’s our first question: How many units should I sell to reach a profit of 50.000?

Step 4: Checking the results

Lastly, Excel will bring you a solution box:

As you see, in our example, we will have to sell 1.000 units to reach a profit of $50.000 with the given unit cost and unit price.

Step 5: Try different scenarios

Additionally, we’ll try different scenarios with our variables.

Here’s another question to test: What should be my Unit Price to reach a profit of $50.000 if I have a capacity of 500 units.

So, we’ll select the By Changing Cell parameter as the Unit Price this time:

After clicking on Ok, Excel will give us the solution:

As you see, we should sell our products at $200 to reach our target profit.

>> Download Sales Dashboard Excel Template for Profitability Calculations

3. Examples of Using Goal Seek in Excel

In the step-by-step instructions, we have used an profit goal example. Let’s give more real-life case studies.

Example 1: Calculate Loan Payments

Question: I will make a credit payment calculation. I have a budget of $5.000 as monthly payment. The interest rate is 8%, and I’ll pay back in 120 months. What’s the loan amount that I can pay in these conditions?

So, we have calculated Monthly Payment with PMT Function. And then use the Goal Seek function to calculate our ideal loan amount.

Example 2: Calculate GPA

Question: I need a minimum score of 70 as GPA to continue the scholarship. Also, all the exams have been completed, except for the Math test. What is the Math score I need to keep my scholarship?

So, we have calculated our GPA formula using SUMPRODUCT and SUM functions:

Lastly, we’ll work Goal Seek function:

In result, we need a score of 59 to attain an average GPA of 70.

Finally, we can multiply examples with real life examples. Here are some of the other examples that you can use this very useful Goal Seek function:

• Revenue calculations
• Finding the champions in sports tournaments
• Election results
• ROI calculations
• Investment analysis
• And much more…

4. Why is Goal Seek not working in Excel?

Sometimes we have questions from our users about the Goal Seek function not working. So, here are some possible issues that you might have:

a. Iteration settings

Firstly, your iteration settings might be limiting your Goal Seek function. If this is the case, you can adjust you maximum iteration number and maximum change numbers from your workbook settings.

Go to File > Options > Formulas to configure your options:

• You can increase the Maximum iteration number to make more possible solutions
• You can decrease the maximum change number to get more accuracy.

b. Revisit your Goal Seek Parameters and Formulas

Also, it’s possible that your formula is not accurate or you have not set your goal seek parameters correctly. So, please check the instruction above and double check your Goal Seek dialog box.

Additionally, please be sure that your formulas do not give any circular references, as this will prevent your goal seek function working smoothly.

5. Conclusion

Finally, in this article, we have tried to explain how to use Goal Seek function in Excel. As one of the most important pre-built what-if analysis tools in Microsoft Excel, Goal Seek allows to make quick calculations and find out your ideal variable values to reach your targets.

In summary, this analysis tool works a bit reversely. Thus, you set your target, and it calculates what should be your variable to reach that target.

So, we have tried to explain this useful tool with real-life examples and images. Hope you enjoy our article!

Recommended Readings:

How to use Excel Solver for Linear Programming?

Can Excel Extract Data From Website?

How to Create an Expense Report in Excel? Easy, Quick and Professional Instructions