Excel What-If Data Tables: One- and Two-Variable Setup

A What-If Data Table recalculates one formula for every value in a list of test inputs (one-variable) or every combination of two lists (two-variable), producing a full sensitivity grid in a single operation instead of copy-pasting the formula manually for each scenario.

How do I build a one-variable Data Table?

List your test values down a column, put a reference to the result formula in the cell one row up and one column to the right of that list, select the full rectangular block, then tell Excel which input cell the list represents.

Setup for a 30-year, $250,000 mortgage:
B1: Loan Amount = 250000
B2: Rate = 6%
B3: Years = 30
B4: =PMT(B2/12, B3*12, -B1)     ' Monthly payment formula

Data Table layout:
      D          E
1                =B4    ← reference to the formula, top-right corner
2     5.0%
3     5.5%
4     6.0%
5     6.5%
6     7.0%

1. Select D1:E6
2. Data > What-If Analysis > Data Table
3. Column Input Cell: B2   (the rates in column D feed into B2)
4. OK

Excel fills column E with the payment at each rate:

Rate   Monthly Payment
5.0%   $1,342
5.5%   $1,419
6.0%   $1,499
6.5%   $1,580
7.0%   $1,663

Those numbers come directly from =PMT(rate/12, 360, -250000) at each rate — you can verify the 6% row yourself: =PMT(0.06/12,360,-250000) returns 1498.88.

How do I build a two-variable Data Table?

Put the formula reference in the single top-left corner cell of the block, one variable's test values down the column below it, the other variable's test values across the row to its right, then select the entire block including both lists and the corner formula.

Layout:
      D          E         F         G
1     =B4       15 yrs    20 yrs    30 yrs   ← Years across the top
2     5.0%
3     6.0%
4     7.0%
   ↑
Rates down the left

1. Select D1:G4
2. Data > What-If Analysis > Data Table
3. Row Input Cell: B3      (Years — matches the row of headers)
4. Column Input Cell: B2   (Rate — matches the column of headers)
5. OK

Excel fills the whole grid: payment for every rate/years combination in one operation, with the single formula in the corner driving every cell.

What is sensitivity analysis, and why use a Data Table for it?

Sensitivity analysis means testing how much a result changes when one input changes, holding everything else fixed — a Data Table does this exhaustively across a whole range of inputs at once, rather than you manually typing one input, reading the result, retyping, and repeating.

For a comparison of Data Tables against the other two What-If tools — Goal Seek (reverse-solve for one input) and Scenario Manager (save named sets of many inputs) — see What-If Analysis: 3 tools and when to use each. For more worked setups (loan payments, sales forecasts, investment returns), see Excel Data Tables for What-If Analysis.

Why does a Data Table slow down a large workbook?

Every cell inside a Data Table is really an array formula built on {=TABLE(row_input, column_input)}, and by default Excel recalculates every one of those cells on every workbook calculation — not just when the Data Table's own inputs change, but whenever anything anywhere in the workbook recalculates. A 20x20 two-variable table means 400 full recalculations of your underlying formula on every keystroke that triggers recalc.

Fix 1 — recalc setting scoped to Data Tables only:
Formulas tab > Calculation Options > Automatic Except for Data Tables
(Data Tables then only recalc on F9 / Calculate Now, everything else stays live)

Fix 2 — manual calculation for the whole workbook:
Formulas tab > Calculation Options > Manual
(press F9 to recalculate everything when you're ready)

Common mistakes with Data Tables

Most Data Table errors come from a misplaced formula reference or mixed-up input cells, both of which produce a table that looks fine but contains the wrong numbers.

• Referencing the wrong corner cell: the formula reference must sit exactly one row above and one column right of a one-variable list (or in the single top-left corner for two-variable) — misplacing it produces a table of identical or wrong values.
• Mixing up Row Input Cell and Column Input Cell: the row of test values maps to Row Input Cell, the column of test values maps to Column Input Cell — swapping them either errors or silently produces a transposed, wrong grid.
• Trying to edit one cell inside the results: every result cell is part of one array; Excel blocks editing or deleting a single cell with "Cannot change part of a data table."
• Not noticing the recalculation cost: a large table left on full Automatic recalc can make an otherwise-fast workbook feel sluggish on every edit, even edits unrelated to the table.