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.
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
- 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