Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
Goal Seek in Excel (Table of Contents)
What is Goal Seek Analysis in Excel?
Goal Seek is the tool of What-If-Analysis which performs the task of identifying the variable number required to achieve the targeted number. For example, if the cost price is 15 and you are expecting the revenue of 50,000, then using excel Goal Seek we can identify how many units we need to sell to achieve the target of 50,000.
So, the goal seeks to show how one data can impact another set of data. So goal seek determines what value needed to achieve the desired result.
Examples of Goal Seek in Excel
Let us discuss some practical examples of goal seek analysis in excel.
In an election, two candidates contested, in a survey from one of the TV, channel results are as follows.
Now arrive % Votes by dividing the individual vote by total votes.
Now Candidate A knows he is close to winning the election but short by 3.67%. Now he wants to analyze how many voters he needs to attract to win the election with the vote percentage of 50.01%.
Now we will apply excel GOAL SEEK Analysis to help out Candidate A here. Follow the below steps to apply the excel goal seek to this problem of Candidate A.
Step 1: Go to DATA tab What-If-Analysis and Goal Seek.
Step 2: Now we can see the below Goal Seek window.
In this window, we can see three boxes.
Set cell, To Value, and By Changing Cell.
Set Cell: This is the targeted cell. Which cell did we need to change the value?
To Value: The Set Cell value should be what??
By Changing Cell: By changing which cell we need to impact the Set Cell.
Step 3: In this example, we need to set the cell as C2.
Step 4: Next is To Value, this is the Set Cell value should be what. In this case, it should change to 50.01%.
Step 5: Next by changing which cell we need to impact the cell C2. In this example, we need to know how many votes Candidate A needs to secure to get the vote % of 50.01%. So By Changing Cell is B2 cell.
Step 6: Now all the parameters are set, click on OK to get the result.
As we can in the above image Candidate A should secure 70014 votes to get the vote % share of 50.01%.
Click on Ok to apply the changes.
Mr. A opened a retail shop recently. In order to meet his monthly break-even, he wants to generate a revenue of 2.5 Lakh per month. On an average selling price, he will sell goods at 25 per unit. Now he doesn’t know how many quantities he needs to sell to generate a revenue of 2.5 Lakh.
In the cell, B3 apply the formula as Unit Price * Units.
Now open Goal Seek window.
The set cell is the Revenue cell, so set the cell as B3.
Next To Value should be 2.5 Lakh.
By Changing Cell is Units cell, so select the cell B2.
Click on Ok to get the required units to get the revenue of 2.5 Lakh at 25 per unit price.
So, Mr. A required to sell 10000 units in the month to generate a revenue of 2.5 Lakhs.
A student appeared in 5 subject examinations and left with 1 more subject. As an estimation, he has secured below marks in each subject and his average grade is 88.
Since he has to write one more exam he is targeting the overall average of 90 from all the 6 subjects. Now that the student wants to know how much he needs to score in the remaining 1 paper to secure an overall average of 90.
Open Goal Seek first.
Set Cell will be Average Cell, so select B7 cell.
Next Set cell value should be 90, so enter the value as 90 for To Value box.
The final box is by changing which cell we need to see the impact. In this example, we need to change the cell B6 i.e. last subject paper.
Click on Ok to see the impact.
So, in order to get an overall average of 90 students has to score 98 in the final subject.
Like this, we can use Goal Seek analysis in excel to identify what needs to be done to get the desired or targeted result.
Things to Remember
- The set cell must contain a formula.
- Changing cells should be part of the formula to see the impact.
- Changing the cell should be empty and should not contain any special characters.
This has been a guide to Goal Seek in Excel. Here we learn how to do Goal Seek Analysis in excel along with examples and downloadable excel template. You may learn more about excel from the following articles –