Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

How To Use Goal Seek in Excel To Find a Possible Input

how use goal seek in excel to get result valuesUsing Goal Seek in Excel can be a game changer for you.

By using goal seek you can find a possible input or value to get the desired 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.

Ahead in this post, you’ll learn how to use goal seek in excel to solve some complex problems.

Table of Content

  1. Open Goal Seek
  2. Components of Goal Seek
  3. Solving a Problem with Goal Seek
  4. Things to Remember
  5. Goal Seek with VBA
  6. Download Sample File

How to open It?

To open “Goal Seek” to use one these two simple methods.

  • Go to Data Tab → What If Analysis → Goal Seek.

open goal seek in excel

  • Use shortcuts key Alt + T + G.

You can use this method to activate goal seek in Microsoft Excel 2007 To 2016 Versions.

Components of Goal Seek

When you open goal seek, you’ll get a small pop-up. And basically, It has three required components.

goal seek in excel a small pop up window

Set Cell – Cell in which you want the desired result.

Make sure, the cell you are referring here has a formula in it.

To Value – Value you want as a result.

By Changing Cell – Cell in which you want alteration to come up with the result equals to the “To Value”.

Make sure, the cell you are referring here is used in the formula in the cell which referred in as “Set Cell”.

Ahead in this post, I’ll show you how you can use goal seek in excel to solve some complex problem with no efforts.

Solving a Problem with Goal Seek

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

Your boss comes to you & ask 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

And if you want to do it manually you have two options.

  1. Perform a back calculation where you have to deduct all the taxes & charges from the landing price ($1000) to come to the base price.
  2. Or, you have to tweak in your base price to get to our desired final price.

But, it’s not as easy as it sounds.

Here goal seek can be a game changer.

Just open the goal seek option using steps I have mentioned above.

  • You’ll get a window like this.

using goal seek in excel to calculate new final price

  • Now, you have to input following values.
    1. Click on the “Set Value” & refer it to the cell having landing price (C7).
    2. Click on the “To Value” & enter the value you want as a result, in the cell having landing price.
    3. In the end, click on the “By Changing Cell” & refer it to the cell having 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

Rolling back again, what actually goal seek had done with your spreadsheet?

How does It work?

Now, If you check base price cell, it’s 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 & trial method.

But, goal seek is pretty quick in this and you can’t see the entire process.

Things to Remember

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

  • A cell which is used as “Set Cell” must contain a formula which is dependent on cell “By Changing Cell”.
  • Goal Seek use 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).

Iteration Adjuestment To Use Goal Seek In Excel

  • Sometimes excel will not able to give you the desired result & pop up a message that “may not have found a solution”. In that situation:
    • You can enter a value closer to your result & try again ($999.95 instead of $1000). Also, make sure to check above two pointers.
  • Goal Seek is only able to work with single cell input value at one time.

Using Goal Seek In VBA

You can enjoy your coffee while working with Excel’s goal seek command. Hope you don’t mind if we add some extra chocolate in it using VBA.

Yes, you are right.

You can easily use goal seek in VBA.

expression .GoalSeek(Goal, ChangingCell)

Expression: It must be a cell in which you want the desired result. As we use “Set Cell”. This should be a single cell.

Goal: Desired result which we want to return as a result. As we use “To Value”. This should be a variant.

Changing Cell: Cell in which changes require for achieving result value. As we use “By Changing Cell”. This should be a single cell.

Here is ready to use macro code I have used.

Sub GoalSeekVBA()

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

If you want to use above code, just change cells or download this sample file to check how you can use goal seek with VBA.

Last Words

First time when I learned about using goal seek in excel, I found it really cool.

And, instantly I showed it to one of my colleagues,

After checking it, he said “Oh! I can do this by using formula. I don’t need this goal seek.” But a few days back he realized that goal seek is super useful.

Over To You

I hope you found it useful.

I am super excited to hear from you.

What do you think about using goal seek in excel? Tell me in the comment box.



  • Shakhrukh Akhmedjanov

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

    • Puneet Gogia

      You can use VBA for that. Use Sheet name along with Cell name.

      • Shakhrukh Akhmedjanov

        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?

        • PG

          Hey Shakhrukh,

          Please share your file if possible.

        • PG

          Hey Shakhrukh,

          Please share your file, if possible.