Have you ever needed to figure out the missing numbers in a spreadsheet? Whether you’re trying to hit a sales target, balance student grades, or project your inventory needs, Excel’s Goal Seek tool can help you find unknown goal data and make more informed decisions. In this post, we’ll be using Excel’s “Goal Seek” for various scenarios. Stick with me—this is one feature you’ll wish you’d learned ages ago.

What Is Goal Seek in Excel?

Goal Seek is part of Excel’s “What-If Analysis” toolset. It allows you to “reverse-calculate” the inputs needed to achieve a specific goal in a formula. Instead of working forward and guessing numbers, you input the target, and Excel finds out what data is missing.

Imagine you’re a salesperson trying to figure out how many units of a product you need to sell to hit a revenue target. Or maybe you’re working with inventory projections and need to establish the cost per item to meet a revenue goal. You could spend hours calculating manually or just ask Excel to do the work for you. Spoiler alert: Excel will do it faster and with fewer mistakes.

Practical Example: Finding Missing Data for Sales Goals

Let’s say you have annual sales data for 2024 for three stores: Save-A-Lot, Trader Joe’s, and Walmart. Each store has a different goal, but there’s some unknown data in the spreadsheet. Here’s where Goal Seek shines.

The goals are:

  • Save-A-Lot needs to hit $5,000 in total sales
  • Trader Joe’s has the same target of $5,000
  • Walmart has a specific target to sell pineapples worth at least $1,000, but not a total sales goal

However, some values—like quantities and prices—are missing. We’ll use Excel’s Goal Seek to find out what unknown data is needed to hit these goals.

RELATED TOPIC: How to Link Excel Data to PowerPoint Charts

Using Goal Seek for Save-A-Lot’s Sales Target

Save-A-Lot needs at least $5,000 in sales, and the price per unit is $4.99. But the unknown data is the quantity of units they need to sell to hit that goal. Here’s how to solve it using Excel’s Goal Seek.

  1. Select the empty quantity cell in the spreadsheet (this will be the cell Excel changes).
  2. Go to the Data tab, then select What-If Analysis, and click Goal Seek.
  3. In the Goal Seek window, you’ll need to fill in:
    • Set Cell: Select the cell that contains the total sales, as that’s what we want to adjust (the goal is $5,000).
    • To Value: Enter 5000, since Save-A-Lot’s target is $5,000.
    • By Changing Cell: Select the empty quantity cell where the quantity will be calculated.
  4. Hit OK, and Excel automatically calculates that Save-A-Lot needs to sell 329 units at $4.99 to reach $5,000.

Be sure to click OK in the pop-up to lock that number in place.

Finding Missing Pricing Data for Trader Joe’s Sales Goal

Next, Trader Joe’s also has a $5,000 goal, but this time we know the quantity sold: 425 units. We don’t, however, know the price per unit. Goal Seek can tell us exactly how much we need to sell each item for to hit $5,000.

  1. Select the empty price per item cell (this is the cell Excel will change).
  2. Open Goal Seek from the What-If Analysis menu under the Data tab.
  3. In the Goal Seek box:
    • Set Cell: Select the total sales cell again.
    • To Value: Enter 5000, since the goal is $5,000.
    • By Changing Cell: Pick the price per item this time.
  4. Click OK, and Excel will figure out that Trader Joe’s needs to sell each item for $11.76.

Now you know the exact price per item needed to make that goal.

RELATED TOPIC: How to Use the IF Function in Excel

Using Goal Seek for Walmart’s Pineapple Goal

Walmart’s case is slightly different. Instead of having a total sales goal, they need to reach $1,000 in pineapple sales only. They know the price per pineapple—$2.59—but we need to find the missing data which is the quantity, Excel’s goal seek can handle this too.

  1. Select the quantity cell again, as that’s what we’re missing.
  2. Open Goal Seek from the What-If Analysis.
  3. Fill out the Goal Seek box like this:
    • Set Cell: Select the total amount for pineapples.
    • To Value: Enter 1000.
    • By Changing Cell: Choose the quantity cell for pineapples.
  4. Hit OK, and Excel will show that Walmart needs to sell 386 pineapples at $2.59 each to hit $1,000.

When and Where to Use Goal Seek

Goal Seek isn’t just for sales targets. You can apply it to a variety of situations:

  • Inventory Projections: How many products do you need to stock to reach a sales goal?
  • Budget Forecasting: What expense cutoffs do you need to hit a savings goal?
  • Student Grades: What score do you need on the final to pass the class?

By reversing the process and letting Excel find the unknown data, you save time, avoid errors, and focus on the bigger picture. If your analysis involves formulas and goals, there’s a good chance you can use Goal Seek to make your life easier.

RELATED TOPIC: How to Add Your Favorite Events to Your Outlook Calendar

Conclusion

Excel’s Goal Seek tool is an often-overlooked feature that can be a game-changer when you need to find unknown data for any goal. While many of us manually adjust numbers in dozens of different ways, Goal Seek does the hard work for you. Once you understand how it works, you’ll find endless applications for business, personal finance, and beyond.

If you’d like to try it yourself, follow along with the video tutorial linked above and see how easy it is to apply Goal Seek across different scenarios.

Don’t forget to like, subscribe, and share this post if you found it helpful. Thanks for reading!

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR