Goal Seek in Excel

Goal Seek is a unique Excel function. It’s useful for making decisions and evaluating scenarios. Let’s learn how to use Goal Seek in Excel.

How to Use Goal Seek in Excel

Imagine you’re a basketball player, with a target of 15 points, on average, per game. You want to hit this over ten games, and you’ve already played nine of them. Now, you need to know how many points you have to score in the tenth game to reach your target.

Goal Seek allows you to force data to meet your desired output value, in this case 15. It tells you what needs to happen to reach your goal.

Let’s work through the data. As you can see, your first nine games are listed, along with the average of the nine. Cell B11 is blank. What you’re finding is the value of B11 that makes B12 equal an average of 15.

Click into cell B11. Find the Data tab on Excel’s ribbon. On the right side, click on What-If Analysis in the Forecast section. Then, from the dropdown, choose Goal Seek.

 

You’ll see the Goal Seek dialogue box appear, asking for three inputs. These are Set cell, To value, and By changing cell. Your Set cell is the output, so click into the Set cell box and then type B12.

To value is the desired result. Type 15.By changing cell is the input value, so click into the field and then type B11.

Press OK. Excel will perform the Goal Seek analysis, and return the score you will need to reach your goal of 15. As you can see, that’s 40 points.

The utility of Goal Seek is readily obvious. It really helps you explore data and ponder potential outcomes by doing a What-If analysis. What would be complex math on paper becomes a simple task, thanks to Excel’s forecasting power.