13- 17 May, 2013

Excel Goal Seek tool

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

• 03:16
• Skill Level:
• 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

Handy Tips & Tricks

Advanced Excel Course – June 2013 in South Africa Our next advanced excel course is running during June 2013 in Johannesburg. During the week of the 10th June we offer the following options. You can either attend the full 5 … Continue reading

Creating a Waterfall Chart in Excel the easy way

A very popular chart is the waterfall chart which shows the impact of various items on a total e.g. the various components that explain the move from budgeted profit to actual profit. A common way to build this is using … Continue reading

Just a quick note to say your blog is brilliant. Full of useful bits of info. After your courses, I am by far the best modeller in our office, and we regularly get comments from our business partners on the quality of our models. Thanks for all the help,

– Mike

View Entire Video Gallery ››

• 03:14
• Skill Level:
• What's my skill level

• 00:42
• Skill Level:
• What's my skill level

Waterfall chart using stock charts

• 07:11
• Skill Level:
• What's my skill level

Absolute and Relative cell references

• 06:34
• Skill Level:
• What's my skill level