How to use Goal Seek in Excel Sheet

Generic filters

In school days, we used to solve mathematical equations.…where we have the result of the equation…

..but don’t have the input value.

So, if you think about to solve the same kind of problems in Excel, you have a specific tool which can help you.

Goal Seek in Excel

The  basic idea is to find the exact input value and a formula can help you to to get that value.

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:

For say, you need to borrow some money from someone.

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.

You can solve many complex problems with the help of goal seek function.

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 in Excel Sheet?

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.

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

How to use Goal Seek Function in Excel?

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

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

How to Solve a Problem in Excel with Goal Seek Function?

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.

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.

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

In the end, click on the “By Changing Cell” and refer it to the cell having base price.

•
• Click OK.

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 before you work on Goal Seek

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.

Learn how to Select Non-Contiguous Cells in Excel

Table of Contents Select cell without using Ctrl Key By following these simple steps we can do it. At first, …

How to apply Strikethrough in Excel [Shortcut + Command]

Table of Contents Shortcut Key to Apply Strikethrough to a Cell Let’s say you’re in hurry and don’t want to …

Formula Bar in Excel

Table of Contents Key Points To Consider Using Formula Bar You can hide or unhide the formula bar. You can …

Convert Date into Day

Convert Date into Day Sub date2day() Dim tempCell As Range Selection.Value = Selection.Value For Each tempCell In Selection If IsDate(tempCell) …

Open Calculator

Sub OpenCalculator() Application.ActivateMicrosoftApp Index:=0 End Sub In Windows, there is a specific calculator and by using this macro code you …

Unmerge Cells

Sub UnmergeCells() Selection.UnMerge End Sub Unmerge Cells code simply uses the unmerge options which you have on the HOME‌ tab. …

Remove Text Wrap

Sub RemoveTextWrap() Range(“A1”).WrapText = False End Sub This code will help you to remove text wrap from the entire worksheet …

Auto Fit Rows

Sub AutoFitRows() Cells.Select Cells.EntireRow.AutoFit End Sub You can use this code to auto-fit all the rows in a worksheet. When …

Auto Fit Columns

Sub AutoFitColumns() Cells.Select Cells.EntireColumn.AutoFit End Sub Use above Excel macro code to auto fit rows in all your sheet at …

Insert Multiple Columns

Sub InsertMultipleColumns() Dim i As Integer Dim j As Integer ActiveCell.EntireColumn.Select On Error GoTo Last i = InputBox(“Enter number of …

Insert Multiple Rows

Sub InsertMultipleRows() Dim i As Integer Dim j As Integer ActiveCell.EntireRow.Select On Error GoTo Last i = InputBox(“Enter number of …