How to use Goal Seek in Excel

How to Open Goal Seek

To open Goal Seek, use one of these two methods.

• Go to the Data tab → What-If Analysis (in the Forecast group) → Goal Seek.

• Use the shortcut key Alt + T + G, or the ribbon keys Alt → A → W → G (press them one after the other).

These methods work in Excel 2007 and every desktop version after it, including Excel 2016, 2019, 2021, 2024 and Microsoft 365. On a Mac, use the same menu path (Data → What-If Analysis → Goal Seek); the keyboard shortcuts above are for Windows. Goal Seek isn’t available in Excel for the web, so open the file in the Excel desktop app when you need it.

Components of the Goal Seek Dialog Box

When you open Goal Seek, you’ll get a small pop-up to input data. Basically, it has three required components.

1. Set cell: The cell in which you want the desired result. Make sure the cell you refer to here has a formula in it.
2. To value: The value you want as the result. You need to type the number here; this box doesn’t take a cell reference.
3. By changing cell: The cell that Goal Seek changes to make the result equal to the “To value”. Make sure the formula in the “Set cell” uses this cell (directly or through other formulas), and that this cell holds a value, not a formula.

Solving a Problem in Excel with Goal Seek

Imagine you’re working in a company that is trying to get a tender order by quoting the lowest price.

For this, you need to prepare a price structure in which your landing price (to the customer) should be $1000, whereas your present final price is $1089.90.

If you want to do it manually, you have two options.

1. Perform a back calculation where you have to deduct all the taxes and charges from the landing price ($1000) to come to the base price.
2. Or, you have to keep tweaking your base price until you get to the desired final price.

It’s not as easy as it sounds, but Goal Seek can be a game changer here.

Download this file from here and follow the steps below.

• When you open Goal Seek, you will get a window like this.

• Now, you need to input the following values.
  1. Click in the “Set cell” box and refer it to the cell with the landing price.
  2. Click in the “To value” box and enter the value you want as the result in the landing price cell.
  3. In the end, click in the “By changing cell” box and refer it to the cell with the base price.

• Click OK.
• Goal Seek will do something like this with your spreadsheet.

After that, a Goal Seek Status box tells you whether Goal Seek found a solution and shows the target value and the current value. Click OK to keep the new values, or click Cancel to put the original values back.

How Does It Work?

Now, if you check the base price cell, its value has changed to $925, and with this, your landing price has changed to $1000. Goal Seek changed your base price using a hit and trial method: it tries a value, checks the result, and adjusts the next try until the result is close enough to the target. Goal Seek is pretty quick at this, so you can’t see the entire process.

Find the Interest Rate for a Loan with Goal Seek

Now, let’s go back to the loan example from the start of this post. Let’s say you want to borrow $100,000, you want to pay it back in 180 months (15 years), and you can pay $900 each month. The question is, what is the highest interest rate you can accept?

1. First of all, enter the loan amount (100000) in cell B1 and the term in months (180) in cell B2.
2. After that, leave cell B3 empty for the interest rate. Goal Seek will fill it in.
3. Now, in cell B4, enter the PMT formula (below this list) to calculate the monthly payment. With B3 empty, Excel takes the rate as 0%, so the formula returns -555.56.
4. Open Goal Seek and enter B4 in “Set cell”, -900 in “To value” and B3 in “By changing cell”. The target is negative because PMT returns a payment as a negative number.
5. In the end, click OK. B3 now shows about 0.0702. Format it as a percentage (Home → Number) to see it as 7.02%.

=PMT(B3/12,B2,B1)

So, you can borrow the money at an interest rate of up to about 7.02% per year and still keep your payment at $900 a month.

For this particular problem, there’s also a formula that gives you the rate directly. The RATE function returns the monthly rate, so you need to multiply it by 12:

=RATE(B2,-900,B1)*12

But most of the time, there’s no ready-made function that works your calculation backward, like the price structure above. That’s where Goal Seek saves you.

Important Points about Goal Seek

These are a few points that you need to keep in mind while using Goal Seek.

