Goal Seek is an in-built function that allows you to get the desired output by changing the assumptions. The process uses the trial and error method to achieve the desired result.

The function is extremely useful in the sensitivity analysis. You will get a clearer picture of the goal seek function once we discuss it with some examples.

In this article, we will briefly discuss ‘What-If-Analysis’ followed by the two example sets to demonstrate the working of goal seek.

What-If-Analysis in Excel

What-if analysis is the way of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. By using What-If Analysis tools in Excel, you can use several different sets of values in one or more formulas to explore all the different results.

Become an AI-powered Business Analyst

Purdue Post Graduate Program in Business AnalysisExplore Now
Become an AI-powered Business Analyst

Goal Seek Example 1

In this example, we aim to use goal seek to find the marks needed to be scored in chemistry so that the overall grade reaches 90.

  • Enter the formula in the cell adjacent to the final grade to calculate the average of all the marks.

goal seek

Go to Data > What - If Analysis > Goal Seek

data goal seek

  • The marks in the Chemistry subject is our input cell. You have to set the value of the final grade to 90 by changing the marks of Chemistry.

marks

  • Click OK. You can see the marks for Chemistry are automatically filled so that the final grade reaches 90.

goal seek 5

Goal Seek Example 2

In this example, our aim is to find what will be the rate of interest if the person wants to pay 

$3000 per month to settle the loan amount.

PMT function is used to calculate the monthly payment amount.

Let’s go step-by-step to see how we can find the rate of interest that will settle a loan of $200,000 by paying $3,000 a month.

  • Enter the PMT formula in the cell adjacent to the Payment cell. Since there is no value currently in the rate of interest cell, Excel assumes it will be 0% which gives us the payment of $1,333.33. Ignore it.

example

  • Follow the steps discussed in the previous example to open the goal seek argument window. We will set the monthly payment to -3,000. Note that it’s a negative value since the amount will be deducted.

    Set rate of interest as the changing cell.

example 2

Your Data Analytics Career is Around The Corner!

Data Analyst Master’s ProgramExplore Program
Your Data Analytics Career is Around The Corner!

  • Click OK. You will see the goal seek function gives the interest rate needed to pay the loan amount in 150 months, paying $3,000 a month.

example 3

Go to Home > Number and change the value to Percentage.

example 4

Your final outcome will look like below:

goal seek

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

Conclusion

In this article, you have learned the goal seek function in MS Excel. They can be used to perform many operations that are otherwise time-consuming.

Boost your analytics career with powerful new Microsoft Excel skills by taking the Business Analytics with Excel course, which includes Power BI training

This Business Analytics certification course teaches you the basic concepts of data analysis and statistics to help data-driven decision making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.

Do you have any questions for us? Feel free to ask them in this article’s comments section, and our experts will promptly answer them for you! 

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Program NameDurationFees
Caltech Post Graduate Program in Data Science

Cohort Starts: 2 Apr, 2024

11 Months$ 4,500
Post Graduate Program in Data Science

Cohort Starts: 15 Apr, 2024

11 Months$ 4,199
Post Graduate Program in Data Analytics

Cohort Starts: 15 Apr, 2024

8 Months$ 3,749
Applied AI & Data Science

Cohort Starts: 16 Apr, 2024

3 Months$ 2,624
Data Analytics Bootcamp

Cohort Starts: 24 Jun, 2024

6 Months$ 8,500
Data Scientist11 Months$ 1,449
Data Analyst11 Months$ 1,449