How to Use the What-If Analysis in Excel

Excel's What-If Analysis tools can feel like a hidden feature, but they are incredibly powerful for making smarter business decisions. Instead of manually changing numbers in your spreadsheet to see what might happen, these tools let you systematically test different scenarios to forecast outcomes. This article walks you through exactly how to use the three core components of What-If Analysis: Scenario Manager, Goal Seek, and Data Tables.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is What-If Analysis Anyway?

What-If Analysis is a set of tools in Excel that allows you to experiment with data to see how changing certain values (or variables) affects the results of your formulas. It's the process of asking "what if this number were different?" and getting an immediate, calculated answer. This is essential for business activities like budgeting, financial forecasting, sales planning, and risk assessment.

Instead of creating dozens of copies of the same spreadsheet, you can use these tools to model and compare multiple outcomes in a single, organized place. Excel’s suite includes:

• Scenario Manager: Lets you create and save different groups of input values (scenarios) and switch between them to see the different results. It's perfect for comparing distinct possibilities, like a "Worst Case," "Expected Case," and "Best Case" budget.
• Goal Seek: Works backward. If you know the result you want from a formula, Goal Seek will figure out what input value you need to get there. It’s ideal for simple problems like, "How many units do we need to sell to reach $100,000 in revenue?"
• Data Tables: Showcases how changing one or two variables in a formula impacts the outcome across a wide range of possibilities. This is fantastic for sensitivity analysis, such as modeling how different interest rates and loan terms affect a monthly payment.

You can find all three tools on the Excel ribbon under the Data tab, in the Forecast group, by clicking the What-If Analysis dropdown.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Use Scenario Manager for Comparing Outcomes

Scenario Manager is your go-to when you need to compare a few distinct, well-defined scenarios. Imagine you're planning a marketing campaign and want to model a few different budget allocations.

Let's set up a simple example. Our goal is to calculate the total Return on Investment (ROI) based on spending for ads and content creators. Our spreadsheet looks like this:

Worksheet Setup:

• B2: Ad Spend
• B3: Content Creator Cost
• B4 (Total Spend): `=SUM(B2:B3)`
• B5: Projected Revenue
• B6 (ROI): `(B5-B4)/B4` This cell should be formatted as a percentage.

Step 1: Create Your First Scenario (Baseline)

First, input your baseline or "most likely" values. Let's say Ad Spend is $5,000, Content Creator Cost is $2,000, and Projected Revenue is $15,000. This gives us a total spend of $7,000 and an ROI of 114%.

Now, let's save this as a scenario:

1. Navigate to Data > What-If Analysis > Scenario Manager.
2. In the window that appears, click Add.
3. For Scenario name, type "Expected Case".
4. For Changing cells, select cells B2, B3, and B5. You can select multiple non-adjacent cells by holding down Ctrl (or Cmd on Mac) while clicking them. These are the inputs we'll be changing.
5. Click OK.
6. A new window will appear showing the current values for your selected cells. Since these are our baseline values, just click OK.

Step 2: Add More Scenarios

Now, let's add a "Best Case" and "Worst Case" scenario.

Add the "Best Case" Scenario:

1. In the Scenario Manager box, click Add again.
2. Name this one "Best Case". The Changing cells should still be pointing to B2, B3, and B5. Click OK.
3. In the Scenario Values window, enter your optimistic numbers. Let's say we get a deal on ads, but invest more in a top-tier creator, and revenue soars.
4. Click OK.

Add the "Worst Case" Scenario:

1. Click Add one more time.
2. Name it "Worst Case" and click OK.
3. Now, enter your pessimistic numbers. Ad costs go up, and the campaign underperforms.
4. Click OK.

Step 3: Compare Scenarios and Generate a Summary

You now have three scenarios saved. You can double-click on any scenario in the list and click Show to watch the values on your worksheet change instantly. This is great for a quick look.

The real power, however, comes from the summary report.

1. In the Scenario Manager window, click Summary...
2. Ensure Scenario summary is selected.
3. For the Result cells, select cell B6 (our ROI calculation). You can add B4 as well if you want to see the Total Spend. Click OK.

