**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:

- Go to the
**Data**tab from the Ribbon. - Go to the
**Forecast**section. - 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:

**Set****Cell:**the cell that contains your formula.**To****value:**the goal amount.**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:

- Go to the
**Data**tab from the Ribbon. - From the
**Forecast**section, click**What-if Analysis**. - Select
**Goal****Seek**from the menu. - 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)

- 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.

- Go to the
**File**tab from the Ribbon. (In Excel 2019, you need to click on**More**as well) - Click
**Options**. - Click
**Customize Ribbon**from the left menu. **Check the Developer**checkbox under the Main Tabs menu.- Press
**OK**. - Go to the
**Developer**tab from the Ribbon. - In the
**Code**section, press the**Record Macro**button. - In the Record Macro dialogue box, set a name (for example, GoalSeek)
- Press
**OK**. - 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.

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

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

- Return to the VBA project menu.
- Under the Microsoft Excel objects, double-click on the sheet name.
- On the top of the Sheet (Code) window, you have two lists.
- Choose Worksheet under the right list and Change under the left.
- 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*

- 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

**What is the formula for Goal Seek in Excel?**The Goal Seek formula depends on the situation.

**Is there a way to automate Goal Seek in Excel?**Yes. It is possible to automate Goal Seek in Excel using Macro and VBA.

1- Go to the Developer tab from Ribbon.

2- Go to the Code section, and press the Record Macro button.

3- Go through the Goal Seek process.

4- Press Stop Recording from the Code section.

5- Press Alt+F11.

6- Copy the Module line code into the Worksheet Code window.

**What inputs are necessary for Goal Seek in Excel?**Three inputs are required for the Goal Seek dialogue box in Excel.

1- Set Cell: the cell that contains your formula.

2- To Value: the goal value.

3- By Changing Cell: the cell of the variable you’re looking for.

**How many outputs can Goal Seek have?**Each time you use the Goal Seek option in Excel, there is only one output at the end of the calculation.