What is Sensitivity Analysis in Excel?

Performing a sensitivity analysis helps you predict the outcome of a decision given a certain range of variables. Also known as "what-if" analysis, it’s a powerful way to understand how changes in key inputs - like price, ad spend, or conversion rates - affect your bottom line. This article will show you what sensitivity analysis is, why it's a vital skill, and exactly how to perform it using built-in Excel tools.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is Sensitivity Analysis, Really?

At its core, sensitivity analysis is about building a small model to test a variety of scenarios quickly. It helps you answer critical business questions by highlighting which variables have the biggest impact on your results. Think of it as a way to understand the cause-and-effect relationship between your inputs and your outputs.

To run a sensitivity analysis, you need three things:

• Variables (or Inputs): These are the "levers" you can pull. They are the independent variables you want to test, such as the price of a product, monthly ad spend, or the cost of raw materials.
• A Model (or Formula): This is the engine of your analysis. It's a set of calculations that connect your variables to your desired outcome. In Excel, this can be as simple as a single formula or a series of interconnected cells.
• The Outcome: This is the dependent variable you’re measuring, like a company's net profit, project ROI, or total sales.

For example, imagine you run an e-commerce store selling handmade mugs. Your monthly profit is your outcome. The variables you can control are the price per mug, your monthly advertising budget, and the cost per unit from your supplier. Sensitivity analysis allows you to tweak each of these variables to see how they directly change your final profit, showing you which one to focus on for the biggest gains.

Why Is Sensitivity Analysis So Important?

Moving a variable up or down in a spreadsheet is simple, but understanding the impact of that change is where real business intelligence comes from. Running a sensitivity analysis takes your spreadsheets from being static records to dynamic decision-making tools.

• Improved Decision-Making: It provides a clear view of potential outcomes, helping you choose the best path forward. Instead of guessing the right price for your mugs, you can model how different prices affect profit and confidently choose one.
• Identifying Key Drivers: It reveals which input has the most influence on your final result. You might discover that a 10% reduction in material costs has a much larger impact on profit than a 10% increase in ad spend, telling you where to focus your energy.
• Effective Risk Assessment: It prepares you for the unexpected by showing you the potential downside of different scenarios. What happens to your projected break-even point if shipping costs suddenly spike by 25%? A sensitivity analysis gives you that answer ahead of time.
• Building More Credible Forecasts: When you present financial models, stakeholders will inevitably ask, "But what if...?" Sensitivity analysis gives you the answers, adding a layer of robustness and confidence to your projections.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Performing Sensitivity Analysis in Excel: Data Tables

Excel's "What-If Analysis" toolkit contains a few fantastic features for this, and the most versatile one is the Data Table. It lets you test how changing one or two variables in your model affects a single key outcome.

Building the Base Model

First, you need a simple, working model. Let's stick with our e-commerce mug store. Here's a basic profit calculation setup in Excel:

• Cell B2: Units Sold = 200
• Cell B3: Price per Unit = $25
• Cell B4: Cost per Unit = $10
• Cell B6: Total Revenue = =B2*B3
• Cell B7: Total Costs = =B2*B4
• Cell B9: Profit = =B6-B7

With these numbers, your profit in cell B9 comes out to $3,000. This is our "base case." Now, we want to know what happens to the profit if we change the price or the number of units sold.

How to Use a One-Variable Data Table

A one-variable data table lets you see how different values of a single input cell will change an outcome. Let's test different selling prices.

Step 1: Set Up Your Table Structure In a blank area of your sheet, list the different values you want to test for your variable. For our example, let's test prices from $20 to $30. In column D, starting at D5, enter these values:

• D5: $20
• D6: $21
• D7: $22
• ...and so on, down to $30.

Step 2: Link Your Output Formula In the cell directly above and to the right of your variable list (in this case, E4), you need to reference your final profit formula. Simply type =B9 in cell E4 and press Enter. This tells Excel which outcome to calculate.

Your setup should look something like this: Cell D5:D15 contains your list of prices. Cell E4 contains =B9 (your profit formula).

