How to use Goal Seek in Excel

HomeAdvanced ExcelHow to use Goal Seek in Excel

In high school, I used to solve mathematical equations where I have the result of the equation but don’t have the input value. So, if you think about solving the same kind of problems in Excel, you have a specific tool that can help you.

Goal Seek

Its basic idea is to find the input value that the formula needs to get that result. In simple words, if you know what actual result value you want, you can use goal seek to find the best input value for getting it.

For example: Let’s say you need to borrow some money from your friend. You know how much money you need, how long you want to take to pay off the loan, and how much you can pay each month.

You can use goal seek to calculate the interest rate you will need to secure with your friend. There is N number of complex problems which you can solve with goal seek.

So today, in this post, you’ll learn how to use goal seek in Excel, how it works, and what important points you need to take care of. Let’s get started.

How to Open Goal Seek

To open a goal seek to use one of these two methods.

  • Go to Data Tab → What If Analysis → Goal Seek.
open goal seek in excel
  • Use the shortcut key Alt + T + G.

You can use these methods to activate goal seek in Microsoft Excel 2007 to 2016 versions.

Components of Goal Seek Function

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

goal seek in excel a small pop up window
  1. Set Cell: Cell in which you want the desired result. Make sure, the cell you are referring to here has a formula in it.
  2. To Value: Value you want as a result.
  3. By Changing Cell: The cell in which you want alteration to come up with the result equals the “To Value”. Make sure, the cell you are referring to here is used in the formula in the cell which is referred to in “Set Cell”.

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.9.

product price structure for using goal seek in excel

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 tweak your base price to 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 below steps.

  • When you open goal seek you will get a window like this.
using goal seek in excel to calculate new final price
  • Now, you need to input the following values.
    1. Click on the “Set Value” and refer it to the cell having a landing price.
    2. Click on the “To Value” and enter the value you want as a result, in the cell having a landing price.
    3. In the end, click on the “By Changing Cell” and refer it to the cell having a base price.
specify values to use goal seek in excel
  • Click OK.
  • Goal seek will do something like this with your spreadsheet.
using goal seek in excel to calculate the lading price

How does it work

Now, if you check the base price cell, its value is changed to $925 and with this, your landing price is changed to $1000. Your base has changed by goal seek using a hit and trial method. But, goal seek is pretty quick in this and you can’t see the entire process.

Important Points about Goal Seek

These are a few points that need to be kept in mind while using goal seek.

  1. A cell that is used as a set cell must contain a formula that is dependent on the cell “By Changing Cell”.
  2. Goal Seek uses the iteration method to reach the target value which you mention in “To Value”. You can adjust its iteration from File-> Options -> Formulas (it can increase its accuracy level).
  3. Sometimes Excel will not able to give you the desired result and pop up a message that “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). Also, make sure to check the above two pointers.
  4. Goal seek is only able to work with a single cell input value at one time.

Use Goal Seek with VBA Code 

You can enjoy your coffee while working with Excel’s goal-seek command. Hope you don’t mind if you can 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: It must be a cell in which you want the desired result.
  2. Goal: The desired result that we want to return as a result.
  3. Changing Cell: Cell in which changes require for achieving result value.

Here is a macro code that you can use.

Sub GoalSeekVBA()
Rem: ////////// Code by ExcelChamps.com
Dim Target As Long
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

sample-file

Conclusion

As I said, if you try to find input value by applying 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 to get better at Excel Skills.

Now tell me one thing. Do you have any complex calculations to solve with goal seek? Please share with me in the comment section, I’d love to hear from you. And please don’t forget to share this tip with your friends.

9 thoughts on “How to use Goal Seek in Excel”

  1. Hi am wanting to run a goal seek VBA for two different cells in the one sheet. Is it possible to write a VBA to do this?

    Reply
  2. Hi brother, I want to run the macro for a range i.e. B5:D10 by changing the cell range B15:D20. Can you please help me to get the VBA to execute properly?
    Thanks in advance.

    Reply
  3. Hi, I want to make a Timezone convertor so that I can get time difference with a single click, There are multiple timezone convertors on Google, however I want to understand the process where I can create it myself, can some one help!!

    Reply
  4. And how can I use goal seek function for multiple worksheets with the same columns in them?

    Reply
      • Can you please show me any example of this VBA? E.g. I have “January”, “February”, “March”, … , “December” worksheets and I want to find, let’s say, effective rate that was applied to the materials purchashed and listed in 2-150 rows. How should VBA look like in this case?

        Reply

Leave a Comment