Excel Data Table Row and Column Input Explained

How-to basics · Updated November 19, 2026

In an Excel two-variable data table, Row input cell is the model cell replaced by each test value across the top row, while Column input cell is the model cell replaced by each test value down the first column. To create one, put a formula linked to your model in the table’s upper-left corner, select the whole table range, then choose Data > What-If Analysis > Data Table and assign the two input cells.

What the row and column input cells mean

A data table is a What-If Analysis tool: Excel temporarily substitutes trial values into one or two cells in an existing model, recalculates a formula, and displays the results. The input cells are not the cells containing the scenario lists. They are the original worksheet cells that the model uses as its variables.

The names describe where the trial values sit in the data table, not where the corresponding model inputs sit on the worksheet. Values placed horizontally in the table’s top row map to the Row input cell. Values arranged vertically in the first column map to the Column input cell. This directional pairing is the detail that causes the most confusion.

A row input cell can be used on its own for a one-variable table arranged horizontally; a column input cell can be used alone for a vertical one-variable table. In a two-variable table, fill in both fields. Excel uses each combination of the horizontal and vertical test values to calculate the output.

Build a small example model

Suppose a shop wants to see how a change in selling price and expected sales volume affects a simple profit estimate. Put the unit selling price in B2, units sold in B3, fixed costs in B4, and calculated profit in B5. With those assumptions, enter this formula in B5:

With the unit price in B2, units sold in B3, and fixed costs in B4, the profit formula is =B2*B3-B4. If B2 is 20, B3 is 100, and B4 is 800, the result is 1,200: 20 multiplied by 100, less 800.

This is only an arithmetic example, not a forecast of actual business results. The data table does not create the model or decide which assumptions are realistic; it evaluates the formula using the values you supply.

Set up a two-variable data table

Put a link to the result formula in the upper-left corner of a blank table area. Across the row immediately to its right, enter the price test values 18, 20, and 22. Down the column immediately below the corner, enter the unit-volume values 80, 100, and 120. For example, use D2 as the corner, E2:G2 for the prices, and D3:D5 for the volumes.

With the profit result in B5 and the data table’s upper-left corner in D2, enter =B5 in D2. This cell tells Excel which result to recalculate for every combination. The corner formula should refer to the model’s output; do not put a separate calculation formula in every result cell.

  1. Select the complete rectangular range D2:G5, including the corner formula, horizontal price values, vertical volume values, and the empty result area.
  2. On Excel for Microsoft 365 for Windows, choose Data > What-If Analysis > Data Table.
  3. In the dialog, set Row input cell to B2, because the test values across the top are alternative prices for the model’s B2 cell.
  4. Set Column input cell to B3, because the values down the first column are alternative sales volumes for B3. Leave the fields in this order even if the input cells appear in a different order in your model.
  5. Choose OK. Excel fills the inside of the table with the recalculated profit results.

The results should be 640, 800, and 960 for a volume of 80 at prices of 18, 20, and 22; 1,000, 1,200, and 1,400 for a volume of 100; and 1,360, 1,600, and 1,840 for a volume of 120. For example, the center result uses price 20 and volume 100, so it matches the original profit of 1,200.

How Excel evaluates the table

Excel takes a value from the top row, temporarily places it in the Row input cell, takes a value from the first column, and temporarily places it in the Column input cell. It recalculates the formula referenced in the corner and records the result at the intersection of those scenario values. It repeats this for the combinations in the selected range.

The substitutions are temporary: a completed data table does not permanently replace the original assumptions in B2 or B3. The model keeps its original inputs after Excel calculates the table. The table is therefore a compact sensitivity analysis, rather than a collection of independent formulas that you need to write and maintain yourself.

Because the result comes from the corner formula, the same table can evaluate a complicated model, as long as the formula ultimately responds to the designated input cells. If a model’s output does not depend on one of the selected inputs, changing that input will not meaningfully change the results.

One-variable tables and input orientation

A one-variable data table changes only one model input. For a horizontal set of test values, put the output reference in the corner and scenarios across the top, then select the range and fill in Row input cell only. For a vertical list, put the scenarios down the first column and fill in Column input cell only. Leave the unused input field blank.

For example, a table could compare profit at prices 18, 20, and 22 while keeping units sold fixed at the current value in B3. The horizontal values belong to the Row input cell, B2; Excel varies price but does not substitute values into B3. Conversely, a vertical list of volumes uses B3 as the Column input cell and leaves price at its existing assumption.

When the layout is wrong, transpose the scenario list or change which field you use. Do not select Row input cell merely because the model input is located in a worksheet row, or Column input cell because it is in a worksheet column. The position of the scenario values inside the data-table layout determines the field.

How data tables compare with other what-if tools

Excel’s Scenario Manager stores sets of input values under scenario names, which is useful when you want to compare a few named cases such as Base, High, and Low. Goal Seek works in the opposite direction: you specify a desired formula result and Excel searches for one input value that would produce it. A data table instead shows the formula’s result across a grid of one or two input values.

Ordinary formulas can also calculate a sensitivity grid, but each cell needs a formula or a more involved setup. A data table is convenient when the goal is to test combinations against an existing model without rewriting its formula. It is limited to one or two variable input cells per table, so it is not a general-purpose simulation tool for many independent variables.

Data tables are also distinct from Excel Tables created with Insert > Table or Ctrl+T. Those tables organize lists of records, offer filters, and can use structured references. A What-If Analysis data table is a calculation feature; creating an Excel Table does not produce a sensitivity analysis.

Common setup problems and fixes

  • Results are identical across the grid. Check that the chosen input cells are the cells actually referenced by the model’s formula. Also check that the horizontal and vertical scenario values occupy the expected positions.
  • The wrong values seem to be changing. Confirm that the top-row scenarios map to Row input cell and first-column scenarios map to Column input cell. A common mistake is reversing the two dialog fields.
  • The output is blank or unexpected. Check that the upper-left corner contains a reference to the desired result formula, that the complete rectangular range was selected, and that the row and column scenario cells contain numbers or other values appropriate to the model.
  • The table does not update when assumptions change. Check Formulas > Calculation Options. Automatic is the usual setting; Automatic Except for Data Tables calculates formulas automatically but excludes data tables, which may need an explicit recalculation.
  • Calculation is slow. Each combination can trigger recalculation of the model. Large grids and complex workbooks may take longer, so limit the range to useful scenarios and consider the calculation setting if a workbook contains many tables.

A data table’s result cells are controlled as a group by the What-If Analysis calculation. If you need a different set of assumptions, edit the scenario values or the model inputs and recalculate the table rather than trying to build unrelated formulas over its result area. Save a copy before making broad workbook changes, especially when the model is important.

Version and platform notes

The instructions above are for Excel for Microsoft 365 on Windows. In Excel for Mac, data tables are available in desktop Excel; use the Data tab’s What-If Analysis and Data Table commands where shown, and follow the same row-versus-column mapping. Ribbon placement and dialog layout can vary somewhat with the Mac release.

Excel for the web may display a workbook containing data tables, but the desktop Excel app is the dependable choice for creating or editing What-If Analysis data tables. If the command is missing or unavailable, open the workbook in the desktop application and check that the workbook is editable rather than in a restricted or protected state.

Data tables can affect calculation speed because Excel evaluates the model repeatedly. In a workbook with many formulas, tables, or volatile calculations, a large scenario grid may make recalculation noticeably slower. The feature is best used for a purposeful range of cases, not an unnecessarily huge matrix.

Frequently Asked Questions

What is a row input cell in an Excel data table?
A row input cell is the original model input cell that Excel temporarily replaces with each scenario value listed across the top row of the data table. It is not necessarily located in a worksheet row.

What is a column input cell in an Excel data table?
A column input cell is the model input cell that Excel temporarily replaces with each value listed down the first column of the data table. Use it for a vertical scenario list or as the second variable in a two-variable table.

Can I leave the row or column input cell blank?
Yes. For a one-variable table, fill in only the field matching the direction of the test values and leave the other field blank. For a two-variable table, assign both fields.

Why does my Excel data table show the same answer in every cell?
The model may not depend on the input cell you selected, or the row and column fields may be reversed. Verify the model references, the table’s scenario layout, and the formula reference in the table’s upper-left corner.

Is an Excel data table the same as an Excel Table?
No. A What-If Analysis data table calculates results for test values. An Excel Table is a structured data range used to organize and analyze records; it does not automatically perform scenario calculations.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.