What is a What-If Analysis in Microsoft Excel?

Microsoft Excel is far more than a simple grid for organizing lists, it's a powerful tool for forecasting and planning. If you've ever found yourself asking questions like, "What happens to our profit if we increase our ad spend?" or "How many units do we need to sell to hit our revenue target?" then you're ready to use Excel’s What-If Analysis tools. This article will walk you through the three key features - Scenario Manager, Goal Seek, and Data Tables - to help you make smarter, data-driven decisions.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What is Excel's What-If Analysis?

At its core, What-If Analysis is the process of changing the values in cells to see how those changes affect the outcome of formulas on a worksheet. Instead of creating multiple versions of the same spreadsheet or manually plugging in dozens of different numbers, you can use these built-in tools to explore various outcomes quickly. It helps you turn a static financial model into a dynamic decision-making machine.

Imagine you run a small e-commerce business selling handmade soaps. Your profit is based on a simple formula: `(Price per Soap - Cost per Soap) * Units Sold - Marketing Costs`.

What-If Analysis lets you ask questions like:

• What if the cost of my raw materials (cost per soap) goes up by 15%?
• What if a new marketing campaign doubles my units sold but also increases my marketing costs?
• What’s the lowest price I can offer during a sale and still break even?

Excel provides three specific tools under the Data > What-If Analysis menu to answer these kinds of questions:

1. Scenario Manager: Lets you create and save different groups of values (scenarios) and switch between them. Perfect for comparing a "Best Case," "Worst Case," and "Most Likely" outcome.
2. Goal Seek: Works backward. If you know the result you want from a formula, Goal Seek can find the input value that will produce that result.
3. Data Tables: Shows how changing one or two variables in your formulas will affect results across a whole range of possibilities.

Let's break down how to use each one with a practical example.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

1. Compare Different Outcomes with Scenario Manager

Scenario Manager is your go-to tool when you want to compare a handful of distinct possibilities against each other. It's excellent for presentations where you want to show stakeholders the potential high and low points of a project.

Example: Planning a Product Launch

You're planning a product launch and have a simple model to forecast its first-quarter profit. Your profit depends on three key variables: the number of units sold, the ad spend, and the conversion rate.

Your base worksheet looks like this:

A1: Launch Cost = $5,000 A2: Unit Price = $49 A3: Unit Cost = $15 A4: Units Sold = 1,000 A5: Ad Spend = $2,500 A6: Projected Profit = =(A4 * (A2 - A3)) - A1 - A5

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step-by-Step Instructions:

Step 1: Set Up Your Initial Scenario

The numbers currently in your sheet represent your "Realistic" forecast. This is your starting point.

Step 2: Add New Scenarios

1. Go to the Data tab, click What-If Analysis, and select Scenario Manager.
2. In the pop-up box, click Add.
3. Give your first new scenario a name, like "Optimistic Launch".
4. For "Changing cells," select the cells that will vary. In our case, this is A4 (Units Sold) and A5 (Ad Spend). You can select multiple cells by holding Ctrl while clicking.
5. Click OK.
6. A new window pops up asking for the values for your new scenario. For an optimistic case, let's say we sell 1,500 units and increase ad spend to $3,000. Enter 1500 for A4 and 3000 for A5.
7. Click Add to create another scenario. Now, create a "Pessimistic Launch" with units sold at 500 and ad spend at $2,500.
8. Enter the values and click OK to save each scenario.
9. Repeat for as many scenarios as needed.

Step 3: Compare Scenarios

Your list of scenarios appears in the Scenario Manager box. To preview a scenario, select it and click Show. The spreadsheet values will update automatically.

Step 4: Create a Summary Report

Click Summary... in the Scenario Manager window. Excel will ask which cell contains your final result (cell A6). Select it and click OK.

Excel will generate a new worksheet with a table comparing the inputs and projected profit for all scenarios. This is a clear, professional way to present your analysis.

2. Work Backwards to Find an Input with Goal Seek

Goal Seek is simpler but very powerful. It helps you find the necessary input value to achieve a specific goal in a cell.

Example: Hitting a Sales Target

Our soap company's model is simple: Sales Revenue depends on units sold. We want to generate $20,000 revenue this quarter. How many units must we sell?

B1: Unit Price = $8 B2: Units Sold = 2,000 (current estimate) B3: Total Revenue = =B1 * B2 (currently $16,000)

Step-by-Step Instructions:

1. Go to Data > What-If Analysis > Goal Seek.
2. A dialog box appears:
3. Click OK.
4. Excel will instantly adjust B2 to the required units needed to reach $20,000. It might show 2,500 units now.

This way, you quickly see how many units you need to sell to hit your target.

3. Explore a Range of Possibilities with Data Tables

Data Tables are great for stress-testing your models. They show how outcomes change when you vary one or two input variables across a range of values—sometimes called sensitivity analysis.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Example: Pricing Strategy Sensitivity

Suppose you're deciding on a price for a new digital product. Lower prices may increase sales but reduce revenue per unit. A Data Table can show projected profit across various price points and sales volumes.

One-Variable Data Table

Step-by-Step Instructions:

1. Set up your model with inputs for Unit Price, Units Sold, and a formula for Profit.
2. List different prices in a column, say D2:D11 ($19, $22, $25, ... $49).
3. In the cell above (E1), link it to your profit formula: type = and click on your profit cell.
4. Select the range D1:E11.
5. Go to Data > What-If Analysis > Data Table.
6. In the dialog:
7. Click OK.
8. Excel fills the corresponding profit for each price.

Two-Variable Data Table

Step-by-Step Instructions:

1. List potential prices down column D (D2:D11).
2. List potential units sold across row 1 (E1:J1).
3. Reference your profit formula in D1: type = and select the profit cell.
4. Select the table range D1:J11.
5. Go to Data > What-If Analysis > Data Table.
6. Fill:
7. Click OK.
8. Excel calculates profits for all combinations, providing a comprehensive view.

Final Thoughts

By mastering Scenario Manager, Goal Seek, and Data Tables, you transform Excel from a static data repository into a dynamic forecasting and decision-making tool. These features enable you to explore future possibilities, test assumptions, assess risks, and develop grounded strategies.

While modeling can be powerful, maintaining live data connections from platforms like Shopify or Google Analytics can be tedious manually. That’s why we created Graphed. You can connect your sales and marketing data easily, ask natural-language questions like, "Forecast sales next quarter based on current ad spend," and receive an interactive dashboard. It automates data gathering and modeling, so you focus on insights and decisions.