Excel Data Table Analysis: Compare What-If Scenarios

Formulas & functions · Updated November 21, 2026

Excel data table analysis is a What-If Analysis tool that recalculates a formula across a range of one or two changing inputs. In Excel for Microsoft 365 on Windows, open Data > What-If Analysis > Data Table; arrange the model formula and test values in a grid, then assign the relevant input cell or cells.

What an Excel data table does

A What-If Analysis data table shows how changing one or two input values affects a formula. You supply a working model—for example, a formula for revenue—and a set of possible values for the model’s input cells. Excel temporarily substitutes each value, recalculates the formula, and displays the results together in a table.

The feature is useful when you want to compare a range of possibilities without changing the original inputs one by one. It is a sensitivity-analysis tool: it helps reveal which assumptions have a large effect on a result and which have little effect. It does not predict which assumption will occur, explain why a result changed, or select the best option for you.

Here, “data table” means the What-If Analysis feature, not an Excel Table created with Insert > Table or Ctrl+T. Excel Tables organize and manage lists of records; What-If Analysis data tables run calculations against alternative input values. The two features have different purposes.

How What-If Analysis data tables work

A data table is connected to an existing formula in your worksheet. The input cell is a cell in the model that Excel can temporarily replace with each test value. For example, if a revenue formula depends on price and quantity, those two assumptions can be the input cells. The formula itself remains the calculation that produces the result.

A one-variable data table tests different values for one input cell. A two-variable data table tests combinations of values for two input cells. In both cases, Excel writes calculated results into the table’s result area. It uses the same formula and model each time, so a mistake in the underlying model will also affect every result.

Unlike a regular formula copied down a column, a What-If Analysis data table has special calculation behavior. It is designed to evaluate a set of input substitutions, rather than to create a separate, ordinary formula in each result cell. This makes it convenient for scenario grids, although very large tables can take longer to recalculate.

Build a one-variable data table

Suppose a simple sales model has a price of 12 in B2 and a quantity of 100 in B3. With price in B2, quantity in B3, and revenue in B4, enter the following formula in B4:

=B2*B3

The result is 1,200 because 12 multiplied by 100 equals 1,200. To see how revenue changes at different prices, make a vertical test table. Put a reference to the model result in E2, and put the prices 10, 12, and 14 in E3:E5. The assumed layout is price in B2, quantity in B3, revenue in B4, the result reference in E2, and test prices in E3:E5. In E2, enter:

=B4

Select E2:F5, then choose Data > What-If Analysis > Data Table. Leave Row input cell blank and set Column input cell to B2. Click OK. Excel substitutes each value from column E into B2, recalculates the model, and places the outcomes in F3:F5: 1,000 at price 10, 1,200 at price 12, and 1,400 at price 14. The original price in B2 is restored after the calculation.

The test values go in a column, so the model’s input cell is specified as the Column input cell. If you instead list test values across a row, use the Row input cell. The labels and result formula must be arranged correctly before opening the Data Table dialog; selecting only the test values will not create the intended table.

Build a two-variable data table

A two-variable table compares combinations of two inputs at once. Continue with the same sales model: price is in B2, quantity is in B3, and revenue is in B4. Put a reference to revenue in E2. Across F2:H2, enter quantities of 80, 100, and 120. Down E3:E5, enter prices of 10, 12, and 14. The cell at the top-left of the result grid, E2, contains the formula reference; the row above the results holds quantity tests, and the column to their left holds price tests.

Select E2:H5 and choose Data > What-If Analysis > Data Table. Set Row input cell to B3 because the test quantities run horizontally, and set Column input cell to B2 because the test prices run vertically. Click OK. At price 12 and quantity 100, the result is 1,200; at price 14 and quantity 120, it is 1,680. These figures follow the model’s multiplication formula and illustrate how the two assumptions combine.

Keep the row and column roles straight: the Row input cell receives the values across the top, while the Column input cell receives the values down the left side. A reversed assignment can still produce numbers, but the values will be substituted into the wrong model inputs, making the table misleading.

When to use a one- or two-variable table

Choose a one-variable table when you want to isolate the effect of one assumption, such as price, labor hours, a discount rate, or a unit cost. It is easier to read and can test many values for one input. Choose a two-variable table when the result depends on two assumptions and the interaction matters, such as price and quantity or hours and hourly rate.

