What is Excel Goal Seek and How to Use it

Excel Goal Seek Analysis

Goal Seek is an Excel feature (one of the what-if analysis tools) used for determining a value based on your desired goal. It means we’ve set our goals but don’t know what we need to do to reach them.

Also, with Goal Seek, you can compare different results to decide which best suits your conditions. 

This tool has been available in Excel since the 2010 version and made Excel a more powerful tool for Sales Management.

What-If Analysis

Excel data analysis tools help users obtain reliable data and analyze the output considering different situations. For instance, a data set may consist of numbers that may not be understood initially; using tools such as What-If Analysis helps analyze and explore different outcomes. 

What-If Analysis has three main options:

• Scenario Manager
• Goal Seek
• Data Table

Scenario Manager and Data Table take a set of input values ​​and determine possible outcomes. This tutorial will explain what Goal Seek is and how it works.

What is Goal Seek in Excel?

 Assume we have the result of a function or formula (a certain number), and we want to know what the input of this function should be to get the desired result. The Goal Seek is a tool to show how a value affects other values in a formula.

Goal Seek Formula

Based on what we use Goal Seek for, the formula can change. For example, if you use the feature to calculate 40.2* ??= 62.65, the formula will be 62.65/40.2 to reach the answer. But the procedure could be more complicated for calculating the number of products you need to sell to reach $1000 in profit.

How to Find Goal Seek in Excel?

To find the Goal Seek button in Excel, follow these steps:

1. Go to the Data tab from the Ribbon.
2. Go to the Forecast section.
3. You can find the Goal Seek option under the What-If Analysis tool.

Goal Seek Inputs in Excel

The Goal Seek does all the calculations behind the scenes and only asks for these three parameters:

1. Set Cell: the cell that contains your formula.
2. To value: the goal amount.
3. By changing cell [of your unknown variable]: the cell that changes according to the goal value..

Goal Seek Analysis in Excel with Examples

Consider this straightforward example to understand the Goal Seek feature in Excel. Assume you need 2820 points to pass a course. This course contains ten quizzes, and you passed nine of them. Now you want to know how many points you need for the final quiz to pass the course. To calculate the final quiz point, follow these steps:

1. Go to the Data tab from the Ribbon.
2. From the Forecast section, click What-if Analysis.
3. Select Goal Seek from the menu.
4. Set the Goal Seek parameters:

• Set cell: the current Course average (based on current points)
• To value: in this example, 2820
• By changing cell: the final quiz point (which is unknown)

5. Press OK.

Let’s see another example:

Suppose your business goal is to have 500,000$ profit for the next month. Each product yields $200 in revenue and $100 in variable cost, and your fixed costs are $250,000. Let’s see how we can use the Goal Seek to see how many products should be sold to reach the goal.

Goal Profit= $500,000

Quantity of product=?

Profit of each product=$200

The cost of each product=$100

Fixed costs= $250,000

Now let’s assume the quantity of product is one and calculate the total profit.

To calculate the total profit, we need to subtract the costs from the profit, so the formula would be:

=(B1*B2)-(B1*B3)-B4

Total profit of producing just 1 unit of the product = -249900

Now we open goal seek and enter the following values:

• Set cell: In this case, is B5
• To value: In this case, it is 500000
• By changing cell: In this case, is B1

Having the total profit for producing one product, Excel calculates how many products are needed to reach 500,000$ in profit. In this case, the quantity of the product should be 7500.

Automatic Goal Seek in Excel

So far, we have figured out what goal seek is and how it works. We set the three parameters, and Excel finds the answer. But if you change one of the cells in your worksheet, the final result doesn’t change. So, the scenario is to find a way for the Goal Seek feature to do the calculation automatically after a cell is changed.

You must record a Macro or use VBA to automate the Goal Seek feature in Excel.

Creating  Automatic Goal Seek in Excel Using Macro

A repetitive process  (for instance, calculating values by Goal Seek more than ten times) could be a daunting task. But recording the process can help Excel do the calculation automatically.  This is where Macro will help us. You can press the Record Macro button and save the process. You’ll find the button following these steps.

1. Go to the File tab from the Ribbon. (In Excel 2019, you need to click on More as well)
2. Click Options.
3. Click Customize Ribbon from the left menu.
4. Check the Developer checkbox under the Main Tabs menu.
5. Press OK.
6. Go to the Developer tab from the Ribbon.
7. In the Code section, press the Record Macro button.
8. In the Record Macro dialogue box, set a name (for example, GoalSeek)
9. Press OK.
10.  Whenever you do all the steps of your process, press the Stop Recording button.

Now, if you click the Macro button in the Code section, you can see the macro’s name that you recorded.

Automating  Goal Seek in Excel Using VBA

After recording the Goal Seek process using Macro, you can open VBA by pressing Alt+F11 or going to the Developer tab and pressing the Macro button from the Code section. Then, select the macro that you recorded for the process and click the Step Into button. Having followed these steps,  Goal Seek will become automatic.

1. Go to the VBA project menu (the left menu).
2. Click Modules.
3. Double-click on Module 1.
4. Copy the code line in the Module1 (Code) window. 

For example: Range(“B13”).GoalSeek Goal:=2820, ChangingCell:=Range(“B11”)

5. Return to the VBA project menu.
6. Under the Microsoft Excel objects, double-click on the sheet name.
7. On the top of the Sheet (Code) window, you have two lists.
8. Choose Worksheet under the right list and Change under the left.
9. Paste the last code you’ve copied under the Private Sub line in the first section.

For example: 

Private Sub Worksheet_Change(ByVal Target As Range)

      Range(“B13”).GoalSeek Goal:=2820, ChangingCell:=Range(“B11”)

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

10. The unknown value is adjusted according to the goal, and you can set any goal you want by changing the goal cell.

Bottom Line

Goal Seek is used for your business or personal management. Whenever you know the result or goal and need to see what should be done to reach that goal, you can use goal seek in Excel. You can also make the Goal Seek process automatic in Excel using VBA or Macro.

FAQ