How to Build a Sensitivity Analysis in Excel (Data Tables)

To build a sensitivity analysis in Excel, use a Data Table (Data tab, What-If Analysis, Data Table). Reference your output formula in the top-left corner, list input values along a row and a column, then point Excel at the row input cell and column input cell so it recalculates the output for every combination. This shows how an output like IRR or net income moves as two assumptions change.

What a Data Table actually does

A Data Table is Excel's built-in tool for stress-testing a model. You give it a grid of input values, and it substitutes each one into a single input cell, recalculates your whole model, and records the resulting output. Nothing is hardcoded: the table stays live and updates when your assumptions change.

There are two flavors. A one-variable Data Table flexes a single assumption (for example, revenue growth) and can report several outputs at once. A two-variable Data Table flexes two assumptions (for example, growth and margin) against a single output. Two-variable tables are the workhorse of valuation sensitivity grids.

The key constraint: the inputs you flex must be the real driver cells your model reads from. The Data Table temporarily writes each grid value into that driver cell, so linking to a copy or a value inside the table itself will silently produce wrong numbers.

Build a two-variable Data Table step by step

Suppose cell B2 holds revenue growth (10%), B3 holds gross margin (40%), and B10 calculates IRR. You want to see IRR for growth from 6% to 14% and margin from 30% to 50%.

1. In the top-left corner cell of your table grid (say D5), enter =B10 so the corner points at your output. Format it white-on-white if you want to hide the raw number.
2. List the row input values across the top, starting one cell to the right of the corner: put 6%, 8%, 10%, 12%, 14% in E5:I5.
3. List the column input values down the left side, starting one cell below the corner: put 30%, 35%, 40%, 45%, 50% in D6:D10.
4. Select the entire grid including the corner formula and both axes: D5:I10.
5. Go to the Data tab, click What-If Analysis, then Data Table.
6. In Row input cell, enter B2 (revenue growth, because growth runs across the row). In Column input cell, enter B3 (margin, because it runs down the column).
7. Click OK. Excel fills every interior cell with the IRR for that growth and margin combination.

Row axis is revenue growth, column axis is gross margin. The 40% margin row is the base case. Values are illustrative.

Build a one-variable table for multiple outputs

When you want several outputs (IRR, NPV, payback) against one driver, use a one-variable table. List the input values down a column, then in the row above and one cell to the right, place each output formula side by side.

Select the block (the input column plus the output formula row plus the empty grid below), open What-If Analysis, Data Table, and fill ONLY the Column input cell (because your inputs run vertically). Leave Row input cell blank. Excel substitutes each column value into your single driver and fills in every output.

• Inputs down a column: fill the Column input cell only.
• Inputs across a row: fill the Row input cell only.
• Both axes (two-variable): fill both, matching axis direction to driver cell.

Fix the four things that break Data Tables

Data Tables fail quietly, so know the usual suspects before you trust the grid.

• Input cell points to the wrong place: the row or column input cell must be the actual assumption your model reads, not a label or a copied value. If the corner formula does not trace back to that cell, every result is wrong.
• Corner formula missing: the top-left cell must contain the output reference (=B10), not a header word. A blank or text corner produces a blank table.
• Calculation set to Manual: large Data Tables are slow, so people set calculation to Automatic Except Data Tables. If your grid looks stale, press F9 or switch back to full Automatic.
• Selection too small or too large: select exactly the corner, both axes, and the empty interior. Selecting only the interior gives Excel no axes to read.