What is the Goal Seek Function in Excel?

The Excel goal seek function is a great function for scenario analysis when you know the end result but don’t know how to get there.

For example, imagine that you work in sales department of a company and your boss comes to you and says “show me how much revenue we made for this month.” So, you go to your spreadsheet, get the unit sold and the price, put a formula multiplying the units and price and the end calculation is the revenue figure that you tell your boss.

Now your boss asks you how much units we need to sell to earn $50,000 revenue. You have an option to keep changing the number of units, until you get the figure of $50,000

Unit sold = ? Price = $30 Revenue = $50,000

OR you can use goal seek function in excel to calculate the result.

Let’s look at a simplified sales report.

We need to know how much units should we sale to reach a revenue of $50,000 without changing the formula in cell b6.

Go to data > what if analysis > Goal Seek

In our example, we set the below values:

Set cell: This is the cell where we want the goal. In our example our goal is revenue. This means cell B6 since this is the cell where we are calculating our revenue.

To value: This the goal value that we want by changing variables. Our goal is to get the revenue of $50,000.

By changing cell: This is the variable that we want to find out to reach our goal. This is the number of units in our example. As we want to achieve our goal of $50,000 revenue and want to determine how much units we need to cell. This would be cell B4.

Once we give our inputs and click OK, excel tells us a summary of what goal seek has done.

When we accept the results of “goal seek function” by clicking OK, it will change the values in cell B4-units sold, so that our revenue in cell B6-Revenue is $50,000.

The important thing to note here is that goal seek has done this without changing the formula in B6 that we were using earlier to determine our revenue.

The total units that need to be sold is 1,667 in order to achieve revenue of $50,000.