Goal Seek in Excel: Definition, Functions, and How to Use It

In this tutorial, you will learn about the goal seek feature in excel completely. We will discuss its function, example, and how to use it properly so it can help you with your data processing.

When we work in excel, we might sometimes find it difficult to get the input value to maximize our formula output. If we have mastered how to use goal seek, then we should find it much easier to deal with the problem.

Want to know more about this excel goal seek feature and how to utilize it for our cause? Read this tutorial until its last part!

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more

Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!

What is the Goal Seek in Excel?

Goal seek is an excel feature that helps you find the input value to get the formula output you want. With it, you should also be able to see the impact of an input on a formula output much easier.

Goal Seek Function

You can use goal seek to:

• Get the input value you should give to get the output you want from a formula
• See the cause and effect relationship between an input variable and the output of your formula
• Try several scenarios to optimize your output and input in a formula

In short, this goal seek feature is useful if you want to analyze the variables that work in your formula. The variables here should be quantitative.

Goal Seek Inputs

Here are the things you should input when you use goal seek in excel.

• Formula cell: The cell which contains the formula which input and output you want to analyze and optimize. The formula must be a calculation formula
• Output target: The output you want to get from your formula. The output must be a number
• Variable cell: The cell where goal seek should give its result. The result will be the input value you should give to your formula to get your output target (as close as possible to this output target if it cannot get the target fully). The input must be a number

Prepare those things before you use goal seek!

Goal Seek Implementation Example

To understand better about the goal seek feature in excel, let’s take a look at the following simple example. Let’s say we have these weekly sales quantities.



Currently, the average of the three weekly sales quantities is 762.333333. If we want to increase the average to 800, how many sales quantities should we target for week 4?



To get that week 4 sales quantity target, we can use goal seek to help us. In the goal seek dialog box, we input these into its text boxes.



As our average formula is in cell C6, we input this cell coordinate in the “Set Cell” text box. We also input the weekly sales quantities average target, 800, in the “To Value” text box. For the “By Changing Cell” text box, we input the week 4 sales quantity cell.

We click the OK button and we get 913 as the sales quantity we must target in week 4! That is if we want to reach 800 in the weekly sales quantity average.

How to Use Goal Seek in Excel

Looking for a step-by-step guide to help you use the goal seek feature in excel? The one below should help you with that!

1. Prepare the inputs you want to give to goal seek. Make sure you have the cell with the formula, the cell with the input for the formula, and the output target
   
   
   
   
2. Go to the Data tab, click the What-if Analysis dropdown button, and choose Goal Seek…
   
   
   
   
3. In the dialog box that shows up, input the cell with your formula in the “Set Cell” text box. To input it, type the cell coordinate or put your typing cursor in the text box and click the cell
   
   
   
   
4. In the “To Value” text box, input the output you want from the formula. The output must be a number
   
   
   
   
5. In the “By Changing Cell” text box, input the formula input cell which value you want to optimize. This input is the value you want to get from goal seek to get the output you want from the formula. As with the formula cell, you can type the cell coordinate directly or just click the cell
   
   
   
   
6. Click the OK button in the Goal Seek dialog box
   
   
   
   
7. Done! Goal Seek should begin to calculate the input value you want to get after you click its OK button. After a while, it should give you the value in the input cell you gave in its dialog box.
   
   
   
   Just click OK in the dialog box after you get the input value to finish using goal seek

Goal Seek Requirements

Based on the inputs you should give when you use goal seek, here are the requirements of this feature.

• One cell which contains a calculation formula
• One cell which becomes an input in the formula. Goal seek will try to find the optimum value of this input for you
• One target for the output of the formula. The target must be a number

Goal seek can only process one formula cell, one input cell, and one output target. Note the one quantity for each there as it cannot process multiple formulas, inputs, or targets.

Goal Seek Not Working? Possible Reasons & Solutions

Cannot get the result you desire by using the goal seek feature? The goal seek feature seems to not run properly in your excel?

There are many things that can cause that to happen. Here are three reasons which are probably the most possible ones and the solution for each of them.

• Reason: You don’t give proper inputs in the goal seek dialog box
  Solution: Check again your formula cell, output target, and input cell inputs in the goal seek dialog box. Have you entered the right input for each of them? Read again the goal seek inputs, steps, and requirements parts of this tutorial to guide you


• Reason: The constraints in your formula make you not find the input value to get your ideal formula outcome
  Solution: Because of some components in your formula, you might not get the exact output you want from your input value. However, if you have given correct inputs, goal seek should already offer the optimal input value. Check your formula components again and edit the constraints that cause you to not get the optimal outcome if possible


• Reason: You try to get multiple input values for your formulas by using goal seek
  Solution: Remember that goal seek can only give you one optimal input value for your formula output target. It cannot give you optimal values for more than one input. If you want something like that, you might like to try the solution in the next part of this tutorial

Check again for these three points to help you optimize the goal seek feature function!

Goal Seek for More than One Input Value: Solver Add-In

Need more than one input value from goal seek? Unfortunately, goal seek can only give you one input value at a time.

If you need to get multiple optimum input values at once, you may want to install the solver add-in. It is an Excel add-in from Microsoft which can help you with this kind of problem.

You can read an overview of the solver add-in from the Microsoft official page here. You can also find a link there that explains the way to install the add-in should there be a problem.

Exercise

After you have learned how to use the goal seek feature in excel, now let’s practice your understanding through this exercise!

Download the exercise file and answer all the following questions! Download the answer key file if you have done the exercise and want to check your answers!

Link to the exercise file:
Download here

Questions

1. What is the minimum fifth test score should Andy get to achieve his test score average target?
2. What is the minimum fifth test score should Julia get to achieve her test score average target?
3. What is the minimum fifth test score should James get to achieve his test score average target?

Link to the answer key file:
Download here

Additional Note

As the goal seek output is not dynamic, you should rerun it if you change some values in your formula.



Related tutorials you should learn from: