Excel Data Tables: What They Do and How to Use Them

In Microsoft Excel, a Data Table is a What-If Analysis tool that recalculates a formula for different values in one or two input cells. If you mean a formatted table of rows and columns, that is a different Excel feature: an Excel Table organizes data, while a What-If Data Table compares outcomes.

What an Excel Data Table Is

A What-If Data Table lets you explore how changing an input affects a formula’s result. Instead of replacing an input, noting the answer, and repeating the process manually, you arrange possible input values in a row or column and let Excel calculate the corresponding results.

For example, a workbook might calculate the area of a rectangle from its length and width. A Data Table can show the area for several lengths, several widths, or combinations of both. The original formula remains in the workbook, and Excel temporarily substitutes each listed value into the model’s input cell or cells to calculate the outputs.

Data Tables are part of Excel’s What-If Analysis tools, alongside Goal Seek and Scenario Manager. They are useful for comparing a planned range of possibilities, but they do not tell you which input is best or find an answer that meets a target. Those are different kinds of analysis.

How One-Variable Data Tables Work

A one-variable Data Table changes one input cell at a time. The input values can be arranged vertically in a column or horizontally in a row. The formula being evaluated must refer, directly or through other formulas, to the input cell that Excel will substitute.

Here is a simple example that converts study sessions into total minutes. Assume B2 contains the number of sessions and B3 calculates the total time. With the session count in B2, the formula in B3 is =B2*45. The result is 90 when B2 contains 2, because two sessions of 45 minutes equal 90 minutes.

To compare one through five sessions, put a reference to the result formula in B5, enter 1, 2, 3, 4, and 5 in A6:A10, and leave A5 blank. Select A5:B10, then open Data > What-If Analysis > Data Table. Since the test values are in a column, set Column input cell to B2 and leave Row input cell blank. Excel returns 45, 90, 135, 180, and 225 in B6:B10. The input cell is B2—not the result cell B3—because B2 is what the scenarios vary.

How to Create a Data Table in Excel

For a one-variable table, first build and check the formula you want to evaluate. Then prepare the test values and a reference to the result formula in the correct arrangement. This is important: Excel uses the position of the values to determine whether it should substitute them into the model’s row input cell or column input cell.

Identify the model’s input cell and the formula cell that calculates the outcome.
Enter the possible input values in a single row or column next to the area where the results will appear.
Place a reference to the outcome formula at the corner of the results area. For a vertical list, put the reference one cell above and one cell to the right of the first input value.
Select the whole layout, including the input values, formula reference, and empty result cells.
Choose Data > What-If Analysis > Data Table. In the dialog, fill in Row input cell or Column input cell to match where the test values sit.
Select OK, then check a result against a simple case you can calculate independently.

In Excel for Microsoft 365 on Windows, the command is on the Data tab under What-If Analysis. Excel for Mac has the What-If Analysis command on the Data tab as well; ribbon layout and wording can vary slightly by release. The Data Table dialog uses the same row-input and column-input concepts. Excel for the web may not provide the desktop authoring workflow, so use the desktop app to create or modify a What-If Data Table if the command is unavailable.

How Two-Variable Data Tables Work

A two-variable Data Table tests combinations of values for two input cells. One set of values goes across the top row; the other set goes down the first column. The formula reference belongs in the cell at the top-left corner of the grid, where the two sets of headings meet.

For a rectangle example, assume B2 is length, B3 is width, and B4 calculates area. With length in B2 and width in B3, the formula in B4 is =B2*B3. The result is 12 when the inputs are 4 and 3. Put a reference to B4 in D5, widths 2 through 6 in E5:I5, and lengths 1 through 5 in D6:D10. Select D5:I10 and choose Data > What-If Analysis > Data Table. Set Row input cell to B3, because widths are across a row, and Column input cell to B2, because lengths are down a column. The first result, in E6, is 2: length 1 multiplied by width 2. The table fills with the areas for every combination.

Keep the orientation straight: the values across the top replace the Row input cell, and values down the left side replace the Column input cell. The labels do not have to be the input cell addresses; they are simply candidate values. If a result looks transposed or implausible, confirm both the table layout and the two input-cell selections before changing the formula.

Reading and Formatting the Results

Each result represents the formula’s output for the corresponding input value or pair of values. In the rectangle example, the result at the intersection of length 3 and width 5 is an area of 15. Reading the row and column headings is therefore essential; the output grid alone does not explain which scenario produced a number.

