Excel Data Table Tutorial: One- and Two-Variable What-Ifs

To create an Excel Data Table, place a formula and test values in a small grid, select the entire grid, then choose Data > What-If Analysis > Data Table. Enter the model’s input cell in the Row input cell or Column input cell box; for a two-variable table, fill in both. Excel substitutes each test value into the model and records the formula’s result.

What an Excel Data Table Does

An Excel Data Table is a What-If Analysis tool for comparing the results of a formula under different input assumptions. Rather than changing a value, noting the result, and changing it again by hand, you provide a series of values and let Excel calculate the formula for each one. The output is a grid of results that can be inspected or used in a chart.

The name can cause confusion: a What-If Data Table is not the same thing as an Excel Table. An Excel Table is a formatted range of records, typically created with Insert > Table or Ctrl+T. It adds features such as filter buttons and structured references. A What-If Data Table is a calculation tool that temporarily tests one or two model inputs. The two features solve different problems.

Data Tables are useful for questions such as “How does revenue change at different prices?” or “What happens to a loan payment if both the rate and term change?” They are especially handy when a model already calculates an outcome from input cells and you want a compact sensitivity analysis.

Set Up the Model Before Building a Table

Start with a working model: one or more input cells and a formula cell that calculates the result you want to compare. The input cells are the values Excel will temporarily replace. The output formula must depend, directly or indirectly, on those inputs; otherwise, changing an input will not change the displayed result.

For a simple revenue example, assume B2 contains unit price, B3 contains units sold, and B4 is the revenue result. With price in B2 and units in B3, enter =B2*B3 in B4. If B2 is 20 and B3 is 100, B4 returns 2,000. This is an illustrative calculation, not a forecast: the result depends entirely on the assumptions entered.

Keep the model inputs easy to identify. Add labels such as Unit price, Units sold, and Revenue in nearby cells, and format prices and revenue as currency if appropriate. A Data Table’s input-cell boxes require cell references, so clear labels reduce the chance of assigning a scenario value to the wrong assumption.

Create a One-Variable Data Table in a Column

A column-oriented table is a convenient choice when you want to test a list of possible values for one model input. In this layout, scenario values go down the first column and the result formula sits at the top of the neighboring column.

Leave the model in B2:B4 as described above. In D2, enter a reference to the result cell. With revenue in B4, enter =B4 in D2.
Enter test prices in D3:D7, such as 15, 18, 20, 22, and 25. These are scenarios, not new permanent model inputs.
Select the full rectangular range C2:D7, with the formula in the upper-left corner and the scenario values in the column immediately to its right. For the classic layout described here, put the formula in C2 and values in C3:C7 instead: the formula must be in the top cell of the output column, with trial inputs in the adjacent first column. Select both columns together.

For clarity, use this standard arrangement: place =B4 in C2, put the five trial prices in C3:C7, and leave D2 as the formula’s output column heading area only if you are following a different orientation. The reliable column layout is: formula in D2, trial inputs in C3:C7, and select C2:D7. Then choose Data > What-If Analysis > Data Table. Leave Row input cell blank and set Column input cell to B2, the model’s price input. Select OK.

Excel fills D3:D7 with revenue results. At 15 per unit and 100 units, the first result is 1,500; at 25 per unit, the last is 2,500. The original value in B2 remains the model’s displayed input after the table calculation. A Data Table evaluates alternatives for the output grid; it does not turn each scenario into a permanent edit of the model.

Create a One-Variable Data Table Across a Row

A row-oriented layout works the same way, but the test values run across the worksheet. Put the result formula in the cell immediately to the left of the first test value, then put the input values along the row to its right. Place the results in the row below.

For example, put =B4 in F2 and enter test prices in G2:K2.
Select F2:K3, including the formula, all test values, and the blank result cells beneath the test values.
Choose Data > What-If Analysis > Data Table. Leave Column input cell blank and set Row input cell to B2, because the values across the top represent prices.

The results appear in G3:K3. Use the row layout when it is easier to compare scenarios from left to right or when you want to place several sets of results beneath a shared row of assumptions. The rule to remember is that values across the top use Row input cell, while values down the first column use Column input cell.

Build a Two-Variable Data Table

A two-variable table tests two model inputs at once. It has one set of scenarios across the top, another down the left side, and an output formula in the upper-left corner of the grid. Each interior cell shows the formula result for that pair of assumptions.

For the revenue model, suppose price values of 15, 20, and 25 go across the top, and unit counts of 80, 100, and 120 go down the left. Put a reference to the revenue result in the grid’s upper-left corner, such as =B4 in G2. Enter the three prices in H2:J2 and the three unit counts in G3:G5. Select G2:J5, then choose Data > What-If Analysis > Data Table. Set Row input cell to B2, because the top-row values are prices; set Column input cell to B3, because the first-column values are unit counts. Select OK.

The result for price 20 and 100 units is 2,000. At price 25 and 120 units, the result is 3,000. The top row and left column are assumptions, not results; the interior cells are the calculated outcomes. Label the row and column inputs clearly, especially when the table will be shared, so readers do not mistake units for prices.

How to Choose the Layout and Read Results

Use a one-variable table when only one assumption is changing and a two-variable table when two assumptions need to be compared together. A one-variable table can test any one input cell, such as price, units, or a percentage rate. A two-variable table is limited to two input cells, one represented across the row and one down the column. It does not automatically create a full simulation across many independent assumptions.

Use a column layout when the scenario list is long or you want labels and results in a vertical report.
Use a row layout when you have a short list of scenarios and want to compare results horizontally.
Use two variables when the interaction between two assumptions matters, such as price and volume.

Interpret the outputs as conditional results: they answer what the model returns if the specified inputs take those values. They do not establish which scenario is likely or whether the model’s assumptions are realistic. For a useful sensitivity table, choose plausible test values and check that the model formula includes all relevant relationships.

Common Problems and How to Fix Them

If every result is identical, first confirm that the model’s output formula actually depends on the input cell being substituted. Also check that the input cell is the correct one and that the scenario values are in the expected row or column. A row of values requires Row input cell; a column of values requires Column input cell. Reversing those assignments is a frequent setup error.

If Excel displays unexpected values, verify the position of the formula and the selection. The selected range must include the formula cell, the test values, and the cells where results should appear. In a two-variable table, the formula goes at the corner above and to the left of the output area, with the two sets of assumptions along the top and left edges.

Check that the model input cells are on the same worksheet as the Data Table. Also inspect calculation settings if results do not update as expected: in Excel for Windows, open Formulas > Calculation Options and check whether calculation is set to Automatic. In a large workbook, a Data Table can take time to recalculate because Excel evaluates the formula repeatedly for the scenario combinations.

Formatting can also mislead. A cell may show rounded currency while its underlying value has more decimal places. If a result seems off, increase displayed decimal places or inspect the formula bar before concluding that the table calculated incorrectly. Avoid typing over the cells Excel filled as table results; change the inputs or layout and recalculate instead.

Limitations, Performance, and Alternatives

Data Tables are best for compact, repeatable sensitivity checks. A one-variable table substitutes multiple values into one input cell; a two-variable table substitutes combinations into two input cells. If you need to compare complete sets of assumptions across many changing cells, a Data Table may not be the clearest tool. You could create separate model copies or build an explicit scenario summary with formulas, depending on how many assumptions and outputs need to be compared.

For a single target question—such as “What input makes this formula equal a particular result?”—Goal Seek may be a better fit. In Excel for Windows, find it at Data > What-If Analysis > Goal Seek. Scenario Manager, also under What-If Analysis, can save named sets of values for multiple input cells and show their outcomes, though it is less convenient for displaying a dense grid of combinations. These tools answer different questions: a Data Table maps many trial values to results, Goal Seek searches for one input value, and Scenario Manager stores defined sets of assumptions.

Excel for Microsoft 365 on Windows includes the Data Table command in the desktop application. Excel for Mac also provides What-If Analysis tools, though ribbon placement and dialog appearance can differ slightly by release. Excel for the web does not provide the desktop Data Table creation workflow; open the workbook in desktop Excel to create or edit one. Large tables can recalculate slowly, so start with a small number of scenarios and expand only as needed.

Frequently Asked Questions

Is an Excel Data Table the same as an Excel Table?
No. An Excel Table organizes records and adds features such as filtering; a What-If Data Table calculates formula results for trial input values.

Can an Excel Data Table use more than two variables?
No. A What-If Data Table supports one or two input cells. For more assumptions, consider Scenario Manager or a custom scenario model.

Why is the Data Table option unavailable?
Check that you are using desktop Excel rather than Excel for the web, and look under Data > What-If Analysis. Ribbon details can differ by platform and release. If the command is unavailable in your desktop version, confirm that the workbook is open in Excel rather than a viewer.

Does a Data Table change my original input values?
No. It calculates outputs for trial values without leaving those values in the model’s input cells. The original assumptions remain in place.

Can I use a Data Table with several output formulas?
A single Data Table is organized around a formula in its corner. If you need multiple outputs, you may be able to create additional tables using references to the other result cells, or use a scenario summary that presents all outputs together.

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.