Excel Data Table Cheat Sheet: What-If Analysis Guide

An Excel Data Table is a What-If Analysis tool that recalculates a formula for different values in one or two input cells. To create one in Excel for Microsoft 365 on Windows, set up a model formula, arrange test values beside or above a reference to that formula, select the whole grid, then choose Data > What-If Analysis > Data Table and identify the input cell or cells to substitute.

What an Excel Data Table Does

A Data Table lets you compare many possible results without changing an input and recalculating the model manually each time. It temporarily substitutes each test value into a specified cell, recalculates formulas that depend on that cell, and displays the results in a grid. The substitutions are for calculation; the original input cell keeps its value.

For example, a small sales model might calculate revenue from a unit price and a quantity. A Data Table can show how revenue changes at several prices, or across combinations of prices and quantities. The model remains in its original cells, while the table provides a compact set of scenarios.

This feature is different from an ordinary Excel table, also called a structured table. An ordinary table organizes rows of records and can expand as you add data. A Data Table is a what-if calculation tool. Use Insert > Table, or Ctrl+T on Windows, to create a structured table; use Data > What-If Analysis > Data Table for scenario calculations.

Choose One Variable or Two

A one-variable Data Table tests multiple values for one model input. Use it when you want to see how one assumption affects a result while all other assumptions stay fixed. Common examples include comparing revenue at different prices or estimating output at different production quantities.

A two-variable Data Table tests combinations of values for two input cells. It is useful when the result depends on two assumptions, such as price and quantity, or interest rate and loan term. It does not support three or more changing input cells in the same table. For more complex scenario work, use Scenario Manager, formulas, or a separate scenario model.

Choose one variable when you have one list of possible values and one input cell to test.
Choose two variables when you have one list across the top and another down the side, each corresponding to a different input cell.
Use a regular formula grid instead if you need greater control over how each scenario is calculated or displayed.

Set Up a One-Variable Data Table

Here is a simple revenue model. Put the unit price in B2 and the number of units in B3. With unit price in B2 and units in B3, enter =B2*B3 in B4. If B2 is 12 and B3 is 100, B4 returns 1,200.

To compare prices while keeping quantity at 100, arrange the test values in a column. Leave A5 blank, put a reference to the result in B5, and list prices in A6:A10. With the model result in B4, enter =B4 in B5. Put 8, 10, 12, 14, and 16 in A6:A10. Select A5:B10, then choose Data > What-If Analysis > Data Table. Leave Row input cell blank and set Column input cell to B2, because the trial values are arranged vertically and replace the price in B2.

The results in B6:B10 should be 800, 1,000, 1,200, 1,400, and 1,600. Each is the listed price multiplied by the fixed quantity of 100. The original value in B2 is not permanently replaced by the trial prices.

If your trial values run across a row instead, place the result reference in the cell immediately to their left, select the reference and values together, and use the Row input cell for the model input being tested. The direction of the values determines which input box to use: across means Row input cell; down means Column input cell.

Build a Two-Variable Data Table

Use the same model, with price in B2, units in B3, and revenue calculated in B4. Put a reference to the result in B5. Across C5:E5, enter trial prices of 8, 10, and 12; down B6:B8, enter quantities of 50, 100, and 150. Select B5:E8 and open Data > What-If Analysis > Data Table.

Set Row input cell to B2, because the trial prices are across the top row. Set Column input cell to B3, because the trial quantities are down the first column. Excel calculates each price-and-quantity combination. For example, the result for a price of 10 and 100 units is 1,000; for a price of 12 and 150 units, it is 1,800.

The top-left cell of the selected grid must link to the model result, while the row and column headings contain the trial values. The two input cells must be different cells, and the model result must depend on them. If the formula does not use an input cell, changing that input cannot affect the displayed result.

Read and Format the Results

Each interior cell represents the output for the values indicated by its row and column headings. Keep headings descriptive and close to the table so that a copied or printed grid is still understandable. For example, label a price axis as dollars per unit rather than relying on readers to infer the units from a number such as 12.

