Learn How To Use Goal Seek Function In Excel

Search
Generic filters

Table of Contents

What is Goal Seek Function in Excel?

Its basic idea is to find the input value which a 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 are the important points you need to take care.

Let’s get started.

How to Open Goal Seek Function in Excel?

To open goal seek use one of these two methods.

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

1 open-goal-seek-in-excel

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.

2 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 here has a formula in it.
  2. To Value: Value you want as a result.
  3. 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 is referred in “Set Cell”.

Solving a Problem in Excel with Goal Seek

Imagine you’re working in a company who is trying to get a tender order through 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.

3 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 in 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  and follow the below steps.

  • When you open goal seek you will get a window like this.
4 using-goal-seek

Now, you need to input following values.

    1. Click on the “Set Value” and refer it to the cell having landing price.
    2. Click on the “To Value” and enter the value you want as a result, in the cell having landing price.
    3. In the end, click on the “By Changing Cell” and refer it to the cell having base price.
    4. Click OK.
    5. Goal seek will do something like this with your spreadsheet.
 
5 specify-values-to-use-goal-seek-in-excel
 

How does it work

Now, if you check base price cell, it values 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 few points need to be kept in mind while using goal seek.

  1. A cell which is used as set cell must contain a formula which is dependent on cell “By Changing Cell”.
  2. 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).
  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 above two pointers.
  4. Goal seek is only able to work with 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 in 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: Desired result which we want to return as a result.
  3. Changing Cell: Cell in which changes require for achieving result value.

Here is a macro code which you can use.

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

Conclusion

As I said, if you try to find input value by applying 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 in a single click. It will do all the complex calculations for you in a second. The best part is, you can use it with VBA as well. And, I hope this will help you get better at Excel.

Free Excel Templates

Sales-Pipeline-Tracker-Excel-Template

Sales Pipeline Tracker

Sales Pipeline Tracker is a ready excel template that helps …

Download Now
Retail_Business_Accounting_Templates-balance

Retail Business Accounting

This is a Double Entry Bookkeeping Accounting System Retail Business Accounting …

Download Now
Energy_Consumption_Calculator

Electricity Consumption Calculator

Electricity Consumption How much electricity do you use each month? …

Download Now
Cash_Flow_Report_Template_-_Direct_Method_V13

Cash Flow Report

Cash Flow Report Statement is a financial report to provide …

Download Now

We Create Your Life Easy With Our Free Excel Templates & Spreadsheet Dashboards

Use free Excel Templates, Tools and Dashboards to create and draft a professional looking dashboards and computation sheet for your personal and business use. You can Explore Calendars, Activity Planners, Invoice Templates, Sales Forecast Sheets, Budget templates and various business information delivering Templates in Excel and Spreadsheet at XLSX Templates. 

These templates, dashboards and tools are useful in various occasions. You can also customize these templates as per your requirement. Modification of fields and data source helps in making the template more relevant.

Learn Microsoft Excel : Blogs and Articles

Learn about various tips and tricks in Microsoft Excel and Spreadsheet. Create best templates and dashboards using free tricks and tutorials in excel and spreadsheet. These tutorial posts are useful for everyone who wants to master the skills in excel and spreadsheet.