Excel Goal Seek tool

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