How to Use Data Tables in Excel

A Data Table is a built in what if tool that recalculates a formula across a grid of input values in one pass. You list the inputs you want to test, point the table at the formula you care about, and Excel fills in every result. In financial models it is the standard way to build a clean sensitivity table without copying a model dozens of times.

What a Data Table does and when to use it

A Data Table substitutes a series of values into one or two input cells and records what a target formula returns for each combination. A one variable table flexes a single input down a column or across a row. A two variable table flexes two inputs at once and fills a full grid.

Reach for it when you want to see how an output, such as NPV, IRR, or operating margin, responds to a range of assumptions. It keeps a single live model and reports the results, so you are not pasting twenty copies of the same calculation.

Step by step

Say B20 holds your model NPV, which depends on a discount rate in B2. You want NPV at rates from 8 to 12 percent.

1. List the input values you want to test in a column, for example 8% to 12% in A4:A8.
2. In the cell one row up and one column right of that list, here B3, enter a reference to the output formula: =B20. This is the corner cell.
3. Select the rectangle that covers both the input list and the corner cell row, here A3:B8.
4. Go to Data > What-If Analysis > Data Table.
5. Leave Row input cell blank and set Column input cell to B2, since the inputs run down a column.
6. Click OK; Excel writes each NPV result next to its rate.

The corner cell must reference the output formula, not retype it.

A model use case

For a two variable table, put one set of inputs down the left column and another across the top row, then place =B20 in the top left corner where they meet. A common pairing is revenue growth across the top and gross margin down the side, with EBITDA in the corner.

Select the whole grid including the corner and both input headers, open Data > What-If Analysis > Data Table, then set Row input cell to the growth assumption and Column input cell to the margin assumption.

• One variable: fill either a column or a row of inputs, not both.
• Two variable: inputs run down the left and across the top, output in the corner.
• Format the corner cell as text or hide it so the table reads cleanly.

Pitfalls and limits

Data Tables write results as a single array formula using TABLE, so you cannot edit or delete one cell of the output on its own. The corner cell must point to a formula elsewhere; typing the calculation directly in the corner will not work.

Large tables recalculate on every workbook change and can slow a file noticeably. If that happens, set calculation to Formulas > Calculation Options > Automatic Except for Data Tables and press F9 when you want a refresh. A Data Table also accepts only two inputs at most; for more variables use Scenario Manager or a switch driven model.