What a sensitivity table does and why you'd build one

A sensitivity table (also called a data table) lets you see how one number changes when you alter the inputs that feed into it. You build it in Excel by setting up a formula once, then letting Excel automatically recalculate that formula across dozens or hundreds of different input values at the same time. The result is a grid showing you every outcome side by side, so you can spot patterns without typing the same formula over and over.

The most common use is financial: you have a monthly budget or loan payment or profit calculation, and you want to see what happens if the interest rate goes up, or the price per unit drops, or you hire one more person. Instead of changing one cell, running the calculation, writing down the answer, changing it again, and repeating fifty times, you build a table that does all fifty calculations at once.

Sensitivity tables also work for any scenario where you're testing "what if" questions — project timelines, material costs, production volumes, anything where one or two inputs drive a final number you care about.

Key Takeaways

  • A sensitivity table in Excel automatically recalculates a formula across multiple input values, showing you all the outcomes in a grid without retyping the formula.
  • One-variable tables test how a single input (like interest rate or price) changes your result; two-variable tables show how two inputs interact to change the outcome.
  • You set up the table by placing your formula in one corner, your test values along the top row and left column, then using the Data Table feature to fill in all the results at once.
  • The table uses absolute and relative cell references so Excel knows which input to swap out each time it recalculates.

Setting up a one-variable sensitivity table

Start with a working formula in a single cell. For example, say cell C5 contains =B2*B3 (price times quantity), and you want to see what your total revenue looks like if the price varies from $10 to $100.

In a blank area of your spreadsheet, create two columns. In the first column, list all the price values you want to test — $10, $15, $20, and so on down to $100. Leave the cell directly above your list empty. In the column to the right, one row up from your first price value, type the cell reference to your formula: =C5 (or whatever cell holds your formula). This tells Excel where to look for the calculation to repeat.

Now select the entire range: the empty cell at the top left, all your input values in the left column, and the formula reference in the top right. In Excel, go to the Data menu, then click What-If Analysis, then Data Table. A dialog box opens asking for the Column input cell — this is the cell in your original formula that you want Excel to swap out. Type the cell reference (in this example, B2, which holds the price). Click OK. Excel fills the table with results when ready.

Each row now shows what your formula produces when that price is plugged in. You can read across and see the pattern: as price goes up, revenue goes up proportionally (assuming quantity stays the same).

Building a two-variable sensitivity table

A two-variable table shows how two different inputs interact. Using the same revenue example, you might want to see what happens when both price and quantity change. This creates a grid where one input varies across the top and the other varies down the left side.

Set up your test values in an L-shape: put one set of values (prices, $10 to $100) across the top row starting in the second column. Put the other set of values (quantities, 1 to 100 units) down the first column starting in the second row. In the top-left cell of this L-shape (the intersection), type your formula reference: =C5.

Select the entire rectangle — from the top-left cell all the way down and across to include all your test values. Go to Data > What-If Analysis > Data Table. This time the dialog asks for both a Row input cell (the input that varies across the top, like B2 for price) and a Column input cell (the input that varies down the side, like B3 for quantity). Fill in both and click OK.

The result is a grid where you can read any combination: find the row for 50 units and the column for $25, and the cell at their intersection shows your revenue for that exact scenario. Two-variable tables are especially useful when two factors compete or reinforce each other.

Choosing your input values wisely

The values you test should cover the range you actually care about. If you're modeling a loan payment and interest rates in your area run from 3% to 7%, test that range — not 0% to 50%. If you're testing production volumes and your factory can make 100 to 500 units per month, those are your boundaries.

Space your test values evenly if you want to spot trends easily. Testing prices at $10, $11, $12, $13 shows fine detail but creates a very long table. Testing at $10, $20, $30, $40 is often enough to see the pattern. You can always add more rows or columns later if you need to zoom in on a particular range.

For two-variable tables, keep the number of test values reasonable. A table with 20 prices and 20 quantities creates 400 cells to calculate — Excel handles it when ready, but the result becomes hard to read. Start with 10 values in each direction and expand only if you need more precision.

Reading and using your sensitivity table

Once the table is built, scan it for the outcomes that matter to your decision. If you're looking at loan payments, find the payment amount at your expected interest rate and term, then look left and right to see how sensitive the payment is to small changes in rate. If the payment barely moves when the rate changes by 0.5%, the loan is stable. If it jumps significantly, you're taking on more risk.

Sensitivity tables also help you spot break-even points. If you're modeling profit and you see the number flip from negative to positive as you move across the table, that's the price or volume where you start making money. That information is often more useful than any single calculation.

You can format the table to highlight important values: use conditional formatting to color cells red if profit is negative and green if it's positive, for example. This makes patterns jump out visually without requiring you to read every number.

Common mistakes and how to avoid them

The most frequent error is using the wrong cell reference in the Data Table dialog. If your formula is in C5 but you tell Excel the column input cell is C5 instead of B2 (the actual input you want to vary), the table fills with the same number repeated. Double-check: the cell you reference in the dialog should be a cell that your formula uses, not the cell that contains the formula.

Another mistake is forgetting to include the formula reference in the top-left corner of your table range. If you select only your input values and not the formula cell, Excel has nothing to calculate and the table stays blank. Always include that top-left cell in your selection.

For two-variable tables, mixing up row and column inputs is straightforward. If your prices are across the top, the row input cell should be the price cell in your formula. If quantities are down the left side, the column input cell should be the quantity cell. Getting these backwards swaps your results.

When to use a sensitivity table instead of other approaches

A sensitivity table is faster than manually changing values and recording results, and it's cleaner than building separate formulas for each scenario. It's also better than a chart alone because you can see exact numbers, not just visual trends.

However, if you need to test more than two variables at once (price, quantity, and shipping cost, for example), a sensitivity table becomes unwieldy. At that point, consider a scenario manager or a more complex model. If you only need one or two "what-if" calculations, a straightforward formula change might be faster than building a table.

Sensitivity tables shine when you're exploring a range of possibilities and want to see the full picture at once — when the question is not "what if this one thing changes" but "what if this thing changes by any amount in this range, and what does that tell me about my risk or my opportunity."

Frequently Asked Questions

Can I change the input values after I build the table?

Yes. Edit the values in your input rows or columns, and the table recalculates automatically. The data table formulas are live, so any change to an input value updates all the results that depend on it.

What if my formula references cells outside the table?

That's fine. Your formula can pull from anywhere in the spreadsheet — other cells, other sheets, named ranges. The data table only swaps out the specific input cell you told it to. Everything else stays the same.

Can I use a sensitivity table with text values or only numbers?

Data tables work best with numbers. If you try to use text (like product names or yes/no values), Excel may not calculate correctly. Stick to numeric inputs for reliable results.

How do I delete a data table if I don't need it anymore?

Select the entire table range and press Delete. The formulas clear out and you're left with an empty grid. If you want to keep the values but remove the table structure, copy the table, paste it as values only, then delete the original.

Can I build a three-variable sensitivity table?

Not in a single table — Excel's data table feature handles one or two variables only. For three or more variables, build multiple two-variable tables (one for each pair) or use a scenario manager, which lets you name and compare different combinations of inputs.