Excel: The What If Analysis With Goal Seek

There are some Excel features that are awesome but somewhat hidden. And the "goal seek analysis" is one of them.

Sometimes you'll have a formula with a specific goal in mind, like "I want the result to be exactly 42". When you don't know how to change the formula's input to get that number, you should use goal seek.

The Dataset

To use the goal seek feature we will need some data first.

Here's a table with 2 inputs (book price and quantity sold), and a formula (revenue = price * quantity).

If we sell 100 books at $29 each, we will make $2,900.

Let's say we want to get exactly $5,000 in revenue from the book sales. How many books should we sell to get that result? There are 3 ways to answer that question:

Since this is a tutorial about goal seek, let's see how to do the last option ;-)

Goal Seek Example

Select the cell containing the revenue, and in the "data" tab click on the "what if analysis" button. There select the "goal seek" option.

A popup will appear that you need to fill like this:

Press OK, and we are done! Excel did the math for us, and automatically updated the spreadsheet with the correct answer. In this case we see that we need to sell 172 copies to get $5,000 in revenue.

Actually the number in the cell is 172.41, but it is rounded in the screenshot above.

Conclusion

With just a few clicks, we can get Excel to do some magic computation and give us interesting information.

For this example we had a simple formula. But you could do the same on something much more complex, and Excel will give you the perfect answer in no time!

Other articles you might like on ExcelFrog.com