This Excel feature helps me make better decisions (and avoid regrets)

I’ve made my share of decisions I wish I could undo. Like setting the wrong price point for a few products or building a vacation budget in Excel that stretched my finances too thin. Excel doesn’t come with a crystal ball, but it does include tools that help you explore what could happen before you commit. With just a few clicks, you can test alternate possibilities, forecast results, and even reverse-engineer your goals. That’s exactly what Excel’s What-If Analysis tools are designed to do.

You’ll find three of them — Goal Seek, Scenario Manager, and Data Tables — under the Data tab, within the Forecast group, labeled What-If Analysis. Each one offers a way to visualize potential outcomes and, ultimately, make choices with fewer regrets.

Goal Seek

Find out how you can reach your target

Goal Seek is a tool for when you know the result you want from a formula but need to figure out what input will get you there. You might know the revenue you want to hit or the profit margin you’d like to reach, but you’re unsure how to adjust your inputs to make it happen. That’s the sweet spot for Goal Seek. It reverses the usual direction of a formula: instead of starting with an input to see the output, you’re telling Excel, “I want this output to be X. Now, find the input that makes that true.”

Goal Seek works with one variable input value at a time. For example, imagine your organization makes a fruit drink. You know your cost to produce each unit and the profit margin you’d like to achieve, but you need to determine the minimum selling price that will make that possible. Your sheet might look like this:

The formula in the profit margin cell (B4) calculates the margin based on the selling price and the COGS. The goal is to make that calculated profit margin equal your desired target — say, 40%. In the Goal Seek menu, set the Set cell field to the cell with your profit margin formula (B4), the To value field to 0.40 (your desired target), and the By changing cell field to your selling price (B3). When you click OK, Excel will calculate the selling price needed to ensure the profit margin equals 40%.

Because it adjusts only one variable at a time, Goal Seek is best suited for straightforward financial questions with a single unknown. If your analysis involves multiple variables, you’ll need something else.

Scenario Manager

Compare your best case, worst case, and most likely scenarios

What if your decision depends on several interacting variables—price, cost, marketing spend—each shifting at once? That’s where Scenario Manager comes in. It’s designed for situations involving multiple variables (up to 32 changing cells per scenario).

With Scenario Manager in Excel, you define scenarios, such as optimistic, realistic, or conservative, each containing a unique set of input values. You specify which cells should change (for instance, price, cost, or ad budget), and Excel stores those variations as separate scenarios you can switch between at any time.

Imagine you want to evaluate how different recruitment strategies affect your total cost and cost per hire. Your worksheet might look like this:

Here, the goal is to analyze Cost Per Hire under different sets of assumptions. To do that, open Scenario Manager and select Add. In the Add Scenario dialog, give your scenario a name and choose your Changing cells—the cells that will vary between scenarios. For example, you might name your first scenario High Spend to Hire Quickly and select cells B1–B3 (agency fee %, ad cost, and referral bonus).

Next, the Scenario Values menu appears, where you enter specific values for each of your changing cells (say, 0.20, 12000, and 2500 for B1, B2, and B3). Click Add to create another scenario, such as Use Existing Channels or Minimize All Discretionary Spend. After adding your final scenario, click OK instead of Add to return to the main Scenario Manager menu. From there, you can highlight any scenario and select Show to see how your outcome cells, like Total Recruitment Cost or Cost Per Hire, change across different assumptions.

Once your scenarios are built, switching between them instantly reveals how your results shift under different conditions. Whether you’re weighing a price increase against a marketing expansion or balancing costs across departments, this tool helps you make data-backed decisions with confidence.

Data Tables

Test dozens of possibilities in seconds

Now imagine wanting a panoramic view of your options. Data Tables let you calculate multiple results within a single table on one worksheet by changing one or two variables. You can create a one-variable table (to vary a single input) or a two-variable table (to vary two inputs at once). They’re excellent for examining a range of possibilities at a glance — no need to click through menus or rerun calculations.

Suppose you’re a student and want to know what score you need on your final exam to achieve a specific overall grade. You could set up your dataset like this:

Before using the Data Table feature, list your possible exam scores in a single row or column — say, 10%, 25%, 50%, and 90%. Then, reference your formula cell just above or beside those values. For instance, if your scores are in A8:A17 (one column), reference the formula in B7 (in the next column). If they’re in C6:L6 (a row), reference the formula in B7(on the next row). You’ll understand why this setup matters once you see how the two-variable table works. In practice, referencing the formula means placing =B4 (or whichever cell contains your formula) in the reference cell.

Next, highlight the range you’ve created (either A7:B17 or A20:K21) and open the Data Table menu. Depending on how you arranged your data, enter the cell containing your final exam score (in this case, B3) into either the Column Input Cell or Row Input Cell field.

Click OK, and Excel will populate the table with the calculated overall grades for each possible exam score. You’ll see all potential outcomes at once, making it easy to identify the score you need to reach your desired grade.

That’s an example of a one-variable table. For two variables, imagine you want to analyze how your profit changes as both your selling price and cost of goods sold vary. You might start with something like this:

Unlike the one-variable table, you’ll create two sets of possible values here—one for selling price and another for COGS. Place one set along a single row and the other along a single column, ensuring they intersect. Reference your formula cell in that intersecting cell. For example, you could list possible COGS values in B7:H7 and selling prices in A8:A17, with the intersecting cell A7 referencing your formula (e.g., =B4).

Highlight the range (A7:H17) and launch the Data Table menu. This time, enter the cell containing your base COGS (B2) in the Row Input Cell field, and the one containing your base selling price (B1) in the Column Input Cell field.

Excel will instantly display the total profit for every combination of selling price and COGS, giving you a panoramic view of profitability risks and opportunities.

Of the three What-If Analysis tools, Data Tables are the most complex to set up, but they’re also the most powerful for visualizing multiple outcomes at once. Just keep in mind that they’re limited to one or two input cells. If your model involves more than two variables, you’ll be better off using scenarios instead.

Don’t make big (or small) calls without using What-If Analysis

Every decision you make — especially those that involve numbers — is a what-if exercise. But your brain can only juggle so many possibilities at once. These Excel tools help you test your assumptions, compare trade-offs, and discover potential problems before you commit.

You don’t need to master all three immediately. Start with Goal Seek, the simplest, to explore a single question that matters to you. Then, try building a few Scenarios to compare different plans, or experiment with a two-variable Data Table when you’re ready to map out a bigger decision. Over time, you’ll find yourself running simulations and making choices you won’t regret.