1. The cell that you use as the “Set cell” must contain a formula that depends on the “By changing cell”.
2. Goal Seek uses iteration to reach the target value that you enter in “To value”. By default, it stops after 100 iterations or when the result is within 0.001 of the target. You can change both from File → Options → Formulas (Maximum Iterations and Maximum Change); a smaller Maximum Change gives you a more accurate result.
3. Sometimes Excel won’t be able to give you the desired result and shows a message that it “may not have found a solution”. In that situation, you can enter a value closer to your result and try again ($999.95 instead of $1000). It also helps to type a starting value in the “By changing cell” that’s close to the answer you expect. Also, make sure to check the two points above.
4. Goal Seek can only work with a single input cell at one time. If you need to change more than one cell, or you want to add limits (like a minimum margin), use Solver instead (more on this below).
5. Goal Seek doesn’t update by itself. If you change any value later, the result stays as it is, and you need to run Goal Seek again.
6. The value Goal Seek finds can be a long decimal that is very close to the exact answer but not exact. Round it or format the cell if you need a clean number.

Goal Seek vs Solver vs Data Tables

Goal Seek sits in the What-If Analysis menu with two other tools, and Solver is close to it as well. Here’s how to pick the right one:

• Goal Seek: You know the result you want and need one input value to get it, like the base price or the interest rate on this page.
• Solver: You need to change more than one cell, add limits (like a maximum discount), or find the highest or lowest possible result. Solver is a free add-in that comes with Excel. To turn it on, go to File → Options → Add-ins, select Excel Add-ins in the Manage box, click Go, tick Solver Add-in and click OK. After that, you’ll find Solver on the Data tab.
• Data Table: You want to see the results for many input values at once (for one or two inputs), instead of a single answer. Unlike Goal Seek, a data table updates when your values change.
• Scenario Manager: You want to save sets of input values (like best case and worst case) and switch between them.

Use Goal Seek with VBA Code

You can enjoy your coffee while working with Excel’s Goal Seek command. I hope you don’t mind if I add some extra chocolate to it using VBA. Yes, you are right, you can use Goal Seek with VBA.

expression.GoalSeek(Goal, ChangingCell)

1. Expression: The cell (a Range) that has the formula and in which you want the desired result.
2. Goal: The value you want this cell to return (required).
3. ChangingCell: The cell that should change to reach the goal (required).

GoalSeek returns True when it finds a solution, so you can check the result in your code.

Here is the macro code that you can use.

Sub GoalSeekVBA()
Rem: ////////// Code by ExcelChamps.com
Dim Target As Double
On Error GoTo Errorhandler
Target = InputBox("Enter the required value", "Enter Value")
Worksheets("Goal_Seek").Activate
With ActiveSheet.Range("C7")
.GoalSeek _
Goal:=Target, _
ChangingCell:=Range("C2")
End With
Exit Sub
Errorhandler: MsgBox ("Sorry, value is not valid.")
End Sub

When you run it, it asks you for the target value, activates the Goal_Seek sheet, and changes C2 until the formula in C7 returns the value you entered. If you enter text instead of a number, or click Cancel, it shows an error message instead.

Goal Seek works on one cell at a time, but with a loop, you can run it for many rows in one go. This also gets around the “To value” box, as the code can take each target from a cell. Let’s say your formulas are in D2:D10, the target values are in E2:E10, and the input cells to change are in B2:B10. Run this code with that sheet active:

Sub GoalSeekAllRows()
    Dim r As Long
    For r = 2 To 10
        Range("D" & r).GoalSeek Goal:=Range("E" & r).Value, ChangingCell:=Range("B" & r)
    Next r
End Sub

sample-file

Frequently Asked Questions

Conclusion

As I said, if you try to find the input value with a manual method, you need to spend a lot of time or create a formula to perform a back calculation.

But by using Goal Seek, you can do this with a single click. It will do all the complex calculations for you in a second. The best part is that you can use it with VBA as well. And, I hope this will help you get better at Excel.

Quick Note: It’s one of those Excel Tips that can help you get better at Excel Skills.