What If Analysis in Excel

Excel's What-If Analysis tools let you change the input values in your formulas to see how those changes will affect the outcome. Instead of manually creating dozens of tables to compare results, you can use these built-in features to forecast different possibilities quickly. This article will walk you through setting up and using Excel’s three main What-If Analysis tools: Scenario Manager, Goal Seek, and Data Tables.

What is What-If Analysis in Excel?

What-If Analysis is a process of changing values in cells to see how those changes affect the outcome of formulas on your worksheet. Think of it as a way to test hypotheses or explore different future outcomes for your business without having to manually recalculate everything. For example, you could ask:

• "What if we increase our ad budget by 20%? How would that impact our lead generation goal?"
• "What if our conversion rate drops by 0.5% next quarter? What will our projected revenue be?"
• "What if we raise the price of our product to $55? How many units do we need to sell to hit our profit target?"

Excel gives you three powerful tools to answer these kinds of questions:

1. Scenario Manager: Lets you create and compare groups of input values (scenarios) side-by-side. It's perfect for when you have several variables that might change, like in a "best-case," "worst-case," and "most-likely" forecast.
2. Goal Seek: Works backward to find the right input to get the output you want. If you know the result you need, Goal Seek will tell you what it’ll take to get there.
3. Data Tables: Show you how changing one or two variables in a formula affects the result across a whole range of possibilities, all displayed in a simple table.

You can find all three of these tools on the Data tab, under the Forecast group, within the What-If Analysis dropdown menu.

Using Scenario Manager for Multiple Variables

Scenario Manager is your go-to tool when you’re dealing with multiple uncertainties and want to compare the outcomes. Imagine you're planning a marketing campaign and want to model a few different possibilities based on your budget and expected performance.

Let’s set up a simple model. We’ll look at launching a new ad campaign, and our success depends on three key variables: Monthly Ad Spend, Cost Per Click (CPC), and Conversion Rate. Our goal is to calculate the total number of new customers.

Creating Marketing Scenarios

Now, let’s create three scenarios: A "Most Likely" case (our current data), a "Pessimistic" case (lower budget, higher CPC, lower conversion), and an "Optimistic" case (more budget, lower CPC, better conversion).

Step 1: Open Scenario Manager

Go to Data > What-If Analysis > Scenario Manager.

Step 2: Add Your First Scenario

In the Scenario Manager dialog box, click Add. We'll start with the "Pessimistic" scenario.

• Scenario name: Pessimistic Case
• Changing cells: Select cells C3:C5. Hold the Ctrl key to select a non-contiguous range if needed.
• Click OK.

Step 3: Enter the Values for the Scenario

A new window will appear, prompting you to enter the values for your selected "Changing cells." Let's plug in numbers that reflect a worst-case outlook:

• Ad Spend (C3): 3000
• CPC (C4): 2.50
• Conversion Rate (C5): 0.015 (or 1.5%)

Click Add to save this scenario and immediately start creating the next one.

Step 4: Add the Other Scenarios

Now, create the "Optimistic Case."

• Scenario name: Optimistic Case
• Changing cells: C3:C5 (this should be pre-filled)
• Click OK and enter your optimistic values:

Click OK. You'll now see both of your new scenarios listed in the Scenario Manager.

Step 5: View Scenarios and Create a Summary

Back in the Scenario Manager, you can click on any scenario name ("Pessimistic Case," "Optimistic Case") and click Show to see the numbers in your worksheet update instantly.

The real power, however, comes from the summary report. Click the Summary... button.

• In the pop-up, make sure "Scenario summary" is selected.
• For the Result cells, select the cells containing your final calculations. In our case, that would be C7 (Total Clicks) and C8 (New Customers).
• Click OK.

Excel will instantly generate a new, neatly formatted worksheet summarizing all your scenarios, the input values for each, and the resulting outcomes. This gives you a clear, side-by-side comparison, making it easy to present your findings.

Finding Your Target with Goal Seek

While Scenario Manager is for exploring multiple outcomes, Goal Seek is for finding a specific input. It’s perfect when you know the result you want to achieve but aren't sure what single variable needs to change to get you there.