You can add clear labels, units, and a descriptive title around the grid. Apply number formatting to the result cells when it helps readability, but avoid changing the input values into text if the model expects numbers. A Data Table reports the formula result; it does not automatically add explanations, identify a preferred scenario, or create a chart. You can chart the results separately if a visual comparison would help, taking care to select the correct headings and values.

Data Table results are linked to the underlying model and are recalculated when Excel recalculates the workbook. If you copy the grid elsewhere, check whether the copied cells still refer to the intended model. For important work, keep the input cells, formula, and scenario grid clearly labeled so another person can reproduce the analysis.

What-If Data Tables Versus Excel Tables

The shared name causes frequent confusion. A What-If Data Table is an analysis tool for testing formula outcomes. An Excel Table is a structured range of records, such as a list of products, dates, or survey responses. Excel Tables support features such as header filters, automatic expansion when rows are added, and structured references in formulas.

To create a regular Excel Table, select a data range and choose Insert > Table, then confirm whether the range has headers. On Windows, Ctrl+T is a common shortcut; on Mac, the keyboard shortcut can depend on the Excel version and keyboard settings, so the ribbon command is a dependable alternative. You can also use Home > Format as Table to apply a table style. These commands do not create a What-If Data Table, and the Data > What-If Analysis > Data Table command does not turn a record list into a structured Excel Table.

Use an Excel Table when you want to manage and analyze ordinary tabular data. Use a What-If Data Table when you already have a formula model and want a grid of results for alternate input values. The two features can appear in the same workbook, but they solve different problems.

Limitations and Common Problems

A What-If Data Table is designed for one or two changing inputs. It is not a general-purpose scenario manager for dozens of independent assumptions, and it does not store a separate copy of the workbook for every case. If you need more complex scenario comparisons, consider Scenario Manager or a purpose-built model layout.

No results appear: Confirm that the selected range includes the formula reference and all input values, and that the correct input cell is set in the dialog.
Every result is the same: Check that the formula depends on the input cell being varied. A disconnected input will not change the result.
Results are in the wrong direction: Verify that row values map to Row input cell and column values map to Column input cell.
The workbook recalculates slowly: A Data Table can require many formula recalculations, especially when the workbook contains complex dependent formulas or a large grid. Reduce the number of scenarios or simplify the calculation model where practical.
A result cell cannot be edited alone: Data Table outputs are produced as a group. Change the input layout or rebuild the table rather than treating one output as an ordinary independent formula cell.

Calculation settings can also affect when outputs refresh. If the workbook is set to manual calculation, results may not update immediately after a model change. Check Formulas > Calculation Options and select Automatic if that is appropriate for the workbook. Changing calculation mode can affect the entire workbook, so understand the setting before relying on displayed results.

Choosing Between Data Tables and Other What-If Tools

Choose a Data Table when you want to see a compact grid of outcomes for a defined set of one or two inputs. For example, it is well suited to comparing rectangle areas across lengths and widths or testing how a formula changes across different quantities. Its strength is the side-by-side comparison, not finding an optimum or recording a narrative about each scenario.

Use Goal Seek when you know the desired result and want Excel to find a single input value that makes a formula reach it. Use Scenario Manager when you want to save and switch among named sets of input values. For optimization with constraints, Solver may be more suitable if it is available and enabled in your Excel installation. These tools answer different questions, so select based on whether you are comparing outcomes, targeting a result, saving named cases, or optimizing.

Before choosing, ask how many inputs need to change, whether the possible values are known in advance, and how the results will be communicated. A one- or two-variable grid is easy to inspect. A large collection of assumptions may be clearer as a separate scenario table or a more specialized model, rather than an oversized Data Table.

Frequently Asked Questions

How do I find Data Table in Excel?
In Excel for Microsoft 365 on Windows, go to Data > What-If Analysis > Data Table. On Mac, look for What-If Analysis on the Data tab. If the option is not available in your Excel for the web session, open the workbook in the desktop app.

Is an Excel Data Table the same as a table made with Ctrl+T?
No. Ctrl+T creates a structured Excel Table from a range of records in Windows Excel. A What-If Data Table is created through Data > What-If Analysis and evaluates a formula for alternate input values.

Can a Data Table change more than two variables?
A What-If Data Table directly varies one or two input cells. For additional variables, consider Scenario Manager, separate model cases, or another analysis approach suited to the workbook.

Why does my Data Table show the same answer in every cell?
The formula may not depend on the input cell you selected, or the row and column input cells may be assigned incorrectly. Check the model’s references and confirm the table’s orientation in the Data Table dialog.

Can I type over one Data Table result?
Data Table outputs are generated together from the scenario layout, so an individual result is not meant to be an independent entry. Edit the input values or model, or create a separate calculation for a one-off result.

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.