Last Updated on
Goal Seek is a great function in Microsoft Excel which can be used to find out the desired input value for a formula when you already know the output value.
However, not a lot of people know that this feature exists and those who do struggle to use it.
Check out our guide below on how to use Goal Seek in Microsoft Excel.
How To Use Goal Seek In Microsoft Excel
Learn how to use the Goal Seek feature in Excel
Step
Open Microsoft Excel Spreadsheet
To use Goal Seek, you first need to open up your Microsoft Excel spreadsheet that features all the data and information you want to use.
Step
Select Cell
Once your spreadsheet is open, you will need to select the cell that you want to change.
Step
Select Data Tab
Then, head up to the top ribbon menu on your window. From the tab menu, you will need to select the ‘Data’ tab. You can find it in between ‘Formulas’ and ‘Review’. Find it, select it and this will open up the Data menu.
Step
Select Goal Seek From Drop Down Menu
On the Data menu, you need to find the ‘What if Analysis’ button.
It will also have an icon of a spreadsheet with a question mark (?) overlaid on top of it. Find it, select it, and then select ‘Goal seek’ from the drop down menu that will appear.
This will bring up a Goal Seek dialog box near your selected cell.
Step
Type Desired Result In The ‘To Value’ Box
In the ‘To value’ box, you will need to type in the desired result. For example, if the original data was 2 but you wanted it to be 5, then you would type 5 into the ‘To value’ box on the Goal Seek dialog box.
Step
Type In The Cell You Need To Change
Then, in the ‘By changing cell’ box, you will need to type in the cell you need to change.
This will be the cell you need to change to get your desired result – in a typical Excel spreadsheet table, this will be the cell next to your desired result cell.
Find that cell and write its cell reference into the ‘By changing cell’ box.
Step
Click OK
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 result.
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.
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.
If the error ‘Cell must contain a formula’ comes up 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 written format to a formula.
Excel will automatically turn this formula into the answer, but Goal Seek requires the formula for it to function.
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 for finding accurate solutions.
