How to use Goal Seek in Excel

We're not going go too deeply into Goal Seek, because it can get quite complicated. We'll stick with a basic outline, and an example, of just what it is.

Goal Seek

Goal Seek is used when you know what answer you want, but don't know the exact figure to input for that answer. For example, you're quite certain that 8 multiplied by something equals 56. You just not sure what that missing number is. Is it 8 multiplied by 6? Or Is it 8 multiplied by 7? Goal Seek will tell you the answer.

We'll test that example out right now. So start a new spreadsheet, and create one the same as in the image below:

Before you can use Goal Seek, Excel needs certain things from you. First it needs some sort of formula to work with. In the image above we have the simple formula =B1 * B2. We've put this in cell B3. But the answer is wrong for us. We had a Goal of 56 (8 times something). We want to know which number you have to multiply 8 by in order to get the answer 56. We tried 8 times 6, and that gave the answer of 48. So we have to try again.

Instead of us puzzling the answer out, we can let Goal Seek handle it. So do the following:

• 
  
  From the Excel menu bar, click on Tools


• 
  
  From the drop down menu, click on Goal Seek


• 
  
  A dialogue box pops up like the one below:

The dialogue box needs a little explaining. "Set cell" is the answer you're looking for, this is the Goal. Set cell needs a formula or function to work with. Our formula is in cell B3, so if your "Set cell" text box does not say B3, click inside it and type B3.

"To Value" is the actual answer you're looking for. With "Set cell", you're just telling Excel where the formula is. With "To Value" you have to tell Excel what answer you're looking for. We wanted an answer of 56 for our formula. So click inside the "To Value" text box and type 56.

"By Changing Cell" is the missing bit. This is the part of the formula that needs to change in order to get the answer you want. In our formula we have an 8 and a 6. Clearly, the 6 is the number that has to go. So the cell that needs to change is B2. So go ahead and enter B2 in the "By Changing Cell" text box. Your dialogue box should now look like this:

Click OK when your dialogue box looks like the one above. Excel will then Set the cell B3 to the Value of 56, and change the figure in cell B2. You'll also get a dialogue box like the one below:

Click OK on the dialogue box. Your new spreadsheet will look like this one:

So Goal Seek has given us the answer we wanted: it is 7 that when times by 8 equals 56.

Increase your profits with Goal Seek

To give you a more practical example of what Goal Seek does, consider this problem. You have a business that generates 25 thousand pounds worth of profit. You currently sell 1000 items at 25 pounds each. You want to increase your profit to 35 thousand pounds. Assume that you're still going to sell 1000 items. By how much does the price of each item have to increase by in order to generate the new profit total?

We'll work it out together using Goal Seek. And then you can have a try yourself with an exercise. First, here's a new spreadsheet for you to download:

Download the Goal Seek Spreadsheet

When you open the spreadsheet, you'll notice that the Current Sales Figures and the Future Sales Figures are exactly the same. The formula in Cells B4 and E4 are = B2 * B3 and = E2 * E3

We can use Goal Seek to solve our problem. What we want to know is, What should be the new Price Per Item in order to generate Profits of 35 thousand?

• 
  
  So, from the Excel menu bar, click on Tools


• 
  
  From the drop-down menu, click Goal Seek


• 
  
  The Goal Seek dialogue box appears

This time, our formula is in cell E4. So we want to Set the cell to the cell that has our formula. So type E4 into the "Set cell" text box.

The "To Value" text box will hold our new Profits. The Goal we are aiming for is 35 thousand. So type in 35000 in the "To value" text box.

The cell we want to change is the Price Per item figure. So in the "By changing cell" text box, type in E3.

Click OK when you're done. Excel will give you this dialogue box:

The dialogue box is telling you that Goal Seek has found a solution. Click OK. Your spreadsheet will already have changed. The new Future Sales Figures will be displayed. Your spreadsheet should look something like the one below:

Goal Seek has given us the answer of 35 pounds. So the cost of each item has to increase by 10 pounds if we want a profit of 35 thousand.