This Excel tutorial guides you through the step-by-step instructions for using Excel Goal Seek.

In data analysis, you often need to adjust the values of variables of a formula to reach a specific outcome. This is where Goal Seek steps in.

It enables you to work backward through the formula, setting a target result, and allowing Excel to determine the input needed to reach that goal.

Whether you’re a seasoned analyst or a spreadsheet novice, learning how to use Goal Seek can significantly enhance your decision-making process.

What Is Goal Seek in Excel?

Excel’s Goal Seek is a dynamic tool that empowers you to solve complex equations effortlessly. It’s particularly useful for backward calculations, enabling you to set the result of a mathematical equation.

Then Excel determines the necessary input to achieve the result. Since the result always contains an Excel formula, Goal Seek automatically takes into account all the variables involved in the mathematical exercise.

Goal Seek performs automated iterative calculations of the equation by putting different input values for the selected variables. Since Excel uses an algorithm behind the Goal Seek tool, the process is usually faster than the trial and error method of solving an equation.

Whether you’re adjusting loan payments, determining optimal product pricing, or fine-tuning financial models, Goal Seek streamlines the process by iteratively refining inputs until the desired result is achieved. Goal Seek automates what-if scenarios and thus enhances your decision-making capabilities by analyzing raw data.

Exploring the Goal Seek Excel Tool

You can find the Goal Seek tool in the Data tab > Forecast > What-If Analysis menu. The shortcut or keyboard inputs to launch the Goal Seek Excel add-in are Alt > A > W > G.

Set Cell

This refers to the cell that contains the formula or calculation whose result you want to set to a specific value. It’s the output cell that you’re trying to adjust to achieve a desired outcome.

For example, if you’re calculating the monthly payment of a loan and you want to find the interest rate needed to achieve a particular payment, the Set Cell would be the cell containing the payment calculation.

The Set cell field should always be referred to a cell in the worksheet that contains a formula. However, there shouldn’t be any Circular References in the Set cell field or the Goal Seek tool will generate errors.

Also read: 6 Ways to Fix Microsoft Excel Cannot Calculate a Formula Error

To Value

This is the target value you want the Set Cell to reach after using Goal Seek. You provide the desired value that you’re aiming to achieve in the output cell.

Using the loan example again, this could be the specific payment amount you want to attain.

By Changing Cell

This refers to the input cell, also known as the variable cell, that you’re allowed to adjust in order to reach the target value in the Set Cell.

In the loan scenario, this would be the cell containing the interest rate, which you can modify to find the rate required to match the desired payment.

Reasons to Use Excel Goal Seek

• Excel Goal Seek efficiently determines input values needed for specific formula results, simplifying complex backward calculations.
• It streamlines scenario analysis, aiding optimal decision-making through automated iterations that save time over manual methods.
• By modifying one variable, Goal Seek enables achieving precise targets and enhancing financial modeling, data analysis, and problem-solving processes.
• The tool’s ability to solve equations iteratively for desired outcomes provides a practical approach to finding solutions that manual methods may struggle with.
• Whether refining loan payments, fine-tuning pricing strategies, or more, Goal Seek empowers users to make informed choices backed by accurate calculations.

Now that you’ve acquired a basic understanding of the Excel Goal Seek tool, find below several real-world scenarios where you can apply this Excel data analysis skill.

Calculating Mortgages Using Excel Goal Seek

Finding the years of repayment, interest rates, or monthly installments of a mortgage is one of the most common usages of this powerful iterative calculation tool in Excel.

Suppose, you’re applying for a loan to buy new equipment for your business. Here are the particulars of the mortgage:

• Annual interest rate: 4%
• Years of repayment: 20
• Loan amount: X
• Monthly installments: -$1,500

Here’s how Excel Goal Seek can help you determine the loan amount you can afford against a monthly payment of $1,500.

1. Add the row labels Annual Interest, Years of repayment, Loan amount, and Monthly payment from rows A1 to A4.
2. Under column B, populate the values, like B1=4%, B2=20, and B3 should be empty. Goal Seek will calculate B3 for you.

3. In the cell B4, enter the following PMT or loan repayment formula:

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

4. Hit Enter and Excel should show $0.00 in B4.

Now, you must call the Goal Seek tool to calculate the loan amount. Here’s how it’s done:

1. Click the Data tab and go to the Forecast commands block.
2. Click the What-If Analysis drop-down menu.
3. In the context menu that shows up, click Goal Seek.

4. The Goal Seek Excel add-in user interface will pop up.
5. There, click the Set cell box and select the B4 cell on the worksheet.
6. Inside the To value field, enter the amount you want to pay per month, which is -1500.
7. Click inside the By changing cell field and highlight the B3 cell on the spreadsheet.
8. Click OK to calculate the borrowing amount you should consider.

The above image, the Goal Seek Status dialog, shows the calculated loan amount that you can choose provided the above mortgage constraints.

If you’re okay with the results, click the OK button on the Goal Seek in the Excel dialog box.

You can customize the Goal Seek by changing the variables. For example, you can calculate the annual interest you must pay if you’d like to take a loan of $250,000 for a 20-year repayment term.

Alternatively, you can fix the loan amount and annual interest and calculate the years of repayment by using Goal Seek.

