How to hit a target value using Goal Seek

Nov 11, 2021 | Excel Hints and Tips

Have you ever tried to work out what you need to change a figure to in order to hit a target value?  Do you end up randomly changing a figure on your spreadsheet until you get close to what you need the figure to be?  Does this take you ages?  Do you think there must be a better way? 

Well there is!!  The solution is goal seek! 

Working with Goal Seek 

Goal seek enables a cell containing a calculation to be set to a specific value by changing a cell that affects that value either directly in one calculation or indirectly through a chain of calculations. 

  1. On the Data tab choose What-If Analysis and Goal Seek 
  2. Change the Set Cell to the cell reference that needs to be set to specific value 
  3. Change the To Value to the value you want the Set Cell to be changed to 
  4. Change the By Changing Cell to the cell that will be altered to reach the value required 

Example 

In the spreadsheet below we are looking to find out how many computers need to be sold to reach a turnover of £100,000. 

Spreadsheet Goal Seek

The goal seek dialog box would need to be set as follows. 

Goal seek dialog box

We would be setting the total turnover in B8 to £100,000 by changing the number of computers sold in B5 

Goal Seek Status 

The goal seek status will inform you whether a solution has been achieved or not.  The way Excel tries to find a solution is using a process called iteration where it tries numbers which get closer and closer to the result but a solution is not always possible. 

Goal Seek Status

OK will accept the solution or Cancel will return the figures back to what they were before the goal seek was started. 

This is a really simple example but the more complex the combination of calculations are that affect the result the more difficult this is to achieve manually and the more useful Goal Seek becomes. 

This can be particularly useful for sales forecasting and projections but is actually really helpful for anything where a target figure is looking to be met. 

Further Reading

If you’ve enjoyed reading about the Goal Seek feature in Excel, there’s some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.