Goal Seek in Excel

Goal Seek in Excel

3 mins readComment
Rashmi
Rashmi Karan
Manager - Content
Updated on Feb 23, 2024 15:09 IST

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.

Goal Seek in MS Excel

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.

Goal seek 1

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)
Goal seek 2

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.

Goal Seek 3

Step 5: The new value in the Quantity Sold cell (e.g., B1) will show you how many units you need to sell to achieve your goal. Check if this solution is feasible and adjust your goal or other parameters as needed.

Goal seek 4

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. 

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio