The Goal Seek function in Excel is useful when you want to find out what value is required for a formula to return a certain output.
Users can use the Goal Seek feature to fill in the right input values needed to reach a “goal”. The feature uses a trial and error approach that solves the goal by plugging in guesses until the goal is met.
The rules for using the Goal Seek feature in Excel are as follows:
- Goal Seek requires a cell with a formula with several input values.
- The user must specify which input cell to change to achieve a target goal for the formula cell.
- Goal Seek iterates through possible values for the specified input cell to reach the target value.
Let’s explore a quick example of a scenario where we can use the Goal Seek feature.
Suppose we want to sell T-shirts online. A certain quality of shirt has a retail price of $10. If we have 100 shirts in stock, we can sell them at a discounted price of 20%. This gives us a revenue of
100*(10*(1-20%)) = $800.
Our business partner argues that we should at least reach $2000 in revenue to be able to have a decent profit for the quarter. Assuming the retail price and discount are unchanged, how many more shirts do we need to sell?
When you want to figure out a specific value that will allow a formula to reach a target, we can use the trial and error method. We can create a table similar to what we have below:
But what if we want to try to increase our goal to $10,000? Or perhaps we want to adjust the discount instead? This trial and error exercise becomes tedious.
Luckily we can use Excel’s built-in Goal Seek feature to automatically compute the right values we need to achieve our output.
We can use the Goal Seek in all kinds of situations. For example, we can use the feature to determine the lowest score needed in a final exam to get the desired letter grade. We can also use it to look for the minimum interest rate required for an investment to get a certain amount of profit in 5 years.
Let’s learn how to use the Goal Seek feature in Excel to fill out parameters that help us reach our goal. In the next section, we will share an example of a spreadsheet you can use to test out the Goal Seek feature.
A Real Example of Using Goal Seek in Excel
Let’s take a look at a real example of the Goal Seek analysis tool being used in an Excel spreadsheet.
The table below has similar input to the T-shirt scenario described earlier. We have a value for the number of units to sell, the retail price, and the discount. These inputs are used to derive the revenue.
Let’s say we want to increase our revenue to 25,000 by increasing the number of units. We can simply find out the number of additional units to sell by using the Goal Seek feature. We just have to select the cell with the formula, the target value we want, and a cell reference to the input we want to iterate on.
In this case, our formula is in cell B14, and the target we want to reach is 25,000. We allow cell B11 to be iterated on since that corresponds to the number of additional units to sell.
You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try out the Goal Seek tool in Excel, read the next section for a detailed guide!
How to Use Goal Seek in Excel
This section will help you understand how to start using the Goal Seek feature in Excel. You’ll learn how you can use this feature to automatically compute the values you need to make a formula reach its target value.
- First, select the cell with the desired formula you want to tweak.
- Next, look for the What-If Analysis icon in the Data tab. Select the Goal Seek.. option in the dropdown list.
- A dialog box will appear with a few fields we need to fill. The set cell value should equal the cell with the formula that returns the final result. The “To value:” field should be filled with the target or goal you want to reach.
- Next, you must indicate which cell you would like to iterate on. In this example, we select B1 since we want to change the number of units to sell. Once all values are set, click on the OK button.
- Excel will replace the cell you specified to change with the needed value. In this case, we find out that we need about 1,111 units to reach a revenue of $25,000. Click OK to overwrite the original value in cell B11. Click on Cancel if you want to keep the original value.
- You can redo this procedure again to try out changing different inputs to iterate on. For example, we can try changing the retail price instead of the number of units.
- In this case, we still reached the target value of $25,000, but it was done by increasing the retail price to $55.55
Frequently Asked Questions (FAQ)
- Why does my formula not work with multiple variables?
The built-in Goal Seek feature in Excel only works with one variable input value. Let’s illustrate what happens if we try to use it in a situation that requires more variable inputs.
In the example below, we have the results of an election survey. We want to know how many votes Candidate B should get to increase the 25% share to a majority, or 51%.
The Goal Seek feature seems like it works, but it simply adds votes to cell B5 without reducing any votes from the other candidates. The total number of votes has increased, which is not what we intended.
When using the Goal Seek feature, be mindful that it can only change one variable at a time.
That’s all you need to remember to start using Goal Seek in Excel. This step-by-step guide shows how easy it is to use this feature to find out what value an input must be to return the desired output.
The Goal Seek feature is just one way you can easily automate data analysis in Microsoft Excel. With so many other Excel functions out there, you can surely find one that suits your use case.
Are you interested in learning more about what Excel can do? Stay notified of new guides like this by subscribing to our newsletter!