How to Do Sensitivity Analysis in Excel

Building a forecast in Excel is one thing, but understanding what makes it tick is another. A sensitivity analysis - sometimes called a what-if analysis - is how you move beyond a single static number and see how your outcomes change when key assumptions fluctuate. It's the best way to stress-test your model, identify risks, and make smarter decisions. This article will walk you through three practical methods for running a sensitivity analysis in Excel, from simple manual checks to powerful automated tables.

What is Sensitivity Analysis Used For?

At its core, sensitivity analysis lets you see how sensitive a key output (like profit or revenue) is to changes in your input variables (like price, marketing spend, or conversion rate). Instead of relying on a single "best guess," you test a range of possibilities to answer questions like:

• "What happens to our net profit if the cost of raw materials increases by 10%?"
• "How many units do we need to sell to break even if we lower our price by $5?"
• "Which factor has a bigger impact on our ROI: a 15% increase in ad spend or a 5% increase in conversion rate?"

This process is crucial for financial modeling, budgeting, risk assessment, and general business planning. It helps you identify the most critical drivers of your business, understand potential risks, and build buffer plans for different outcomes.

Setting Up Your Excel Model for Analysis

Before you can analyze anything, you need a properly structured model. A messy spreadsheet will only lead to confusing or incorrect results. Your model should have three clear components:

1. Inputs (or Assumptions): These are the variables you will change. They should be in separate, clearly labeled cells. Hard-coding numbers directly into formulas is the number one mistake to avoid.
2. Calculation Engine: This is the part of your model with all the formulas that connect your inputs to your outputs.
3. Outputs: This is the final result you care about measuring, like 'Net Profit', 'Revenue', or 'Customer Lifetime Value'.

Let’s use a simple profit forecast as our example throughout this tutorial. Here’s how it looks in Excel:

Inputs:

• Units Sold (Cell B1): 5,000
• Price per Unit (B2): $50
• Variable Cost per Unit (B3): $20
• Fixed Costs (B4): $75,000

Calculations & Outputs:

• Revenue (Cell B6): =B1*B2
• Total Variable Costs (B7): =B1*B3
• Profit (B8): =B6-B7-B4

With this structure, you can change any value in cells B1 through B4, and the Profit in cell B8 will automatically update. This setup is the foundation for all the methods below.

Method 1: Manual Scenario Testing

The simplest way to start is by manually changing your input cells and watching what happens to the output. This is great for quick, back-of-the-napkin analysis.

For example, you could create a simple table to track a "Best Case," "Base Case," and "Worst Case" scenario for your Profit.

1. Test the Base Case: With your original numbers, the Profit is $75,000. Write this down.
2. Test the Worst Case: Maybe you're worried about price pressure. Manually change the 'Price per Unit' in cell B2 to $45. Your Profit in B8 automatically updates to $50,000. Write this down.
3. Test the Best Case: Now, change the price in B2 to $60. Your Profit jumps to $125,000. Write this down.

Pros: Quick, easy to understand, and requires no special Excel features. Cons: Incredibly tedious if you want to test more than a few values. It's also prone to human error and doesn't provide a good way to compare many data points at once.

Method 2: Using Excel Data Tables (The Best Way)

For a more robust and automated analysis, Excel's Data Tables are the perfect tool. They do the manual work for you, calculating an output based on a whole range of potential inputs. There are two types: one-variable and two-variable tables.

How to Use a One-Variable Data Table

A one-variable data table shows how changing one input cell affects the values of one or more output cells. Let's see how our Profit changes as we adjust the 'Price per Unit' from $40 to $60.

1. Set up your table structure. In an empty section of your sheet, list the different input values you want to test in a single column. For our example, let's type the prices $40, $45, $50, $55, and $60 into cells D2 through D6.
2. Link to your model's output. In the cell directly above and to the right of your input column (E1 in our case), create a direct link to your final output. Type =B8 into cell E1 to link it to your Profit calculation. You can optionally add more output headers to the right (e.g., in F1, link to Revenue =B6) if you want to track multiple results.
3. Open the Data Table tool. Select the entire range that contains your values and your output link (D1:E6). Go to the Data tab on the Ribbon, click on What-If Analysis, and choose Data Table...
4. Configure the input cell. The Data Table dialog box will pop up. Since your variable inputs are in a column, you will use the "Column input cell" field. Click in this box, and then select the original input cell in your model — in our case, cell B2 (Price per Unit).
5. Generate the table. Leave the "Row input cell" field blank and click OK.

