The Goal Seek function in Excel lets you alter an input value in a calculation to achieve a certain result. This What-If Analysis tool is perfect for scenarios where you know the desired outcome but are unsure of the values required to achieve that conclusion.

What-If Analysis is a powerful function in Excel, but it is also one of its most underutilized. In its most basic form, What-If Analysis allows you to experiment with various scenarios and find a number of possible outcomes. In other words, it enables you to see the effect of a change without having to update the actual data. In this post, we'll look at the Goal Seek function in Excel, which is one of Excel's What-If Analysis tools.

What is the Goal Seek function in MS Excel?

Microsoft Excel has three What-If Analysis tools: Data Tables, Scenarios, and Goal Seek. All three methods are used in "what-if" situations to understand how different input values impact the output value or the intended outcome.

The Goal Seek function is used to determine what input value is required to reach your defined goal in a realistic manner.

One thing to keep in mind is that the Goal Seek function only works if you only wish to specify one input value. If you need to define numerous input values, you may use the Solver add-in instead.

For example, if you want to take out a loan and you know the output value, that is how much you can manage to pay each month, and you also know the input values of (1) the amount of money you need to borrow and (2) the number of months you need to pay back, you can use the Goal Seek function to determine (3) the interest rate required to keep the monthly payment within your budget.

However, if you want to know what interest rate you need and how long it will take to repay the loan in order to retain the monthly payment at the appropriate level, you must use Solver.

The best part of Excel Goal Seek is that it handles all of the work for you; all you have to do is enter the following three parameters:

• The Set Cell, also known as the Formula Cell

• To Value or target/wanted value

• By Changing Cell or the cell to meet the goal

Cases Where Goal Seek Function Can Be Used

There are a range of instances in which you would wish to use Goal Seek. Here are a few scenarios in which the Goal Seek function might be useful.

Personal

The loan example is the most commonly used example of a personal use case of Goal Seek. You know how much you need, how many months you want to pay it back, and how much you can manage to pay back per month.

You may apply Goal Seek to figure out what interest rate you need to acquire to keep it in your monthly budget.

Business

Assume your organization has declared a net profit objective of $2,000,000 USD for the current fiscal year. Q3 has officially ended, so you now have net profit statistics for Q1, Q2, and Q3. Your current year's net profit is $1,518,750.

You may use Goal Seek to calculate what net profit you need to conclude Q4 with in order to meet your $2,000,000 yearly goal.

Education

We've all had that one course in school that, no matter how hard we studied, we simply didn't understand. Perhaps you have a failing grade and want to know what score you need on your final exam to pass the course. Or maybe you have a B and want to improve it to an A.

You may use Goal Seek to discover what final score you need to achieve the desired final score.

How to Use Goal Seek in Excel: Step-By-Step

Goal Seek, for example, will show you how many sales you need to make in a particular amount of time to attain a $100,000 yearly net profit. Or, what grade you need to earn on your last exam to get a total passing mark of 70%. Or, how many votes do you need to win the election?

Stop guessing and use the Excel Goal Seek function if you want a formula to produce a certain result but aren't sure what input value inside the calculation to change to get that outcome.

According to the preceding table, if you sell 1000 articles at $10 each, minus the 10% commission, you will make $9000. The question is, how many items do you need to sell in order to make $20,000?

Let's have a look at how to employ Goal Seek to attain the desired outcome.

1. In your data, create a formula cell and a changing cell that is dependent on the formula cell.

2. Click the What if Analysis button after selecting Goal Seek... from the Data tab > Forecast group.

3. In the Goal Seek dialogue box, specify the cells/values to test and click OK:

• Set cell - a reference to the cell containing the formula (B5).

• To value - the mathematical outcome you seek (20,000).

• By changing cell - the reference for the input cell which has to be modified (B3).

4. If a solution is found, the Goal Seek Status dialogue box will appear and tell you. If it succeeds, the value in the "changing cell" will be replaced with a new one. To keep the changed value, click OK; to go back to the old, click Cancel.

Goal Seek found that 2223 items (rounded up to the nearest integer) must be sold in order to create $20,000 in income.

If you're not sure you'll be able to sell that many items, perhaps raising the product pricing can help you meet your income target? To test this circumstance, do a Goal Seek analysis as described above, but with a different Changing cell (B2):

As a result, you'll notice that by increasing the unit price to $22, you may earn $20,000 by selling only 1000 items:

Excel Goal Seek does not change the formula; rather, it changes the input value you supply to the By Changing Cell box. Furthermore, if Goal Seek is unable to ascertain the answer, it displays the closest value it can find.

Why Learn Excel

Excel is the most essential data analysis tool. When it comes to work, firms often seek the most qualified candidates, and having Excel as a skill on your CV would undoubtedly help you. Excel has a lot of technical features, yet it's still the most user-friendly data analysis program.

Professional Excel instruction is not cheap. Some institutes may demand exorbitant fees if you want to obtain an excel certification and study in person with the teacher. The trainers set the cost of a course. If the trainer is an MVP, you'll have to spend a lot of money on training (Microsoft Valuable Professional). And if you don't get any training, you can't expect to keep up with the current trends.

With Excel, you can learn the fundamentals of data analysis much faster and apply what you've learned to more advanced technology. If you want to work in data science and analytics, you must start using MS Excel.