Step 3: Run the Data Table Tool Highlight the entire range of your table, including both the numbers you typed and the formula reference (D5:E15 in our example). Then, go to the Data tab, click on What-If Analysis, and select Data Table.

Step 4: Specify the Input Cell Since your list of variables ($20, $21, etc.) is arranged in a column, you'll use the "Column input cell" box. Click into this box, then click on the original input cell from your model that you are testing—in this case, cell B3 (Price per Unit). Leave the "Row input cell" blank.

Click OK. Excel will automatically fill the column next to your prices, showing you the actual profit for each price point. Instantly, you see how profit changes from about $2,000 (at $20 a mug) to $4,000 (at $30 a mug).

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Use a Two-Variable Data Table

What if you want to see how changes in both price AND units sold affect profit? A two-variable data table is perfect for this.

Step 1: Set Up Your Table Structure You'll build a grid. Your first variable (Prices) will go down a column (like before), and your second variable (Units Sold) will go across a row. For example:

• Enter Prices in cells D5:D15.
• Enter Units Sold values in cells E4:H4.

Step 2: Link Your Output Formula The output formula must go in the top-left corner of the table, at the intersection of the row and column variables. In this example, cell D4. Type =B9 in D4.

Step 3: Run the Data Table Tool Select the entire table range, now including the row and column variables as well as the empty space to be filled. Go to Data > What-If Analysis > Data Table again.

Step 4: Specify Both Input Cells Fill in both boxes:

• Row input cell: This is the value your row variables (Units Sold) will modify. Click in the box and select the original Units Sold cell, B2.
• Column input cell: This is for your column variables (Price). Click and select cell B3 (Price per Unit).

Click OK. Excel instantly calculates the profit for every combination of price and units sold, giving you a comprehensive view of all scenarios. You can add conditional formatting to quickly spot high-profit and low-profit combinations.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Working Backwards with Goal Seek

A Data Table is great for seeing many potential outcomes. But what if you have a specific target outcome in mind and want to find the input needed? That's where Goal Seek comes in.

Goal Seek solves a different kind of "what-if" question: "What input do I need to get this specific result?" For example, how many mugs must we sell to break even (i.e., achieve $0 in profit)?

How to Use Goal Seek

Using our same base model:

Step 1: Open Goal Seek Go to Data > What-If Analysis > Goal Seek.

Step 2: Fill in the Three Fields The dialog box is straightforward:

• Set cell: This is the output cell you want to define. For our break-even analysis, this is the Profit cell, B9.
• To value: The target result. For break-even, type 0.
• By changing cell: The input cell to adjust. We want to see how many units to sell, so select cell B2 (Units Sold).

Click OK. Excel runs the calculation and tells you that the "Set cell" reached the goal. It updates cell B2 with the new value— the number of units to sell for break-even. It might show approximately 0 units, if you set a different target profit, say $5,000, then it calculates the units needed to reach that profit.

Goal Seek is useful for finding metrics like sales targets, marketing conversion thresholds, or pricing needed to hit specific profit margins.

Best Practices for Reliable Analysis

• Keep Your Model Simple: Build clear, easy-to-understand models. Avoid unnecessary complexity that could hide errors.
• Test Realistic Scenarios: Use variable ranges grounded in real-world data. Test best-case, worst-case, and most-likely scenarios for practical insights.
• Document Your Assumptions: Note assumptions like a 5% increase in material costs for later reference.
• Visualize Your Results: After generating Data Tables, turn them into charts. Line charts work well for one-variable analysis, and heat maps for two-variable tables can reveal insights visually.

Final Thoughts

Sensitivity analysis in Excel transforms static spreadsheets into dynamic, interactive decision-making tools. Mastering Data Tables and Goal Seek allows you to explore how variables impact key outcomes confidently, enabling effective planning, goal setting, and risk management.

Building these models in worksheets is powerful but can involve manual data entry for each scenario. At Graphed, we connect directly to your data sources like Google Analytics, Shopify, and CRM to automate this process. You can ask natural language questions like "Show me a chart of projected revenue if ad spend increases by 20%," and get instant dashboards—saving time and reducing manual setup.