Goal Seek tries to help you by finding the correct variable you need to reach a certain goal. This allows you to analyze Excel data where certain variables are just unknown—until you use Goal Seek, that is. Here’s how to use it. This feature is available in Microsoft Excel versions from 2010 onwards, and the instructions below should remain the same across all versions.
What is Goal Seek in Excel?
How many sales do you need for a product to reach a sales revenue target? You could work this out manually, but this could soon become complicated for more advanced queries. Instead, you can use the Goal Seek feature in Microsoft Excel to ponder the question and find your target answer. After all, a mathematical formula is simply a question with an answer. This kind of what-if analysis requires you to have a target value you want to reach, as well as a current value in a cell that Excel can change to try and reach your target value. For instance, for our product revenue example, you’d need to have the number of sales in a cell. You could then use Goal Seek, using the product revenue target as the target value, to determine the number of sales you’d require.
Excel will then change the value (in this instance, the sales target) to meet the target value. You’ll then see a preview of the result—you can then apply the changes to your sheet or cancel them.
How to Use Goal Seek in Excel
To use the Goal Seek tool, open your Excel spreadsheet and press the Data tab on the ribbon bar.
In the Forecast section of the Data tab, press the What-If Analysis button. This will display a drop-down menu—press the Goal Seek option to begin.
The Goal Seek window has three values that you’ll need to set.
The Set cell is the cell containing your mathematical formula—the question without the right answer (at present). Using our product sales example, this would be the sales target formula (1) which, at present, is currently $2500. In the To value box, set your target value. For our product sales example, this would be the product revenue target (for instance, $10,000). Finally, identify the cell containing the changing variable in the By changing cell box (2). For our example, this would contain the current number of sales. At present, 100 sales—this will need to change to reach the increased target value figure. Goal Seek will change this number (100 sales) to be able to reach the target value set in the To value box.
Press the OK button to begin the Goal Seek what-if analysis. If Goal Seek finds a suitable answer that meets the target value, it’ll display this in the Goal Seek Status box.
For this example, the original number of 100 sales has been increased to 400 sales to reach the target value of $10,000. If you’re happy with the answer that Goal Seek finds, press the OK button. Otherwise, press Cancel—this will close the Goal Seek window and restore the original values.
Better Excel Data Analysis
Using the Goal Seek feature in Excel is a great option if you’re missing some of the pieces to a mathematical question. There are alternatives to Goal Seek, however, including the Excel Solver add-in. Once you have your data, you can use the Excel CONCATENATE function to build upon your data and present it for better Excel data analysis. You’ll need to know some common Excel tips if you’re new to Excel, however.
