Tutorial Playlist
68 Lessons1. Excel Tutorial
2. Excel Worksheet for Practice
3. Google Sheets Vs. Excel
4. Excel Shortcut Keys
5. Excel Keyboard Shortcuts
6. Excel AutoSum Shortcut
7. Redo Shortcut in Excel
8. Charts in Excel
9. Pivot Charts in Excel
10. Excel Sum Formula
11. Excel Percentage Formula
12. Excel Age Calculation Formula
13. Excel Range Formula
14. Round Off Formula in Excel
15. VLOOKUP Formula in Excel
16. Excel Transpose Formula
17. Average Equation in Excel
18. How to Use DATEDIF Formula in Excel
19. IRR Formula in Excel
20. Standard Deviation Formula
21. Excel Age Calculation Formula
22. Excel MAX Function
23. Excel LEFT Function
24. Excel RIGHT Function
25. Trim Function In Excel
26. LookUp function in Excel
27. Columns in Excel
28. How To Sort Data In Excel
29. How To Freeze Panes in Excel
30. Page Setup in Excel
31. How to Recover an Unsaved Excel File
32. Concatenate in Excel
33. Count In Excel
34. IF Condition In Excel
35. H LOOK UP in Excel
36. How Do You Move Columns in Excel
37. Split Cells in Excel
38. Remove Blank Rows in Excel
39. How To Lock Cells in Excel
40. Data Validation in Excel
41. How to Insert Checkbox in Excel
42. How To Highlight Duplicates in Excel
43. Fill Series in Excel
44. How to Create Excel Drop-Down List
45. What-If Analysis in Excel
46. How to Use SUMIFS Function in Excel
47. INDIRECT Function in Excel
48. Pivot Table in Excel
49. Slicers in Excel
50. How to Create a Dashboard in Excel
51. Excel Data Cleaning
52. Data Analysis In Excel
53. Goal Seek in Excel
Now Reading
54. Solver in Excel
55. Power Query in Excel
56. Macros In Excel
57. How To Make Graph in Excel
58. How To Make Histogram In Excel
59. How To Convert PDF to Excel Without Software
60. Barcode Font For Excel
61. Gantt Chart in Excel
62. Excel RANK function
63. AND Function in Excel
64. How to Calculate NPV
65. Format Painter in Excel
66. Count Colored Cells in Excel
67. Amortization Schedule in Excel
68. Master Cell References 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.
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 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:
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.
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:
Here, I want to set the value of B7 to 250, for which I want to change the value of the B6 cell.
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.
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.
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.
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.
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.
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:
The image here shows the default iteration settings.
Goal seeking is an easy tool, but it can sometimes be confusing. Here are some tips for using the function.
Are you preparing for a job interview? Learn some of the Excel questions and answers to ace your interview.
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.
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.
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.
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.
Goal seek function Excel can be accessed by using the shortcut key ‘Alt+A+W+G’ altogether.
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.
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.
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.
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.
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.
Devesh
Passionate about Transforming Data into Actionable Insights through Analytics, with over 3+ years of experience working in Data Analytics, Data V…Read More
Talk to our experts. We’re available 24/7.
Indian Nationals
1800 210 2020
Foreign Nationals
+918045604032
upGrad does not grant credit; credits are granted, accepted or transferred at the sole discretion of the relevant educational institution offering the diploma or degree. We advise you to enquire further regarding the suitability of this program for your academic, professional requirements and job prospects before enrolling. upGrad does not make any representations regarding the recognition or equivalence of the credits or credentials awarded, unless otherwise expressly stated. Success depends on individual qualifications, experience, and efforts in seeking employment.
upGrad does not grant credit; credits are granted, accepted or transferred at the sole discretion of the relevant educational institution offering the diploma or degree. We advise you to enquire further regarding the suitability of this program for your academic, professional requirements and job prospects before enr...