Last Updated on
If you want to know how to use Goal Seek in Excel, you’ve landed on the right page.
Goal Seek is a handy function to use in your Microsoft Excel spreadsheets to find out the desired input value for a formula when you already know the output value. This can prove to be extremely useful, especially when dealing with a large data set. However, not a lot of people know that this feature exists, and those who do struggle to use it.
To help you out, we’re going to explain how to use Goal Seek in Excel, along with what to do in case you face any errors related to it.
How you can use Goal Seek in Excel
Scenario on hand: We have a dataset with a student’s marks and names.
What we want to accomplish: Explore how to use Goal Seek in Excel by:
- Understanding the Goal Seek parameters.
- Operating Goal Seek on a dataset.
- Getting a hang of the Goal Seek errors and problems.
Goal Seek parameters
When you want to work with Goal Seek, it is important to first understand the parameters needed to complete this process:
Set cell
In this box, you will have to enter the cell address you want corrected or changed.
To value
This box requires your desired output. For instance, 70% of the total marks could be the desired output.
By changing cell
In this box, you have to enter the cell address that needs to be adjusted. If a student requires 70% of the total marks, the ‘by changing cell’ will have the cell address of the final project marks that the student needs to secure to get to this 70%.
Note: The ‘By changing cell’ is one variable input. Goal Seek works with only one variable input. If you have multiple input values, the Solver is a valuable Excel tool.
Step
Organize your data
To use Goal Seek, you first need to open up your Microsoft Excel spreadsheet, which features all the data and information you want to use.
For our demonstration, we have the marks that the student has secured throughout the term, except the final project marks.
Once your data is organized, you will need to select the cell that you want to change. In our case, this is cell C10, which refers to the final project marks of the student.
Step
Select Data tab
After selecting the cell, head up to the top ribbon menu on your window. From the tab menu, you will need to choose the ‘Data’ tab.
On the Data menu, you need to find the ‘What if Analysis’ button and select ‘Goal Seek’ listed under it.
Clicking this will bring up a Goal Seek dialog box near your selected cell.
Step
Type the desired value
In the ‘To value’ box, you will need to type in the desired result. For example, in our demonstration, we enter 70% in the ‘To value’ box. In the set cell, we select the cell where the percentage is calculated.
This implies that we want to change 42% to 70%. Next, we enter the cell address that we want to change. In our case, we want to determine the final project marks that the student needs to secure to get an overall 70% mark on the course.
So, in the By changing cell’ box, we enter cell C10 for the final project marks.
Here’s what our Goal Seek dialog box looks like:
Step
Final result
Once that is done, click ‘OK,’ and Excel will automatically overwrite your cell’s value to calculate the result you need to achieve your desired outcome.
Once it has found the solution you need, you can choose to accept it by selecting ‘OK’ once more. This will keep the solution result in your spreadsheet.
Alternatively, you can select ‘Cancel’ to return your spreadsheet to its original state.
Since we got the answer we needed, we clicked ‘OK’ to keep the answer.
Goal Seek errors and problems
Unfortunately, there can be errors and issues that crop up when trying to use the Goal Seek function in Microsoft Excel. Let’s take a look at a few of them to see how you can fix them.
Error 1: Cell must contain a formula in Goal Seek
If the error ‘Cell must contain a formula’ comes up in Goal Seek when trying to calculate your solution, this means that there is no formula written in the cell you want to change.
This means that for Goal Seek to work, you need to be using formulas in your spreadsheet.
To solve this, you need to change the data in the cell you want to change from a written format to a formula.
Excel will automatically turn this formula into the answer, but Goal Seek requires the formula for it to function.
Let’s consider our example once again. In our data, we used a simple formula to calculate the current percentage of marks secured by the student. If we change it to a static 42% instead of a formula that calculates the percentage, we get a ‘Cell must contain a formula error’:
Error 2: Simplified results
Another issue with Goal Seek is irregularities or simplified results.
A lot of people who use Goal Seek complain that their answers were rounded up or not accurate – this is because Goal Seek cannot take into account too many variables or formulas.
So, only use Goal Seek for very simple spreadsheets. Anything too complicated will only result in misleading solutions.
For complicated formulas and spreadsheets, the add-on Excel Solver is more than capable of finding accurate solutions.
Error 3: Reference must be a single cell
The Goal Seek function requires one cell with a formula, one desired value, and one variable value. If you choose more than one variable value, Goal Seek will return the error ‘Reference must be a single cell.’
In our case, if the student has not yet given Test 3 and the final project and we wish to use Goal Seek to calculate the marks for both assessments, it will give this error:
Wrapping up
Goal Seek is a valuable function in Excel that simplifies a lot of calculations. However, to use Goal Seek, one must understand its parameters and know about the common errors it returns. To make things easier for you, we have talked about how to use Goal Seek in Excel and have explained what to do in case you face any errors.
Learn more about Excel through these helpful guides:
