Excel Two-Variable Data Tables: How What-If Analysis Works

Formulas & functions · Updated November 21, 2026

In Excel, a two-variable data table shows how one formula’s result changes across combinations of two input values. Put the formula in the table’s top-left cell, the first set of test values across the top, and the second set down the left; then use Data > What-If Analysis > Data Table to assign the two model input cells.

What a two-variable data table does

A two-variable data table is a What-If Analysis tool for comparing many possible outcomes without changing your model inputs by hand. Excel takes each pair of test values, temporarily substitutes them into two specified input cells, recalculates the workbook, and records the selected formula’s result. The original input values are restored after the calculation.

Despite its name, the table does not need to display two outputs. It varies two inputs and records one formula result for each combination. That result might be revenue, a payment, a margin, a date, or another value calculated by your workbook. If you want to test one input at a time, use a one-variable data table instead.

Data tables are useful when the inputs interact or when you want to compare a grid of assumptions. For example, a sales forecast might test several prices against several sales volumes. A loan worksheet could compare different interest rates and terms against one payment formula. The result is a compact sensitivity analysis, not a prediction about which assumptions will occur.

How the two input cells and grid work

The table has a formula cell at its upper-left corner, scenario values along the top row, and the other scenario values down the first column. The output area occupies the cells where those rows and columns intersect. In the Data Table dialog, the Row input cell is the model cell replaced by values from the top row; the Column input cell is the model cell replaced by values down the first column.

The words row and column describe where the test values sit in the table, not the orientation of the model inputs. If prices are across the top, select the model’s price input as Row input cell. If quantities are down the left, select the model’s quantity input as Column input cell. Picking the opposite cells is a frequent reason results appear transposed or nonsensical.

The two model input cells need to be genuine inputs that the result formula depends on, directly or through other formulas. Excel substitutes each trial value into those cells while evaluating the formula. The table does not create a separate copy of the model, nor does it permanently replace the assumptions in the input cells.

Build a simple revenue example

Suppose a small forecast has unit price in B2, units sold in B3, and revenue in B4. The model layout is B2 for price and B3 for units; in B4, calculate revenue with =B2*B3. If B2 is 20 and B3 is 100, the formula returns 2,000.

Set up a scenario grid with the output formula link in E2, prices 18, 20, 22, and 24 in F2:I2, and quantities 80, 100, 120, and 140 in E3:E6. Given that layout, enter =B4 in E2 so the table knows which result to record. The range E2:I6 now contains the formula cell, the row and column assumptions, and the blank output cells.

Select E2:I6, then choose Data > What-If Analysis > Data Table. In Row input cell, select B2, because the price assumptions are across the top. In Column input cell, select B3, because the quantity assumptions are down the left. Confirm the dialog. Excel fills the 4-by-4 output area with the revenue for each price-and-quantity pair.

For example, the intersection of price 22 and quantity 120 returns 2,640, because 22 multiplied by 120 is 2,640. At price 20 and quantity 100, the output is 2,000. At price 24 and quantity 140, it is 3,360. These results make it easy to spot how the output changes as either assumption moves.

Steps in Excel for Microsoft 365 on Windows

  1. Build and check the model first. Make sure the result formula responds when you change either input cell manually, then restore the intended starting values.
  2. Choose an unused area for the table. Put a direct reference to the desired result formula in the top-left corner, test values for the first input across the top, and test values for the second input down the left.
  3. Select the entire rectangular range, including the formula cell and both sets of test values. Do not select only the blank output cells.
  4. On the ribbon, choose Data > What-If Analysis > Data Table. In the dialog, identify the model input cell for the top-row values as Row input cell and the model input cell for the left-column values as Column input cell.
  5. Choose OK. Check a few intersections by calculating them manually or temporarily trying the same pair in the model. Format the resulting cells as needed for readability.

Keep the scenario headings as numeric values if you want Excel to use them as numerical inputs. Labels such as Low, Base, and High are useful for explaining assumptions, but they are not substitutes for numeric test values in the data table. You can add descriptive labels in nearby cells or use a separate note.

Read and present the results carefully

Each interior cell represents one combination of the row and column assumptions. Read the row heading and column heading together; a value has no useful interpretation without both. If the table shows a steady increase from left to right, the output rises as the top-row variable increases, assuming other model logic remains unchanged. A change down a column shows the effect of the left-side variable at a fixed row assumption.

Formatting can make patterns easier to see. Apply a number format appropriate to the result, such as currency for revenue or percentages for margins. Conditional formatting can highlight high and low values, but it does not change the calculations. Add a clear title and units to the input headings, especially if the workbook will be read by someone who did not build it.

Be cautious about treating a grid as a forecast. A table answers conditional questions: if the model inputs were these values, what would the formula return? It does not establish that the assumptions are realistic, account for uncertainty automatically, or explain why a result changes. The underlying formulas and assumptions still determine the quality of the analysis.

Limits and recalculation behavior

A two-variable data table tests only two changing input cells at a time and returns one result formula. If you need to test three or more changing assumptions simultaneously, you may need separate tables, a redesigned model, or a different analysis method. You can create multiple data tables for different output formulas, but each table’s results depend on its specified inputs and calculation setup.

Data tables are a special What-If Analysis feature, not ordinary formulas copied into every output cell. This distinction matters when editing, copying, or troubleshooting them. Large tables can slow recalculation because Excel evaluates the model repeatedly for the combinations. A grid with 20 values across and 30 down requires 600 combinations, so complex workbooks may take noticeably longer to update.

If recalculation becomes slow, Excel for Microsoft 365 on Windows provides Formulas > Calculation Options > Automatic Except for Data Tables. This keeps ordinary formulas in automatic calculation while excluding data tables until recalculation is requested. Use that option deliberately: table results may be out of date until recalculated, so check them before sharing or relying on the workbook.

Excel for Mac has the same core data-table concept and a Data Table command in the Data tab’s What-If Analysis tools, though ribbon layout can vary by release. Excel for the web can display workbooks containing data tables, but creating or editing What-If Analysis data tables requires the desktop Excel application. If a command is missing, open the file in the desktop version and confirm that it is an Excel workbook.

Common problems and how to fix them

  • All outputs are identical: Confirm that the selected Row input cell and Column input cell are the same cells the model actually uses. Also verify that the result formula depends on both assumptions.
  • Results look swapped: Check the orientation. The input cell for values across the top belongs in Row input cell; the input for values down the left belongs in Column input cell.
  • The table returns zero or an error: Test the output formula directly and inspect the model for errors, missing values, or formulas that do not respond to the selected inputs. A data table cannot repair a problem in the underlying model.
  • The formula seems not to copy normally: That is expected. The output range is populated by the data table operation rather than by dragging a conventional formula. Edit the table through its range and setup rather than assuming each output cell is independent.
  • Values do not update: Check the workbook’s calculation mode and recalculate. If Automatic Except for Data Tables is enabled, the table may need an explicit recalculation. Also make sure the scenario values are actual numeric values, not numbers stored as text.
  • The dialog uses the wrong area: Re-select the complete table range, starting at the formula cell and including the top-row values, left-column values, and output area. Then reopen the Data Table dialog.

When to choose another What-If tool

Use a two-variable data table when you want a rectangular comparison of two inputs against one result. A one-variable data table is simpler when only one assumption changes. Goal Seek works in the opposite direction: it finds an input value that makes a formula reach a target, rather than displaying many combinations. For example, it can find the sales volume needed to reach a particular revenue, provided the model is set up for that calculation.

Scenario Manager is suited to saving and switching among named sets of assumptions, such as pessimistic, expected, and optimistic cases. It is often easier to explain when each case changes several inputs together, though it does not provide the same compact two-dimensional grid. Solver is intended for optimization problems with an objective and constraints, not simply displaying a sensitivity table. Choose the tool based on the question: compare combinations, reach a target, compare named cases, or optimize.

Frequently Asked Questions

How do I make a two-variable data table in Excel?
Put a result formula link in the upper-left cell, test values across the top and down the left, select the full range, then choose Data > What-If Analysis > Data Table. Assign the top-row model input as Row input cell and the left-column model input as Column input cell.

Why is my Excel data table showing the same answer everywhere?
The model may not depend on the input cells you assigned, or the row and column input cells may be reversed. Check that changing each model input manually affects the result, then confirm the two references in the dialog.

Can a two-variable data table show two different results?
One data table records one formula result for each input combination. To analyze a second result, make another table using a link to that result formula, or restructure the workbook to report the outputs separately.

Can I use text values in a two-variable data table?
Data tables are generally designed to substitute values into model input cells, most commonly numeric assumptions. Use numeric scenario values in the grid; add descriptive text labels separately rather than relying on words as the test values.

Can I create a two-variable data table in Excel for the web?
Excel for the web can show a workbook that contains a data table, but use desktop Excel for Microsoft 365 on Windows or Excel for Mac to create or edit the What-If Analysis table.

Related

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.