How to Use Goal Seek in Excel (With Real-Life Examples)

If you know the outcome of a formula but don’t know the input value that’ll generate the outcome, Microsoft Excel got you covered. Simply learn how to use Goal Seek in Excel and perform simulations of formulas and large numerical data without changing the actual data.

Whether it’s your personal finances, savings investments, or business project, informed decision-making is the key to success. For informed decision making you need simulated data from the simulation of databases based on a formula and your objective. Excel Goal Seek is the tool for such data-based simulation.

If you’re new to Excel or have some experience using it but are not an expert, read on to learn the basics of Goal Seek on Excel and how to use Goal Seek function in Excel.

What Is Goal Seek in Excel?

Goal Seek is the built-in What-If Analysis tool of Excel. It shows how the final value in a formula impacts the input value. In easy words, it tells you the input value for a formula to get a desired outcome from the same formula.

Also read: Enable/Disable Display of Formulas in Cells in Excel

Financial investors, finance managers, business owners, and management majors abundantly use this Excel What-If Analysis tool to predict the input for a desired outcome from any mathematical model.

You can also call the Goal Seek on Excel an automated simulation system that analyzes your formula linked to any size of datasets and tells you an input value. If you enter the input value in your mathematical model, you’ll get the result you’re looking for.

You can use this tool in almost any scenario but the followings are the major ones:

• How many units of products you must sell to achieve a specific revenue with added discounts and agent commissions
• At what interest rate you must borrow so you can pay off the loan within a specific period at specific monthly installments
• How many work hours needed to complete a project within a predetermined deadline

Where Is Goal Seek in Excel?

Excel Goal Seek is one of the three What-If Analysis tools of Microsoft Excel. So, you can find the tool in the What-If Analysis command menu. On the Excel ribbon menu click the Data tab and then go to the far right on the ribbon. There, you should see the Forecast section. Select the What-If Analysis button and Goal Seek is the number two in the drop down list.

The location of the tool is the same in Excel for the Mac app. At the time of writing, the feature is available in the following Excel editions:

• Excel for Microsoft 365 (Windows and Mac)
• Excel 2021 (Windows and Mac)
• Also available for Windows and Mac from Excel 2010 and later

Requirements to Use Goal Seek in Excel

Excel Goal Seek will perform all the calculations in the backend. All you need to do is define the following three parameters from your mathematical model or data table:

• Enter a cell reference in the Set cell box. This reference cell must contain a formula. Excel Goal Seek uses this formula to derive the input value.
• You need to enter the result or goal you need to achieve in the To value box.
• The By changing cell is the cell reference in which you need an input for the mathematical model you’re simulating.

How to Use Goal Seek in Excel

Find below some example scenarios where you can use Goal Seek in Excel to predict an input for a desired output from a mathematical formula. The steps are similar for both Windows 11 and macOS devices.

1. Goal Seek to Predict Exam Score

Let’s say, you need to pace up your study plans in the final semester of college to achieve an 85% average score when you graduate. Here’s how you can apply the Goal Seek formula to simulate the score needed in the final semester if you got scores from the other seven semesters:

• Create column headers named Semester and Score on an Excel worksheet.
• Type Semester I through Semester VIII under the Semester column.
• Enter the scores of all seven-semester exams except for Semester VIII.
• Create a Semester Average score by using the following formula:

=AVERAGE(B2:B9)

• Adjust cell references as per your own Excel worksheet.
• Now, call the Goal Seek function.
• The Set cell should be the formula cell where you calculated an average.
• The To value cell should be your desired average semester score which is 85%.
• Enter the blank cell reference for the Semester VIII exam score in the By changing cell box.

• Click OK to get the Semester VIII score you must secure for an average graduation score of 85%.

You need to score 98% in the final semester to get to the target graduation score.

Also read: Microsoft Excel: How to Easily Manage the Sheets

2. Predict Loan Amount Using Goal Seek

Suppose you need to borrow capital for 40 years on a fixed interest (8%) with a fixed monthly payoff for your business. Now, you know that you can pay $1,800 per month from the returns of the business. However, you don’t know how much you must borrow at the above interest for the above period. You can effortlessly calculate the amount to be borrowed by following these steps:

• Create rows for the following components: Annual Interest Rate, Years to Payoff, Loan Amount, and Monthly Installments.
• Enter the respective values for the above components in the respective cells except for the Loan Amount, which you need to calculate using Goal Seek.
• Now, replace the Monthly Installments value with the following PMT formula in Excel:

=PMT(B1/12,B2*12,B3,0)

• Call the Goal Seek function from Data > What-If Analysis > Goal Seek.
• Set cell should refer to the PMT formula cell.
• To value should be $1,500.
• By changing cell should be the Loan Amount value.

• Click OK to simulate the loan amount through iterative calculations.

According to the Goal Seek function, the loan amount must be $215,731 if you wish to borrow it for 40 years at 8% annual interest and pay a monthly installment of $1,500.

3. Find Sales Target Using Goal Seek

If you’re the head of sales and marketing or working as a sales executive and need to create a sales target to achieve a specific revenue goal, Goal Seek is the ideal tool. For example, your business is currently selling 1,000 units of products.

The MSRP of the product is $50 and you’re also offering a discount of 10% to the wholesalers. However, you’d like to increase your company’s revenue target from product sales to $75,000 without changing the MSRP and discount rate. Then, how many products you must sell? Let’s find out using Goal Seek below:

• Create rows for known components like Units Sold, MSRP, and Discount.
• Calculate the revenue using the following formula:

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

• Now, open Goal Seek and set the following as its parameters:
  • Set cell: $B$5
  • To value: $75,000
  • By changing cell: $B$2

Ensure you adjust the cell references according to your own data and Excel worksheet. Press OK and you get the sales target of products which is 1,667 units at the same MSRP and discount rate.

Goal Seek on Excel: FAQs

How Do I Use Goal Seek in Excel for Multiple Cells?

Goal Seek in Excel enables you to simulate an input for a mathematical formula to get a predetermined result. Unfortunately, it doesn’t support any variables or multiple cells to be factored in. For multiple cells and variation-based What-If Analysis, you can use the Solver add-in of Excel.

What Is the Shortcut for Goal Seek?

Currently, there is no dedicated shortcut for the Goal Seek function in Excel. You can use the Alt key on Excel and then press some keys on the keyboard to call the Goal Seek tool without using a mouse. The steps are as outlined here:

Press Alt on Excel > without clicking anywhere on the app press A > press W > press G to open Goal Seek

How Do I Make My Goal Seek More Accurate?

Sometimes, Goal Seek may derive the input value in decimals. If you need a rounded figure automatically, you can increase its accuracy. Here’s how you can do it:

• Click the File tab and choose Options on the sidebar.
• Inside the Excel Options window, click the Formulas option.
• Checkmark the Enable iterative calculation feature.

• Increase the Maximum Iterations field to 150 or more to let Excel test more feasible solutions.
• Also, increase the Maximum Change value from default 0.001 to 0.00001.

Can I Do Goal Seek on Multiple Cells?

You can’t perform a Goal Seek on Excel for multiple cells.

What Are the Advantages of Goal Seek?

Here are the benefits of Goal Seek on Excel:

• Goal Seek is the simplest simulation available for What-If Analysis in Excel
• It comes out of the box with the Excel desktop app
• Goal Seek works in the backend and doesn’t require you to modify the whole database
• Useful for financial modeling

What Are the Limitations of Goal Seek in Excel?

Goal Seek on Excel has the following drawbacks:

• Goal Seek won’t work if there are circular references
• You can’t input any variables when performing Goal Seek simulations
• It doesn’t work on multiple cells
• Excel Goal Seek isn’t available on Excel Web App or Excel for the Web app

Excel Goal Seek: Final Words

By reading the above article on Excel Goal Seek, you should have developed a basic to medium-level understanding of how to do What-If Analysis in Excel. Goal Seek on Excel is particularly useful in mathematical tasks where there’s a clear formula, an input value, a few variables linked to the formula, and one objective to be achieved.

Leave your comment below on your experience with the above step-by-step guide of Excel Goal Seek. If you know any other Excel What-If Analysis techniques, do let me know.

Next up, how to compare text in Excel.