Data Table in Excel Modeling

The layout is rigid and tripping it up is the #1 beginner mistake. The top-left corner cell must contain a reference to the output you're sensitizing (e.g., =IRR_cell), not a label. The column input values run down the left side directly under that corner; the row input values run across the top to the right of the corner. You select the entire rectangle (including the corner and both input lists), go to Data > What-If Analysis > Data Table, and specify the 'Row input cell' (the model cell that the top values should be fed into) and the 'Column input cell' (the model cell the side values feed into). Excel then substitutes each pair into the live model and records the resulting output in each grid cell. A one-variable table uses only the row or only the column input.

A Data Table is an array formula ({=TABLE(row_input, col_input)}) that Excel runs by temporarily plugging each input value into your model's actual input cell, recalculating the whole workbook, and capturing the output — then moving to the next value. This is why the inputs you sensitize must be the genuine driver cells the model reads from, not intermediate calculations. It's also why large data tables can be slow: each cell triggers a full recalculation, so a 10x10 table recalculates the model 100 times.

Because data tables recalc on every workbook change, big models with several tables can become sluggish. The fix is Formulas > Calculation Options > Automatic Except for Data Tables, which leaves normal formulas live but only refreshes data tables on F9. Two other gotchas: the input cells referenced must be on the same worksheet as the data table (a long-standing Excel limitation — use a helper cell to bridge to another sheet), and if your model contains a circular reference with iterative calculation turned on, data tables can return slightly unstable values, so many modelers toggle the circularity off when finalizing sensitivity output.