Let's use a simple sales example. Your team sells a product for $199 per unit. Right now, you plan to sell 400 units, for a total revenue of $79,600. The company has set a new quarterly revenue goal of $100,000.

You want to ask: How many units do we need to sell to reach $100,000 in revenue?

Step 1: Open Goal Seek

Select the cell containing your formula - in this case, the Total Revenue cell (B4). Then, navigate to Data > What-If Analysis > Goal Seek.

Step 2: Fill in the Goal Seek Fields

The dialog box is very straightforward:

• Set cell: This is the cell with the formula you want to change. It will be pre-filled with B4 because we already selected it.
• To value: This is your target outcome. Type 100000.
• By changing cell: This is the single input cell you want to adjust to meet your goal. For us, that’s Units Sold (cell B3).

Step 3: Run Goal Seek

Click OK. Excel will run through the calculations and find the exact value needed. In seconds, it will find a solution and ask if you want to keep the new value.

The result shows that you need to sell approximately 503 units to reach your $100,000 revenue target. Click OK to update cell B3 with this new value or Cancel to return to your original numbers.

Seeing a Range of Outcomes with Data Tables

Data Tables are ideal when you want to see how changes in one or two variables impact your bottom line across a spectrum of possibilities. It’s like running dozens of what-if scenarios at once and presenting them in a single, clean table.

One-Variable Data Table

A one-variable data table shows how changing one input cell affects multiple formulas. Let’s say you’re analyzing the profitability of a product and want to see how different price points affect your revenue and net profit.

Step 1: Set Up Your Table Structure

• List the potential price points you want to test in a column (e.g., in cells E4:E8).
• In the row directly above your price points, and one column to the right, enter references to your result formulas. In cell F3, enter =C6 (pointing to Revenue). In cell G3, enter =C7 (pointing to Profit).

Step 2: Create the Data Table

• Select the entire range of your table, including the column of inputs and the row with formula references (in this case, E3:G8).
• Go to Data > What-If Analysis > Data Table.
• Since our variables are in a column, we will use the Column input cell field. Click into this field and then click on the original input cell that the values in your column should replace. For this example, that is the Price Per Unit cell (C3).
• Leave the Row input cell blank.
• Click OK.

Excel will populate the table instantly, showing you the Revenue and Profit for each price point. This allows you to quickly spot the most profitable price.

Two-Variable Data Table

As the name suggests, a two-variable table lets you test the combined effect of changing two inputs. Let's see how changing both the Price Per Unit and the Units Sold affects our total profit.

Step 1: Set Up Your Table Structure

• In the top-left corner of your table range (e.g., cell E3), enter a reference to your output formula (=C7 for Profit).
• List one set of variables down the first column (e.g., potential Units Sold in E4:E8).
• List the second set of variables across the top row (e.g., potential Price Per Unit in F3:H3).

Step 2: Create the Two-Variable Table

• Select the entire table range (E3:H8).
• Go to Data > What-If Analysis > Data Table.
• For Row input cell: link to the original cell for the variables listed in your top row. For us, that's Price Per Unit (C3).
• For Column input cell: link to the original cell for the variables in your first column. For us, that's Units Sold (C4).
• Click OK.

Excel fills in the grid with the resulting profit for every combination of price and units sold. Now you have a complete matrix of potential outcomes, making strategic planning far easier.

Final Thoughts

Mastering Scenario Manager, Goal Seek, and Data Tables will transform how you approach planning and forecasting in Excel. These tools help you move beyond static numbers and explore a dynamic range of possibilities, enabling you to make more informed, data-driven decisions for your business. Practice them with your own data to see just how powerful they can be.

Manually setting up these analyses in Excel is a fantastic skill, but it can be time-consuming, especially when your data lives across different platforms like Google Analytics, Shopify, or your CRM. At Graphed, we built a tool to simplify this entire process. You can connect your data sources in a few clicks and then use simple, natural language - just like talking to an analyst - to build real-time dashboards and model scenarios in seconds, no formulas required. This gives you instant answers about what's working, what's not, and where to focus your strategy next.