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
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
To open “Goal Seek” to use one these two simple methods.
You can use this method to activate goal seek in Microsoft Excel 2007 To 2016 Versions.
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.
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.
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.
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.
These are few points need to be kept in mind while using goal seek.
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.
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.
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.