When working with formulas in Microsoft Excel, you typically input several known variables to calculate an unknown result. For example, if you know your hourly wage and the number of hours you worked, Excel calculates your final paycheck.
However, what if you already know the final result you want, but you do not know the input required to achieve it? For example, if you want your final paycheck to be exactly £2,000, how many hours do you need to work? Instead of manually changing the “hours” cell over and over again until the final number magically hits £2,000, you can use the Goal Seek tool. This feature forces Excel to reverse-engineer the math and instantly calculate the exact input required to hit your target.
How to Set Up Your Spreadsheet for Goal Seek
Before you can use Goal Seek, your spreadsheet must contain a formula that directly relies on a variable input cell. You cannot use Goal Seek on static, typed-in numbers.
Imagine a simple spreadsheet:
- Cell B1 (Hourly Wage): 25
- Cell B2 (Hours Worked): 40
- Cell B3 (Total Pay):
=B1*B2(This calculates to £1,000).
Your goal is to figure out exactly how many hours (Cell B2) you must work to reach a Total Pay (Cell B3) of £1,500.
How to Run Goal Seek
Once your formula is in place, you can run the tool.
- Click the Data tab in the top ribbon menu.
- In the “Forecast” group, click What-If Analysis.
- Select Goal Seek… from the dropdown menu.
A small dialogue box will appear asking for three pieces of information.
- Set cell: This must be the cell containing your formula. Click inside the text box and then click on Cell B3 (Total Pay).
- To value: This is your target number. Type 1500 into the box.
- By changing cell: This is the variable you want Excel to solve for. Click inside the text box and then click on Cell B2 (Hours Worked).
- Click OK.
Reviewing the Results
The moment you click OK, Excel will run hundreds of calculations in the background in a fraction of a second.
A “Goal Seek Status” window will appear confirming whether it successfully found a solution. Look at your spreadsheet behind the window. You will see that Excel has automatically changed Cell B2 from 40 to 60, successfully proving that you must work 60 hours to reach your goal of £1,500.
- If you want to keep this newly calculated number permanently in your spreadsheet, click OK on the status window.
- If you only wanted to see the answer temporarily but want your original data back, click Cancel, and the spreadsheet will immediately revert to the original 40 hours.
Limitations of Goal Seek
While Goal Seek is incredibly powerful for financial planning, loan calculations, and sales forecasting, it has one major limitation: it can only change one variable at a time. If you need to find out how many hours you must work and what your hourly wage must be simultaneously to hit a target, you must use the more advanced Solver add-in instead.