Format the outputs to match the calculation: currency for revenue, percentages for rates, or whole numbers for counts. Number formatting changes how values appear, not the underlying calculations. You can also use conditional formatting to highlight high or low results, but make sure the color scale does not imply precision the underlying assumptions do not support.

A Data Table is linked to its model, so changes to relevant formulas or assumptions can change its results when Excel recalculates. It is not a saved record of every past state. If you need a permanent snapshot, copy the results and use Paste Special > Values in a separate location, while retaining a clear note of the assumptions used.

Common Errors and How to Fix Them

Every result is identical. Check that the model formula depends on the selected input cell, and that you chose the correct row or column input cell. Also confirm the trial values are in the expected orientation.
The grid returns zeros or unexpected values. Confirm the top-left cell contains a reference to the intended output formula, not a typed value or a heading. Check that the trial values are numeric if the model expects numbers.
The input boxes are reversed. Values across the top use Row input cell; values down the left use Column input cell. In a two-variable table, assign both based on the placement of their corresponding trial values.
The table does not update promptly. Check Excel’s calculation mode. In desktop Excel, calculation settings are available on the Formulas tab under Calculation Options. A workbook set to Manual may need recalculation; Data Tables can also make large workbooks slower.
Results look like formulas cannot be edited individually. The calculated cells belong to the Data Table array. Change the table setup or its input values rather than trying to edit one interior result as an independent formula.

Limits, Speed, and Version Notes

A Data Table is a sensitivity-analysis aid, not a forecasting guarantee. It shows the output of the workbook’s formulas for the assumptions supplied. It does not judge whether those assumptions are realistic, account for uncertainty unless you model it, or identify a best choice automatically. Review the model logic and the units before relying on a result.

Large tables can slow recalculation because Excel evaluates the model repeatedly for different input combinations. Keep the grid to useful ranges, avoid unnecessary volatile calculations in the model, and consider whether a smaller scenario set answers the question. A one-variable table with 100 trial values requires many evaluations; a two-variable table with 100 values along each axis can require 10,000 combinations.

The menu paths above are for Excel for Microsoft 365 on Windows. Excel for Mac has a What-If Analysis and Data Table feature in the desktop app, though ribbon placement can vary by release. Excel for the web does not provide the desktop Data Table command for creating this analysis, so open the workbook in desktop Excel to build or change one. Keyboard navigation differs between Windows and Mac; using the ribbon menu avoids relying on a version-specific shortcut.

When to Use an Alternative

Use a regular formula grid when scenarios need different logic, more than two changing assumptions, or extra columns of explanation. For instance, a grid can calculate price, units, cost, and margin separately for each scenario, whereas a Data Table is designed to return one or more formula outputs for its input substitutions.

Scenario Manager is another built-in What-If Analysis option for storing named sets of values across multiple changing cells. Goal Seek works in the opposite direction: it finds an input value that makes one formula reach a target. Solver, an add-in, can optimize an objective subject to constraints. These tools answer different questions, so choose based on whether you want a result grid, saved cases, a target input, or an optimized solution.

Frequently Asked Questions

Is an Excel Data Table the same as a table made with Ctrl+T?
No. Ctrl+T creates a structured table for organizing data records. A Data Table is a What-If Analysis feature that substitutes trial values into model input cells and calculates results.

Can a Data Table change more than two input cells?
No. Excel’s Data Table feature supports one or two input cells per table. For three or more changing assumptions, consider Scenario Manager or a formula-based scenario grid.

Why does my Data Table show the same result in every cell?
The output formula may not depend on the input cell you selected, or the row and column input cells may be assigned incorrectly. Verify the model formula and match the input box to the direction of the trial values.

Does a Data Table permanently change my model inputs?
No. Excel temporarily substitutes each trial value while calculating the table, then retains the original input value. The results are tied to the model and can update when the workbook recalculates.

Can I create a Data Table in Excel for the web?
The web app does not include the desktop Data Table command for building this what-if analysis. Open the workbook in Excel for Microsoft 365 on Windows or the Excel desktop app for Mac to create or edit the table.

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.