Goal Seek in Excel

Goal seeking is an analysis tool in Excel that helps calculate backward from an end goal. This powerful tool has helped me understand how to allocate resources best. 

Goal seek in Excel helps save resources as well as time. It allows us to test various plausible scenarios and determine an outcome. Using the goal seek function has helped me see the impact that certain changes might have. 

This what-if analysis tool can be used in many places. However, not many people even know that Excel offers this feature. Let me show you how to use this feature in this goal seek Excel tutorial. 

What is Goal Seek in Excel? 

Goal seek is a what-if analysis tool. It helps to find the input value in the formula for which we already know the outcome. For instance, if I want to have a revenue goal of $10,000 for my business, I can use the goal seek function. It will help me determine how many sales I need to make to reach my goal. 

I have used the goal seek in Excel extensively in my career. It has made my financial analysis much simpler. Financial analysts and advisors can use goal seek to share the projections with their clients. 

Goal seek is a function that can be applied in various scenarios. It can be used to determine: 

• The grade a student will need to pass. 
• How long it will take to pay off a loan. 
• The votes needed to win an election. 
• The price of a product to make a profit. 

How to use goal seek in Excel 

The goal seek function can be used in many ways. Let me explain how the function works, and then I will show the different scenarios where it is used: 

Step 1: Create a table 

Start by entering the values in an Excel spreadsheet. Let me show you an example: 

In the given image, I have left cell B6 empty. Using the goal seek tool in Excel, I will find the value for the cell, such that the total equals 250. 

Let me show you how. 

Step 2: Apply the goal seek function 

The total sum of the values is 226. I want to change this total to 250. Let me show you how to do that.  

Go to the ‘Data’ option from the top ribbon. Under ‘Data tools’ you will see the ‘What-if Analysis’ option. Select the ‘Goal Seek’ function.

Step 3: Add specifications 

On selecting the ‘Goal Seek’ option, the following window will show up: 

The three options on the dialogue box are: 

• Set cell: The cell where you want to see the new value. 
• To value: The final value you want in that particular cell. 
• By changing cell: The cell whose value you want to alter. 

Here, I want to set the value of B7 to 250, for which I want to change the value of the B6 cell. 

Step 4: Finish the updated table 

Once you have filled the table to obtain the ideal result, press on ‘OK’ twice. This is what my table finally looks like. 

Are you a beginner at Excel? Learn the Excel basics from industry experts. 

Examples of Goal Seek Functions in Use 

This is a very powerful yet simple tool offered by Excel. I have used a basic example above. Let's see some real-life applications of goal seek in Excel. 

Example 1: 

Let’s say I am a business owner. My goal is to increase my profit which can either be possible by increasing the units sold or the price of the product. 

The given table shows the business data. C3 shows the revenue my business is making at the moment, and C6 shows the revenue I want to earn. 

To reach the target revenue, I can increase the number of units sold. To predict what that will look like, I will use goal seek in Excel. 

Finally, this is what my updated table looks like. 

As you can see in the image above, I need to sell 625 units to make the desired profit. 

However, I can also reach my target by increasing the price of each unit. 

Using goal seek in Excel has saved me from the tedious job of manual calculations. I can use this tool to make projections. 

Note: Use the correct formula to calculate the total revenue earned. If you don’t use a formula and do it manually, the goal seek function will not work. The following error will appear on your screen. 

As you can see, I have used the formula “=C1*C2” to calculate the revenue. 

Example 2: 

Here is another goal seek Excel example. If you are a student, you can use this tool to predict your marks as well. Let’s see how: 

The image shows the marks in different subjects and the overall percentage. To increase the overall percentage, you need to work on the subjects with poor marks. For instance, according to the example I have used above, increasing the marks in Science can boost the overall percentage. 

To increase the overall percentage to 86%, goal seek has found the average marks that you need to score in Science. 

Example 3: 

Here is another example: use the goal seek function Excel to calculate your interest amount. Let me show you how: 

In the example used above, the monthly payable interest is $3,750. Let’s say I want to pay a monthly interest of $3,000. Here’s how to achieve that. 

Let’s try to achieve the desired interest by altering the rate. 

