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

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