Excel will generate a brand new, perfectly formatted worksheet that neatly compares all your scenarios. It will show the changing values for each scenario and the final results for ROI and Total Spend, making it incredibly easy to present your findings.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Use Goal Seek to Find a Specific Input

Goal Seek is simpler than Scenario Manager but no less useful. It's designed to solve one problem: finding the single input value needed to achieve a specific goal.

Let's use a sales example. You sell a product for $49. Your fixed costs are $5,000 per month. You want to know how many units you need to sell to break even (i.e., make a profit of $0).

Worksheet Setup:

• B1 (Units Sold): Guess, say 100
• B2 (Price Per Unit): $49
• B3 (Total Revenue): `=B1*B2`
• B4 (Fixed Costs): $5,000
• B5 (Profit): `=B3-B4`

With 100 units sold, our profit is -$100. We want to find the number of units that makes the profit exactly $0.

Using Goal Seek to Find the Breakeven Point

1. Go to Data > What-If Analysis > Goal Seek.
2. A simple dialog box will appear. Fill it out as follows:
3. Click OK.

Excel will instantly run through thousands of possibilities and find the answer. The Goal Seek Status window will show that it found a solution, and cell B1 on your sheet will now display the break-even number (approximately 103 units). You can click OK to keep this new value or Cancel to revert to your original number.

Goal Seek is perfect for these kinds of "back-of-the-napkin" calculations right inside your model.

How to Use Data Tables for Sensitivity Analysis

Data Tables are fantastic for analyzing how one or two variables affect a formula's result across a whole range of possibilities. This is more in-depth than Goal Seek, as it shows you an entire spectrum of outcomes, not just one.

One-Variable Data Table

Let's continue with our breakeven analysis. We know we need to sell 103 units at $49 to break even. But what if we change the price? A one-variable data table can show us our profit at various price points.

1. Set up the structure: In a separate area of your sheet, list the different prices you want to test in a column (e.g., in cells D4:D10, list prices from $35 to $65).
2. In the cell one column to the right and one row above your list (cell E3), you must reference your original output formula. So, in cell E3, type `=B5` to link it to our Profit calculation.
3. Select the range: Highlight the entire table, including your list of prices and the formula link at the top (D3:E10). Cell D3 can be left blank or contain a descriptive header.
4. Open the Data Table tool: Go to Data > What-If Analysis > Data Table.
5. Define the input cell: Because our list of variables (prices) is in a column, we will use the Column input cell box. In this box, select the original price cell from your model, which is B2.
6. Click OK.

Excel will instantly fill the table, calculating the profit for each corresponding price point, assuming all other variables (like Units Sold) remain constant. This gives you a clear view of how pricing affects profitability.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Two-Variable Data Table

What if you want to see how profit changes if both price and units sold change? That's where a two-variable data table comes in.

1. Set up the structure: This is slightly different:
2. Select the range: Highlight the entire structure, including both variable lists and the corner formula (D3:J10).
3. Open the Data Table tool: Go to Data > What-If Analysis > Data Table.
4. Define both input cells:
5. Click OK.

Excel will populate the grid with the profit for every combination of price and units sold. You now have a comprehensive sensitivity analysis that shows which scenarios lead to profit or loss at a glance.

Final Thoughts

Mastering Scenario Manager, Goal Seek, and Data Tables moves you from simply recording data in Excel to actively modeling the future. Whether you're comparing budget options, reverse-engineering a sales target, or stress-testing a financial plan against different variables, Excel's What-If Analysis tools are invaluable for turning your spreadsheets into powerful decision-making engines.

As powerful as these tools are, setting up complex models in Excel can still be time-consuming, especially when your data lives across a dozen different platforms like Google Analytics, Shopify, and your CRM. We built Graphed to remove this friction by connecting directly to your data sources and allowing you to ask these same "what-if" questions using natural language. Instead of wrangling cells and formulas, you can get instant dashboards and insights, helping you get to the answer faster.