Excel Data Table Setup: What-If Analysis and Tables

To set up an Excel what-if data table, create a formula that refers to your model, arrange possible input values beside or above it, select the whole grid, then choose Data > What-If Analysis > Data Table and identify the input cell or cells. If you meant a regular Excel Table that sorts and filters a list, select the data and choose Insert > Table; these are different features.

Two Excel features called data tables

“Data table” can refer to two things in Excel. A What-If Analysis data table calculates a formula repeatedly using different values for one or two inputs. It is useful for comparing outcomes without rewriting the formula or making a separate copy of the model for every scenario. A regular Excel Table is a formatted, expandable data range with filter buttons and features such as structured references.

The distinction matters because the setup and purpose differ. Use a what-if table when you want to see how a result changes as assumptions change—for example, revenue at different prices and sales volumes. Use an Excel Table when you want to manage rows of records, such as a sales log, inventory list, or customer register. The steps below cover both, with the what-if feature first because it is the one designed for scenario analysis.

How a what-if data table works

A what-if table temporarily substitutes each test value into an input cell in your worksheet model, recalculates the formula, and records the result in the table. Your model needs a formula that depends on the input cell. The table does not create a new model or forecast on its own; it reports the results of the formula you already built.

For a concrete example, suppose a simple revenue model has a unit price in B2, units sold in B3, and revenue in B4. With the unit price in B2 and units sold in B3, enter =B2*B3 in B4. If B2 contains 25 and B3 contains 100, the model returns 2,500. A data table can then test other prices, sales volumes, or both while leaving the model’s original assumptions in place.

Data tables are a type of What-If Analysis tool, alongside Goal Seek and Scenario Manager. They are best for a compact comparison of one or two changing inputs. They are not a substitute for a model with many independent assumptions, and they do not automatically determine which scenario is most likely.

Set up a one-variable data table

A one-variable table tests multiple values for one model input. The values can be arranged down a column or across a row. A vertical arrangement is often easiest to read. In the revenue example, test different prices while keeping units sold at 100:

Keep the model in B2:B4: price in B2, units in B3, and the revenue formula in B4.
In D3:D7, enter the trial prices 10, 15, 20, 25, and 30.
In E2, enter a reference to the model result. With revenue in B4, use =B4.
Select D2:E7, including the blank cell D2, the result reference in E2, and all trial values.
Choose Data > What-If Analysis > Data Table. Leave Row input cell blank and set Column input cell to B2, because the trial values are in a column and replace the model’s price input.
Select OK. Excel fills the result cells in E3:E7.

The outputs are 1,000, 1,500, 2,000, 2,500, and 3,000, respectively: each price is multiplied by the fixed 100 units. The formula reference belongs above the output column, while the trial inputs sit below the top-left corner. If you instead list trial values across a row, place the result reference one row below that row and use the Row input cell field.

Set up a two-variable data table

A two-variable table tests combinations of two inputs, such as price and units sold. Put one set of trial values across the top row and the other set down the first column. Place a reference to the model’s result in the top-left corner of the selected grid.

For the same revenue model, enter =B4 in D2. Put prices 20, 25, and 30 in E2:G2, and put sales volumes 80, 100, and 120 in D3:D5. Select D2:G5, then open Data > What-If Analysis > Data Table. Set Row input cell to B2, since the price trials run across the top, and Column input cell to B3, since the volume trials run down the first column. Select OK.

The result for price 20 and 80 units is 1,600; for price 25 and 100 units it is 2,500; and for price 30 and 120 units it is 3,600. The full grid also shows mixed combinations, such as 20 × 120 = 2,400 and 30 × 80 = 2,400. A two-variable table can reveal that different combinations produce the same result, though the table itself does not explain which combination is realistic.

Arrange the worksheet and choose input cells

Good layout prevents the most common setup errors. Keep the model’s input cells separate from the scenario grid, and make the relationship between each trial value and its input cell obvious. Use labels next to the table—such as “Price” and “Revenue”—but do not put text into cells that Excel needs to treat as numeric inputs.

For a vertical one-variable table, place the formula reference at the top of the output column and trial values beneath the top-left corner.
For a horizontal one-variable table, put trial values across the top and the formula reference directly below the first value.
For a two-variable table, put one input’s values across the top, the other input’s values down the left, and the formula reference at their intersection.
Use the actual model input cell in the Data Table dialog, not the formula result cell or a nearby label cell.
Keep the reference formula linked to the intended output. A typed number will not recalculate as the model changes.

