Normally in Microsoft Excel, you input your data variables to calculate a final result. But what if you already know the exact final result you want, and you need to figure out what variable will get you there? For example, if you know you want a final exam grade of 90%, and you need to figure out exactly what score you must get on the final test to achieve it.
Instead of manually changing the test score cell over and over until the final grade cell reaches 90%, you can use Excel’s built-in “Goal Seek” tool to calculate the exact variable required in a fraction of a second.
How to Use Goal Seek
To use Goal Seek, you must have a spreadsheet set up with a formula. The tool will essentially work your formula backward.
- Open your spreadsheet in Microsoft Excel.
- Click on the Data tab located in the top ribbon menu.
- In the “Forecast” or “Data Tools” group, click on What-If Analysis.
- Select Goal Seek from the dropdown menu. A small dialogue box will appear with three distinct fields.
Configuring the Goal Seek Parameters
You must fill out the three fields in the dialogue box carefully for the calculation to work:
- Set cell: Click on the cell that contains your final formula (e.g., the cell calculating your final overall grade). This cell must contain a formula.
- To value: Type the exact numerical result you want to achieve (e.g., type
90if you want a 90% grade). - By changing cell: Click on the cell that contains the unknown variable you want Excel to figure out (e.g., the cell representing the final test score). This cell must be completely empty or contain a raw number, not a formula.
Click OK. Excel will instantly run through hundreds of calculations in the background. If it finds a mathematical solution, the “Target value” and “Current value” will match, and the “By changing cell” will now display the exact number you need to reach your goal. Click OK to keep the new number, or click Cancel to revert your spreadsheet back to its original state.