EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Goal Seek in Excel
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Goal Seek in Excel

By Madhuri ThakurMadhuri Thakur

Goal Seek in Excel

Goal Seek in Excel (Table of Contents)

  • Introduction to Goal Seek in Excel
  • How to Use Goal Seek in Excel?

Introduction to Goal Seek in Excel

A Goal Seek is a tool that is used to find an unknown value from a set of known values. It comes under the What-If Analysis feature of Microsoft Excel, which is useful to find out the value that will give the desired result as a requirement. This function instantly calculates the output when the value is changed in the cell. You have to mention the result you want the formula to generate and then determine the set of input values that will generate the result.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,325 ratings)

How to Implement Goal Seek?

The Goal Seek is implemented by using the Goal Seek dialog box, as shown in the following figure:

Goal Seek

As shown in the figure, the Goal Seek dialog box accepts three values:

  • Set Cell: It specifies the cell whose value will be changed to the desired value after the result of the Goal Seek operation.
  • To Value: It specifies the value that you want as a result of the Goal Seek tool.
  • By Changing Cell: It specifies the cell whose value needs to be changed to achieve the desired result.

How to Use Goal Seek in Excel?

Goal Seek in Excel is very simple and easy to create. Let understand the working of Goal Seek in Excel by Some Examples.

You can download this Goal Seek Excel Template here – Goal Seek Excel Template

Goal Seek in Excel – Example #1

Let us take a simple example to understand the concept.

Let us take the example of multiplication between two numbers A and B

Goal Seek Example 1

In the above figure, there are two numbers, A and B, with the value of 9 and 6, respectively.

A product is done using the function =PRODUCT(B1,B2), resulting to 54.

If A is 9, what will be the second number for B to get the result, 72.

Following are the steps:

  • Click on Data Tab
  • Under Data tools group
  • Click on the What-if Analysis drop-down menu
  • Click on Goal Seek

Goal Seek Example 1-1

  • In the Goal Seek Dialog Box, select B3 in the ‘ Set Cell.’
  • Enter 72 in the ‘ To Value ‘
  • Select B2 in ‘ By Changing Cell ‘

Goal Seek Example 1-2

  • Then press OK

Goal Seek Example 1-3

The Result is:

Goal Seek Example 1-4

Goal Seek in Excel – Example #2

Example 2

Let us take the example of Aryan Ltd. Trading with generators. The price of each generator is Rs 18000, and the quantity sold is 100 nos.

We can see that the company is suffering a loss of 13.8 lacs. It is identified that the maximum price for which a generator can be sold is Rs. 18000. Now, It is required to identify the no. of generators that can be sold, which will return the break-even value (No Profit No Loss). So the Profit value (Revenue – Fixed Cost + Variable cost) needs to be zero to attain break-even value.

Following are the steps:

  • Click on Data Tab
  • Under Data tools group
  • Click on the What-if Analysis drop-down menu
  • Click on Goal Seek

Example 2-1

  • Select C8 in the ‘ Set Cell ‘
  • Enter 0 in the ‘ To Value ‘
  • Select C3 in ‘ By Changing Cell ‘

Example 2-2

  • Then press OK

Goal Seek Example 2-3

The Result is:

Example 2-4

Example #3

Example 3

In the above figure, a person has taken a loan amount of Rs 18000. He has been told that the loan amount will be sanctioned at an interest rate of 10% per annum for a period of 45 months, which makes the repayment of Rs 481.32 per month. When he calculated the total amount, which he will be repaid in 45 months, he got Rs 21659.47 as a result.

Now he wants to increase the number of repayment periods as he would not be able to pay Rs 481.32 per month. However, he does not want to increase the total amount of repayment by more than Rs 25000.

So, to achieve this, the person needs to go for Goal Seek.

Following are the steps:

  • Click on Data Tab
  • Under Data tools group
  • Click on What if Analysis, drop-down menu
  • Click on Goal Seek

Example 3-1

  • In the Goal Seek Dialog Box, select B6 in the ‘ Set Cell.’
  • Enter 25000 in the ‘ To Value ‘
  • Select B4 in By ‘ Changing Cell ‘

Example 3-2

  • Then press OK

Example 3-3

  • Goal Seek lowers the monthly payment, the number of payments in cell B4 changes from 45 to 82.90. As a result, the Equated Monthly Instalment (EMI) decreased to Rs. 301.56.
  • Then press OK to accept the changes.

Example 3-4

The Result is:

Example 3-5

That’s how easy a Goal seek is. Now let us take another example

Example #4

Example 4

In the above figure, a person wants to invest a lump sum amount in his bank for a certain period of time.

The bank employee suggested he open a Fixed Deposit Account. The rate of interest is 6.5 %, and the number of years is for 5 years. So to find out the return, the person uses the FV function (procedure of calculation is shown in the above figure).

The person now wants to increase the return amount to $ 1500000, but he does not want to increase the time period of investment as well as an initial investment amount. He wants to find the interest rate which will help him to attain the desired return.

Following are the steps:

  • Click on Data Tab
  • Under Data tools group
  • Click on the What-if Analysis drop-down menu
  • Click on Goal Seek

Example 4-1

  • In the Goal Seek Dialog Box, select B6 in the ‘ Set Cell.’
  • Enter 1500000 in the ‘ To Value ‘
  • Select B4 in ‘ By Changing Cell ‘ to change the rate of interest

Example 4-2

  • Then press OK

Goal Seek Example 4-3

  • Goal Seek increases interest rate in cell B4 changes from 6.50% to 20.11%, keeping the number of years and the initial investment unchanged.
  • Then press OK to accept the changes.

Example 4-4

The Result is:

Example 4-5

Pros & Cons of Goal Seek in Excel

  • The Goal Seek will allow the user to find out the accurate data by back calculating the resulting cell by giving a specific value to it.
  • Goal Seek Feature can be used with the Scenario Manager feature.
  • Data must contain a formula to work.

Things to Remember 

  1. Goal Seek is based on the Dependent and Independent cells.
  2. The set cell should always be the resulting cell.
  3. To value should always have a numeric value.
  4. By changing the cell should be the cell that needs to be changed.

Recommended Articles

This has been a guide to a Goal Seek in Excel. Here we discuss its uses and how to use Goal Seek in Excel with excel examples and downloadable excel templates. You may also look at these useful functions in Excel –

  1. VBA Goal Seek
  2. Excel Data Visualization
  3. Data Model in Excel
  4. Excel Database Template
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
7 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Goal Seek Excel Template

EDUCBA

Download Goal Seek Excel Template

EDUCBA

डाउनलोड Goal Seek Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more