Goal Seek in Excel

Find a desired result by having Excel automatically change input values in Excel.

Using Goal Seek, you setup a formula or a series of formulas in a worksheet and have Excel cycle through different input values until it finds the one that generates the result that you wanted.

Note: there are limitations to using Goal Seek and sometimes you will need to use Solver instead. Goal Seek is used because it is easier and often more convenient.

Sections:

Example Scenario

How to use Goal Seek

Notes

Example Scenario

This is the example spreadsheet:

In this example we have a product that cost us $10 and currently a selling price of $12. You can see in the example that a $12 price gets us a 17% profit margin.

 However, we want a 25% margin, so, what selling price do we need in order to get a 25% profit margin?

(To calculate 17%, cell B4 contains a simple formula in it so it will update when you change cell B1 or B2.)

How to use Goal Seek

1. Go to the Data tab and click What-If Analysis and then Goal Seek...
   
   
2. In the window that opens, we need to fill-in 3 inputs.
   
   Set cell: is the cell that contains the end result that we want to get. In our example, this is the Margin cell, B4, because that is the cell that we want to contain the value of 25%.
   To value: this is the value that we want the Set cell to be equal to. In this case, that would be .25, which is 25% in decimal form.
   By changing cell: this is the cell that Goal Seek will change in order to get the desired value in the Set cell. This is cell B2, the Selling Price cell.
3. You click in the input fields and then select the desired cells on the spreadsheet, but you must type in the value for the To value input.
   In our example, it looks like this:
   
4. Click OK and Excel will do its magic.
   The next window will give some information on the solution it found and you can just hit OK to go to the spreadsheet.
   
5. Result:
   
   We now know that it will take a selling price of $13.32 in order to get a 25% margin in this case.

Notes

In many simple cases, Goal Seek will work just fine, but, Goal Seek often can't find the exact answer for more complex problems; in that case, you will need to use Solver. Solver is a lot more powerful than Goal Seek, albeit more difficult to use, and is great for iterating through problems in Excel.

I recommend trying Goal Seek to solve your problems first and, if that doesn't work, then try Solver.

Make sure to download the attached file to work with this example in Excel.