Shares

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

Using 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

## How to open It?

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

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

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

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.

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.

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

• Click OK.

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

• 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?

• Hey Shakhrukh,