The row and column input fields describe where Excel should substitute the test values in the underlying model; they do not mean “the row containing the result” or “the column containing the answer.” In a vertical one-variable table, the trial values are in a column, so use Column input cell. In a horizontal table, use Row input cell.

Recalculation, formatting, and practical limits

What-if data tables are different from ordinary formulas: Excel calculates a set of scenarios by repeatedly substituting values into the model’s input cell or cells. They can be slower than a normal formula when a workbook contains large tables, complicated calculations, or many volatile formulas. Start with a small grid, confirm the values make sense, and expand it only as needed.

Format the outputs to suit the result—currency, number, percentage, or another appropriate format—using Home > Number. Number formatting changes how values appear, not the underlying calculation. Add clear row and column headings outside the cells used for the input grid so readers can identify what the scenarios represent.

If the table does not update as expected, check that calculation is not set to Manual. In Excel for Microsoft 365 on Windows, review Formulas > Calculation Options. Automatic is the usual setting for a workbook that should recalculate when inputs change. Excel for Mac has the calculation options on the Formulas tab as well, though the ribbon layout can vary by window size and version.

Data tables are limited to one or two changing input cells at a time. For more variables, you can create separate tables, use Scenario Manager, or build a dedicated scenario model with formulas. A data table also shows calculated outcomes rather than probabilities, recommendations, or a guarantee that an assumption will occur.

Set up a regular Excel Table

If your goal is to organize a list of records, create a regular Excel Table instead. Put one header in the first row of each column, avoid blank header cells, and keep each row as one record. Select a cell in the range, choose Insert > Table, confirm the range, and check My table has headers if the first row contains field names. Select OK.

Excel adds filter controls to the header row and applies a table style. New rows added directly below the table are generally incorporated into it, and formulas or formatting can extend with the data. To rename the table, select a cell in it and use the Table Design tab’s Table Name box. A descriptive name such as SalesData is easier to use than the default Table1.

Tables also support structured references. For example, a formula can refer to a column by its header rather than by a cell range. This is useful in a growing dataset because the reference can include new table rows. A regular Excel Table does not run what-if scenarios; for that, use Data > What-If Analysis > Data Table.

On Windows, Ctrl+T opens the Create Table dialog when a data range is selected. On Mac, use Command+T. The ribbon route Insert > Table works on both platforms. In Excel for the web, Insert > Table is available, but the desktop What-If Analysis data table feature and its dialog may not be available in the same way; use the Windows or Mac desktop app when you need to create or edit a what-if data table.

Common setup problems

Blank or unexpected results: Check that the top-left formula reference points to the intended model output and that the input cells affect that output.
Wrong values in a vertical table: Make sure the trial values are in the first column of the selected range and that Column input cell points to the model assumption being tested.
Only one result appears: Select the whole grid, including the formula reference and all trial values, before opening the Data Table dialog.
Values look unchanged: Check for Manual calculation, and make sure the table’s formula depends on the selected input cell.
Excel reports a problem with the range: Recheck the table’s arrangement and ensure the row and column input cells are individual cells in the same worksheet as the model.

Another common misconception is that a what-if data table is simply a formatted Excel Table. It is not: the two features have different purposes and separate commands. Likewise, a data table does not replace the original input values permanently. It uses each scenario value to calculate a result and displays the outcomes in the grid.

Frequently Asked Questions

Where is Data Table in Excel?
In Excel for Microsoft 365 on Windows, choose Data > What-If Analysis > Data Table. The command is available when you are setting up a what-if analysis, not when creating a regular formatted Excel Table.

Can an Excel data table use more than two variables?
No. A What-If Analysis data table supports one or two input cells. To evaluate more changing assumptions, use separate tables, Scenario Manager, or a worksheet model designed to calculate those combinations.

Why is my data table showing the same result in every cell?
Check that the formula reference points to the right result, that the chosen row or column input cell is the correct model assumption, and that changing that assumption actually changes the formula result. Also check workbook calculation settings.

What is the difference between a data table and an Excel Table?
A what-if data table compares formula results under trial input values. An Excel Table organizes a list of records with headers, filters, and expandable ranges. Use Data > What-If Analysis for the first and Insert > Table for the second.

Can I create a what-if data table in Excel for the web?
Excel for the web can work with many workbook features, but the desktop What-If Analysis data table workflow may not be available or behave identically there. Use Excel for Microsoft 365 on Windows or Mac for the full desktop setup steps.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.