Instantly, Excel fills the table by taking each price from your column (D2:D6), plugging it into cell B2, recalculating the entire model, and displaying the resulting profit in the corresponding row. You now have a clean summary of how profit reacts to changes in price.

How to Use a Two-Variable Data Table

What if you want to see how two variables — like 'Price per Unit' and 'Units Sold' — affect your profit simultaneously? That’s where a two-variable data table comes in.

1. Set up your grid structure. List the values for your first variable down a column (e.g., Prices in D2:D6). Then, list the values for your second variable across a row (e.g., Units Sold in E1:G1, with values like 4000, 5000, 6000).
2. Link to your output in the corner. In the top-left corner cell, where the row and a column meet (D1), you must link to your single output. Type =B8 into cell D1. Note: a two-variable table can only show one output.
3. Open the Data Table tool. Select the entire range, including the two sets of inputs and the corner output link (D1:G6). Again, go to Data > What-If Analysis > Data Table...
4. Configure both input cells. This time, you'll use both fields:
5. Generate the grid. Click OK. Excel will populate the table, showing you your profit at every intersection of units sold and price point.

Method 3: Using Excel’s Scenario Manager

Scenario Manager is a good fit when you need to compare a handful of distinct situations where multiple inputs change all at once. For example, comparing an "Aggressive Growth" plan vs. a "Conservative Budget" plan.

1. Open Scenario Manager. Go to the Data tab > What-If Analysis > Scenario Manager...
2. Add your first scenario. Click the Add button.
3. Enter the values for the scenario. A new dialog box will appear, showing your selected cells and their current values. Since this is your "Base Case," the current values are correct, so just click OK.
4. Add more scenarios. Back in the main Scenario Manager window, click Add again. Create a "Worst Case" scenario. The same "Changing cells" will be selected. Click OK and enter more pessimistic numbers (e.g., fewer units sold, lower price, and higher variable cost). Repeat this process for a "Best Case" scenario.
5. View and summarize. From the Scenario Manager window, you can double-click any scenario to see your main model instantly update with those values. For a clean report, click the Summary... button. In the "Result cells" field, select the output cell you want to track (B8 for Profit) and click OK. Excel will generate a brand new worksheet with a perfectly formatted summary report comparing the inputs and outputs for each scenario side-by-side.

Visualizing Your Analysis

Numbers in a table are useful, but a chart is often more powerful for telling a story.

• For a One-Variable Data Table: Use a simple Line or Column Chart. This will clearly show the direct relationship between your input (on the X-axis) and output (on the Y-axis). Is it a straight line? Does it curve? The shape tells you about the sensitivity.
• For a Two-Variable Data Table: The best way to visualize this kind of matrix is with a heat map. To do this, select just the calculated results inside your two-variable data table. Then, go to the Home tab > Conditional Formatting > Color Scales and choose a Green-Yellow-Red scale. This instantly colors the cells, showing you the "hot zones" (high profit) and "cold zones" (low profit) at a glance.

Final Thoughts

Whether you're performing a quick manual check, an automated deep-dive with Data Tables, or comparing specific outcomes with Scenario Manager, you now have the Excel skills to see behind the numbers. This moves you from simple forecasting to a dynamic understanding of the key levers that drive your business, helping you prepare for risk and seize opportunities.

While Excel is fantastic for this type of modeling, setting up these analyses can still involve a lot of manual steps. The dashboards are static and need to be rebuilt every time you want to explore new questions. At my company, we designed Graphed to remove this friction entirely. Instead of configuring data tables and scenario reports, you can connect your data sources (like Shopify, Google Ads, or Salesforce) and just ask an AI-powered data analyst natural language questions like, "What if we increase our ad spend by 20%? How would that impact revenue projection in the next quarter?" Graphed builds live, interactive visualizations in seconds, letting you go from question to insight without getting bogged down in spreadsheet mechanics.