Let’s try to achieve the desired interest by changing the time. 

Importance of Goal Seek in Excel 

Goal seek is widely used by professionals, helping them find out unknown values. This Excel tool has saved me time and effort by getting rid of the need for manual calculations. It has also helped me invest my company’s revenue effectively by making accurate predictions. 

When I quit my high-paying job to start my own business, I used my prior knowledge to make a profit. However, it was a drastic failure, mainly because I was not able to allocate my resources properly. 

Using goal seek in Excel, I started making predictions about my company's expenses and allocating resources accordingly. This helped me immensely to turn around my business. 

Excel Not Working 

Sometimes, goal seek in Excel is not able to find your answer. In such cases, Excel provides the best estimate and shows the message “Goal seeking may not have found a solution.” 

If you are certain that the solution can be found, try the following steps: 

1. Confirm settings

• The ‘Set cell’ should contain the formula. 
• Try checking if that depends on the ‘changing cell’, directly or indirectly. 

2. Adjust iteration 

• Go to ‘Files’ from the top ribbon 
• Click on ‘Options’
• Select ‘Formulas’ 
• Increase the ‘Maximum Iterations’ to find more solutions. 
• Decrease the ‘Maximum Change’ to get more precision. 

The image here shows the default iteration settings. 

3. Remove circular references 

• Goal seek in Excel works best when the formulas are not interdependent. This means that they are not forming circular references. 
  

Tips for Using Goal Seek 

Goal seeking is an easy tool, but it can sometimes be confusing. Here are some tips for using the function. 

1. Multiple variables: This what-if analysis tool can find only one missing value at a time. If you want to find the value of multiple variables at once, try using the ‘Solver’ tool in Excel. 

2. Change to a percentage: If you want to convert a value to a percentage, highlight the cell having the Goal Seek value. Now select the percentage sign from the ‘Home’ tab and change it to a percent. 

3. Finding a particular solution: When goal seek provides an approximate value, you can change that by adjusting the iteration. I have mentioned the process above. 

Are you preparing for a job interview? Learn some of the Excel questions and answers to ace your interview. 

Summing up 

Goal seek in Excel is an excellent tool for finding missing values for a decided output. This tool has helped me immensely when making future projections. The function works backward and helps determine needed values for expected results. 

Want to become a pro at Excel? Check out the certified courses offered by upGrad. These courses are designed to help you upskill and transform your career. 

FAQs

1. How do you use Goal Seek in Excel?

Goal seek in Excel is an analysis tool. You will find this function under the ‘What-if analysis’ option in the ‘Data’ bar. By specifying the cell you want to change and the value of change, you can perform goal seek. 

2. What do you mean by Goal Seek?

Goal seek is a function in Excel used for finding the input when the output is known. It helps in understanding how you can achieve a goal by making changes to the input. 

3. Which menu is Goal Seek in Excel?

Navigate to the ‘Data’ option from the top ribbon. Go to ‘Data Tools’ and click on ‘What-if Analysis.’ Select the ‘Goal seek’ option to perform the function. 

4. What is the shortcut key for Goal Seek?

Goal seek function Excel can be accessed by using the shortcut key ‘Alt+A+W+G’ altogether. 

5. What is Goal Seek in Excel with example PDF?

The goal seek tool can be used in many cases. To predict the price of a product or the expected marks. I have listed several examples above. 

6. Where is Goal Seek in Excel toolbar?

You will find the goal seek option under ‘Data’. Go to ‘Data Tools’ where you will find the ‘What-if Analysis’ option. Click on ‘Goal seek’ to perform the function. 

7. What is more elaborate form of Goal Seek?

A more elaborate form of goal seek in Excel is the Solver tool. Goal seek can only perform formulas with one variable. But, Solver can be used to work with different variables. 

8. Where is Goal Seek in Excel Mobile?

You will find the goal seek option in Excel mobile on the toolbar at the bottom. Navigate to ‘Data’ and then it’s the same. 

9. Why is Goal Seek not working in Excel?

There might be several reasons why the goal seek function might not be working. I have explained the scenarios above and also how you can solve them.