Two-variable tables display a grid of combinations, so they work best when both input ranges are short enough to interpret. A grid with dozens of values in each direction can become crowded and slow to recalculate. If you need to vary three or more assumptions at once, a data table is not the right tool; use separate scenarios, a structured set of formulas, or another analysis method.

  • Use a one-variable table to answer: How does the result change as this one input changes?
  • Use a two-variable table to answer: What happens across these combinations of two inputs?
  • Use a different method when you need named scenarios, a target value, or more than two changing inputs.

Read results carefully and understand the limits

A data table reports the formula’s output for the exact input combinations you provide. It does not automatically interpolate between tested values, establish that a result is likely, or account for uncertainty beyond the assumptions already built into the model. If a table tests prices of 10, 12, and 14, for example, it does not show what happens at 13 unless you add that test value.

Check that the model is complete before trusting the grid. A revenue formula that multiplies price by quantity does not include costs, taxes, discounts, capacity constraints, or other factors unless you have added them to the model. The table can calculate accurately and still give an incomplete picture if the formula omits something important.

Also consider the scale and units of the inputs. Make clear whether a value represents dollars, hours, a percentage entered as 5% or 0.05, or another measure. Label row and column headings, use appropriate number formats, and include units in nearby cells or explanatory text. Formatting helps readers distinguish, for example, a percentage from a whole-number input.

Common problems and fixes

  • Results are blank or unexpected: Confirm that the formula reference is in the correct corner of the selected table and that the selected range includes the formula, test values, and result cells. Check that the input cell is the one actually used by the model formula.
  • Values appear in the wrong direction: Test values across the top require a Row input cell; values down the left require a Column input cell. In a two-variable table, assign both deliberately.
  • Every result looks the same: The formula may not depend on the input cell you selected, or the wrong input cell may have been assigned. Trace the formula’s references and verify the model changes when you edit that input manually.
  • Excel recalculates slowly: Large data tables can require many repeated calculations, especially when the workbook contains complex formulas or many linked tables. Reduce unnecessary test values or simplify the model. Excel’s calculation settings can affect when results update; check Formulas > Calculation Options if results appear stale.
  • The Data Table command is unavailable: Confirm that you are in desktop Excel and have selected a suitable range. What-If Analysis data tables are a desktop feature; Excel for the web may display a workbook containing one, but use desktop Excel to create or manage this analysis.

Data tables compared with other What-If tools

Excel’s What-If Analysis group includes Data Tables, Goal Seek, and Scenario Manager. They answer different questions. A data table calculates outputs for a set of values in one or two inputs. Goal Seek works backward from a desired formula result to find the value of one input that would produce it. Scenario Manager stores named sets of input values so you can switch among distinct cases, including cases with more than two changing cells.

For instance, use a data table to compare revenue across several prices and quantities. Use Goal Seek if you know a target revenue and want Excel to find the required price while other model inputs remain fixed. Use Scenario Manager if you want to compare named cases such as low, expected, and high assumptions across several inputs. These tools complement one another; none validates whether the assumptions are realistic.

Frequently Asked Questions

Is an Excel data table the same as an Excel Table?

No. An Excel Table is a formatted data range that helps manage lists, filters, and formulas. A What-If Analysis data table recalculates a formula for alternative values of one or two model inputs. The shared name can cause confusion, but the features serve different purposes.

How many variables can an Excel data table change?

A What-If Analysis data table can vary one or two input cells. A one-variable table tests a list of values for one input; a two-variable table tests combinations of values for two inputs. For more changing assumptions, consider Scenario Manager or a purpose-built formula model.

Why is my data table showing the same result in every cell?

Check whether the chosen input cell is referenced by the formula and whether the row or column input setting matches the position of your test values. If the model’s output does not change when you manually alter the supposed input cell, the formula may not depend on it.

Can I make a What-If Analysis data table in Excel for the web?

Use desktop Excel to create and manage What-If Analysis data tables. Excel for the web may show a workbook that contains one, but its support for creating and recalculating this feature is limited. The steps here use Excel for Microsoft 365 on Windows.

Can an Excel data table test more than two inputs?

Not in one What-If Analysis data table. It supports one or two input cells. To compare cases with more inputs, use Scenario Manager, create a separate scenario grid, or design a model that explicitly calculates the combinations you need.

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.