Goal Seek answers "what input hits this target?" A Data Table answers the broader question of what the output looks like across a whole range of inputs. It re-runs your model once per input value and lays the results out as a live block, so the whole block recalculates when the model changes. It's the most under-used what-if tool in Excel. Once it clicks, you'll build them constantly.
Nobody works out the corner formula on their own. A Data Table is one special array operation Excel performs on a region you lay out in a rigid shape. There are no copied formulas in it. The rule that trips everyone the first time is about placement. The formula, or a reference to the output cell, has to go in a specific corner of the table, and the input values fan out from it.
How does a one-variable data table work?
One-variable Data Table (column-oriented). Put the input values you want to test down a column. In the cell one row up and one column to the right of the top input value, put a reference to the model's output, such as =B10 if B10 is your monthly payment. Select the rectangle spanning the input column and the formula's column, and go to Data ▸ What-If Analysis ▸ Data Table. Your inputs run down a column, so fill only the Column input cell. That's the model input each value in your column should be substituted into. Leave Row input cell blank. Excel walks down your column, plugs each value into that input cell, recomputes the model, and writes the output beside it.
Try it on a 30-year, $500,000 mortgage payment, sensitised to the interest rate. Your model cell holds =PMT(rate/12, 360, -500000). Down a column you list 6%, 7%, 8%. The corner formula references the payment cell, and the Column input cell points at the rate input. Excel returns monthly payments of $2,997.75, $3,326.51 and $3,668.82. That's three full model runs from one click.
Advertisement
How does a two-variable data table work?
Two-variable Data Table (the grid). Now you vary two inputs at once. Lay one input's values across the top row and the other's down the left column. The corner where the row and column meet, the top-left cell of the table, holds the reference to the model output. Select the whole grid (corner + both header strips + the empty body) and open the Data Table dialog. This time you fill both boxes. Getting them the right way round is the crux, and the common-mistakes section comes back to it:
- Row input cell = the model input that the values across the top row should feed.
- Column input cell = the model input that the values down the left column should feed.
Read it as row values to the row input cell, column values to the column input cell. Excel then computes the output for every combination, a full Cartesian grid of what-ifs, and fills the body. Change any model assumption elsewhere and the entire grid re-runs. That grid is the DCF sensitivity table you'll build next.