Goal Seek in Excel

If you know the result you want from a formula but are unsure what input value is required for the formula to get that result, use the Goal Seek feature. For example, suppose you have a sales target for the upcoming quarter and want to determine the minimum number of units you need to sell each month to reach that target. Using the Goal Seek feature, you can adjust the monthly sales figures until the total sales for the quarter match your target. You understand the sales performance required to meet your goals and make informed decisions about planning strategies to achieve them. Learn more about Goal Seek in Excel in our blog.

What is the Goal Seek Feature?

Goal Seek is a powerful built-in function in Microsoft Excel that helps you determine the input value required to get the desired output result. It works by varying an input value until the desired result is achieved.

Goal Seek helps you find the input value needed to achieve a specific goal. It is beneficial for financial planning, budgeting, and forecasting.

Where Can I Find Goal Seek in Excel?

To locate Goal Seek in Excel, follow these steps - 

• Open Excel and navigate to the “Data” tab.
• Look for the “Forecast” or “What If Analysis” group.
• Click the “What If Analysis” button.
• Select “Goal Seek” from the drop-down menu.

A dialogue box will appear, allowing you to enter the desired target, target cell, and changing cell. Using Goal Seek in Excel lets you solve complex problems and optimise your data analysis effectively.

Example: Step-by-Step Instructions for Using Excel Goal Seek

In a few simple steps, here’s how to use Excel’s Goal Lookup feature:

Dataset: Let's use a simple sales data example. Imagine you have a table with the following columns:

• Price: Selling price per unit
• Quantity Sold: Number of units sold
• Total Revenue: Calculated as Price x Quantity Sold

Goal Seek Scenario: You want to find out how many units of Product A you need to sell to achieve a Total Revenue of $2500.

Step 1: Go to the Data tab in the ribbon. Click What-If Analysis and choose Goal Seek.

Step 2: Set the goal:

• Set cell: Enter the cell reference for Total Revenue (e.g., B3).
• To value: Enter the desired value, which is $2500 (Do not mention the $ sign in the formula)

Step 3: Enter the cell reference for Quantity Sold for Product A (e.g., B1).

Step 4: Click OK. Excel will start iterating and adjusting the value in the Quantity Sold cell until the Total Revenue reaches the goal of $2500.

Tips for using Excel Goal Seek

Here are some other tips to help you get the most out of Excel’s Goal Seek feature :

• Use Goal Seek and other Excel tools like data tables and the scenario manager to create more complex models.
• Be careful when using Goal Seek with formulas that have circular references, as this can cause errors in your spreadsheet.
• Consider using Goal Seek for sensitivity analysis, which involves testing different input values ​​to see how they affect the output value.
• Always check the results to make sure they make sense and are accurate.

Conclusion

Goal Seek in Excel is a powerful feature that can save you time and effort to achieve a specific result in your spreadsheets. You can start using Goal Seek to improve your financial planning, budgeting and forecasting by following the simple steps suggested in this article.