Finding Out Required Scores Using Goal Seek

Suppose you must score an average of 75 to pass a certain selection test. You already appeared for three papers and here are the scores you obtained:

• Biology 70
• Physics 67
• Maths 68

You’re yet to appear for the last paper, which is in the English language. Find below how to use Goal Seek in Excel to solve for the score you must get to pass the selection process:

1. Input the available data in an Excel worksheet as shown above.

2. Create a new row for Average and input the following formula in the cell B7:

=AVERAGE(B2:B5)

3. Hit Enter to get the preliminary average value.

This dataset is now ready for further analysis in the Goal Seek Excel add-in. Here’s how:

1. Call the Goal Seek tool.
2. The Set cell field should be B7.
3. Enter the goal, which is 75, into the To value field.
4. The By changing cell should be B5.
5. Click OK to learn the score you must aim for the last paper.

Use Goal Seek in Excel to Find Break-Even Points

Suppose you’re a business startup and need to calculate the break-even point so you can boost sales of the products you produce.

You can use the Excel Goal Seek tool in this case to quickly figure out the revenue you must bring in to at least pay for the business expenses if not making any profits.

You’ve collected the sales and expenditure data for the last month and you’ve created the above dataset in an Excel worksheet. It shows that the monthly sales value isn’t equal to the business expense. You’re losing $4,750 monthly.

Now, you must increase the monthly product sales to fill the gap. Find below how you can use the Goal Seek Excel add-in in this scenario to solve the objective, which is $26,000 in revenue per month.

1. Press the Alt > A > W > G keys to bring up the Goal Seek tool.
2. Choose B5 as the Set cell value.
3. The To value field should be 0 as you just want to balance the revenue and expenses.
4. The By changing cell should be B2, as you can only increase product quantity.

5. Click OK and Excel should calculate the new sales quantity to break even, which is 305.88.

Suppose, you don’t just want to break even, but want to earn a profit. In that case, change the To value field to the profit amount you want to earn. Goal Seek will calculate the sales volume needed for profit automatically.

The above image shows that Goal Seek forecasted that if you can raise the sales volume to 317.64 units per month, you can make a profit of $1,000 from your business.

Use Excel Goal Seek in Multiple Cells Using VBA

So far, you’ve seen that you can only apply Goal Seek to one problem at a time. Excel doesn’t have any user interface buttons to allow for multiple Goal Seek calculations.

However, you can use Excel VBA scripting to run as many Goal Seek analyses as you want in one worksheet concurrently. This method also enables you to automate calculations that involve Excel Goal Seek analysis.

Find below the VBA script and steps to implement the code in your worksheet:

1. Press Alt + F11 to call the Excel VBA Editor tool.
2. On the VBA toolbar, click the Insert button.
3. Choose Module on the context menu that pops up.
4. Copy and paste the following script into the blank module:

Sub goalseekvba()
For row_no = 2 To 6
Worksheets("Sheet4").Cells(row_no, "D").GoalSeek Goal:=0.35, _
ChangingCell:=Worksheets("Sheet4").Cells(row_no, "C")
Next row_no
End Sub

5. Click the Save button.
6. Click the Close button to close the tool.

Now, you must run the VBA macro by following these steps:

1. Press Alt + F8 to launch the Macro dialog box.
2. Choose the goalseekvba macro.
3. Hit the Run button to execute the code.

Excel will automatically calculate the sales volume needed to attain a profit percentage of 35% for all the products in the list.

Here’s how you can customize this Goal Seek VBA script:

• Set Sheet4 to the actual worksheet name on your Excel workbook where you’re working.
• In the For row_no field, enter the rows you want to include in the calculation. In the current exercise, it’s 2 to 6. You can make it 2 to 100, and so on.
• The code element Cells(row_no, "D") states the column where Goal Seek will apply its iterative calculations. Change it accordingly.
• In the current script, GoalSeek Goal:=0.35, since my target is to achieve a 35% profit for all the rows. You can change the goal value according to your datasets.
• In the code element (row_no, "C"), value C tells Excel which variables to be changed. So, change the column identification according to the variables of your worksheet.

How to Troubleshoot Goal Seek Excel Add-In

If you set the Goal Seek input fields appropriately, you shouldn’t experience any errors. However, if you’ve presented a complex model to Goal Seek, it can produce the following two errors:

• Goal Seek performed maximum iterations so far and no solution is available.
• You’ve given a target value of 100 but Goal Seek generated a solution for a target value of 101.

To fix the above issues, you must change certain settings of Excel Goal Seek. Here’s how.

1. Click the File tab and choose Options from the left-side navigation pane.
2. Click the Formulas option on the left side panel.
3. Under the Calculations options, checkmark the Enable iterative calculation checkbox.
4. Change the Maximum Iterations value to 1,000 or more.
5. Also, reduce the Maximum Change value to 1 to increase the degree of accuracy.
6. Click OK to apply the changes.

More iterations will help to obtain a more accurate solution for your goal seek.

Conclusions

That’s how you use Excel Goal Seek to get values for unknown variables in a mathematical equation when you know the result or goal.

Try it using your own dataset and comment below about your experience. If you know any secret tricks and tips for Goal Seek Excel add-in, do mention those in your comment.