Sensitivity Analysis with Data Tables

Excel Guides / What-If Analysis

What-If Analysis

Run one model across a whole grid of inputs and see every result at once.

Updated 12 Oct 2026 · 7 min read · By Sky Dashboards

Goal Seek answers one question at a time. A data table answers dozens. You give it a list of values for an input, or two lists for two inputs, and Excel recalculates your model once for each value and writes every result into a grid. It is the standard way to show how sensitive a profit or a price is to the assumptions behind it, with no macros and no copies of the model.

How a data table works

A data table has three parts:

• A model with typed inputs and a result formula.
• Trial values: the inputs you want to test, listed down a column, across a row, or both.
• A link to the result: a formula pointing at the model's output, placed where the trial values meet.

When you run Data Table, Excel takes each trial value in turn, drops it into the input cell, recalculates, records the result and moves on. The model itself never changes: when the table is finished, the original inputs are still in place.

One-variable data table

A pottery studio runs evening workshops. Each seat sells for $65 and costs $14 in clay, glaze and firing. Rent and kiln costs are $2,200 a month. At 80 seats a month the profit is $1,880. The owner wants to see revenue and profit for anything from 40 to 120 seats.

1. Type the trial values down a column: 40, 60, 80, 100 and 120 in D3:D7.
2. In the row above the first value, one column to the right, link to the results you want: =B2*B4 for revenue in E2 and =B6 for profit in F2. You can add as many result columns as you like.
3. Select the whole block, including the blank corner, the link row and the trial values: D2:F7.
4. Go to Data > What-If Analysis > Data Table...
5. Leave Row input cell empty. In Column input cell, click B4, the seats input in the model.
6. Click OK. Excel fills E3:F7.

The 80-seat row matches the model exactly ($5,200 and $1,880). That row is a free check that the table is wired to the right input.

Row input cell or column input cell?

This is where most data tables go wrong. The names describe how your trial values are laid out, not where the input sits in the model:

• Trial values listed down a column, as in D3:D7 above: use Column input cell.
• Trial values listed across a row: use Row input cell.
• Trial values along a row and down a column (a two-variable table): fill in both.

Get it the wrong way round and Excel does not complain. It substitutes the wrong values into the input and fills the table with numbers that look plausible but are wrong. Always check that the row or column matching the model's current input shows the same result as the model.

Two-variable data table: a price by volume profit grid

A two-variable table tests every combination of two inputs. The studio wants profit for five prices and five volumes: 25 scenarios in one grid, on the same sheet as the model.

1. Type the prices across a row: $45 to $85 in E9:I9.
2. Type the seat counts down a column: 40 to 120 in D10:D14.
3. In the corner cell where they meet, D9, link to the result: =B6. A two-variable table can only show one result.
4. Select D9:I14.
5. Open Data > What-If Analysis > Data Table... Set Row input cell to B2, because the prices run across the row, and Column input cell to B4, because the seat counts run down the column.
6. Click OK.

Read along a row to see what a price rise is worth at a fixed volume, or down a column to see how many seats each price needs. At $45 the studio needs more than 70 seats just to break even; at $85 it makes a profit even at 40 seats.

The {=TABLE()} array it creates

Click any result cell and the formula bar shows something you cannot type yourself:

• It is one block. You cannot edit or delete a single result cell; Excel says it cannot change part of a data table. To remove the table, select all the result cells, E10:I14, and press Delete.
• The input cells must be on the table's sheet. If they are not, Excel reports that the input cell reference is not valid. The rest of the model can live elsewhere: keep the inputs on the table's sheet and have the model refer to them.
• It stays live. Change the cost per seat to $16 and every cell in the grid updates. Copy the grid and paste it as values if you need a frozen snapshot.

Recalculation speed

Every cell in a data table is a full recalculation of the model. The 5 by 5 grid above runs it 25 times. A 20 by 20 table runs it 400 times, and Excel repeats all of that whenever the workbook recalculates, even after an edit that has nothing to do with the table.

If typing starts to lag, switch to Formulas > Calculation Options > Automatic Except for Data Tables. Ordinary formulas keep updating as you work, and the tables wait until you press F9.

Format the grid with conditional formatting

A grid of 25 numbers is hard to scan. Colour makes the break-even line jump out:

1. Select the results only, E10:I14. Leave out the corner link and the trial values.
2. Go to Home > Conditional Formatting > Color Scales > More Rules...
3. Set Format Style to 3-Color Scale.
4. For Midpoint, set Type to Number, Value to 0 and the colour to white. Set Minimum to red and Maximum to green.
5. Click OK.

Losses now shade red, profits green, and the cells nearest break-even stay pale. With the midpoint left on the default 50th percentile, the colour would turn at the middle value of the grid rather than at zero, which says nothing about break-even.

For a simpler look, use Highlight Cells Rules > Less Than..., type 0 and keep Light Red Fill with Dark Red Text. More options are in the conditional formatting guide.

Frequently asked questions

Why does my data table show the same number in every cell?

The input cell is usually wrong: either the result formula does not use it, or the row and column input cells are swapped. If the numbers look stale rather than identical, check the calculation mode and press F9.

Can a data table use an input on another sheet?

No. The row and column input cells must be on the same sheet as the table. Move the input to that sheet and link the model to it.

How do I delete a data table?

Select all the result cells, or the whole table, and press Delete. Excel will not let you edit or delete just part of it.

Can a two-variable data table show more than one result?

No, only the formula in the corner cell. Build a second table for a second result, or use a one-variable table, which can show several results side by side.

Related guides

All 32 Excel guides