How to use the Excel goal seek tool to see what needs to happen to one cell to get a specific result in another cell.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 03:16
- - Skill Level: 6
- - What's my skill level
Excel Goal Seek tool
The Excel GOAL SEEK tool allows you to generate a required result by automatically changing an identified input cell
In this example we have a simple spreadsheet where we have the Sales Price, some Units Sold over various months, Cost of Sales %(COS) and the resultant Growth Profit. Lets say we want to find out how by changing the Sales Price, we can achieve a Gross Profit of 200.
To activate the GOALSEEK,
- you click on Tools,
- GOALSEEK and this box will pop up,
- now what you’ll see it says tell me, “which cell I must set?”, In this case we want that cell to be set,
- it asks you for “what Value it must go to?” , so we want it to go to 200
- and it asks “what cell needs to be changed to achieve this result?”, and in this case we know it’s the Sales Price
- It is important to remember that the cell that you are going to change has to be an input, it cannot be a formula.
- so if we click Ok
- you’ll see it goes through and gives you a result, that results in the result you want
- and it says “I’ve found a solution is that ok?” and we can push Ok,
In a similar way, let’s say we now want to get a Gross Profit of 250 by changing the Cost of Sales %
- so we click on Tools
- GOALSEEK
- and we say set this cell which is correct, to a value of 250 by changing our Cost of Sales %
- and when we press Ok
- well see that it tells us in order to get a Gross Profit of 250, the Cost of Sale % needs to be about 25%
You can also perform GOALSEEK straight off a graph
- so if we say Ok here
- maybe we think this graph looks a bit funny and the Sales in Month 3 are a bit high.
- If you click on the graph on that bar
- You’ll see that it highlights all the bars
- if you click on it again you’ll see it highlights only the on the Month 3 bar
- and if you go towards the top you’ll see the cursor changes,
- and you can actually drag it down,
- when you let go it immediately says “ok it looks like you want to change cell E4 to a value of 134 – what cell must I change to achieve this?” and in this case we want to change the Units Sold
- and when we click Ok
- you’ll see it generates the number of Units Sold to meet your particular requirements
- and again, once you’re happy with that you can say Ok