An Excel Data Table is a What-If Analysis tool that recalculates a formula for a range of one or two changing input values. In Microsoft 365 for Windows, create one from Data > What-If Analysis > Data Table; if you mean a formatted grid of records instead, use Insert > Table.
What Excel means by a data table
The phrase “data table” can refer to two different Excel features. A What-If Analysis Data Table is a sensitivity tool: it shows how a formula’s result changes as you try different values in one or two input cells. It is useful when a workbook already contains a calculation model and you want to compare possible assumptions side by side.
An Excel Table, by contrast, is a structured range for storing and managing records, such as a list of orders with columns for date, customer, and amount. You can create one with Insert > Table. Excel Tables have filter buttons, automatically expand as data is added, and support structured references. They do not automatically run a sensitivity analysis. This article focuses first on the What-If Analysis feature, then compares the two meanings below.
A Data Table does not replace the model formula. It temporarily supplies candidate values to designated input cells, recalculates the workbook, and records the result for each candidate. The original input values are restored after the calculation. A one-variable Data Table varies one input cell; a two-variable Data Table varies two.
How a one-variable table works
Imagine a small business model with units sold in B2, price per unit in B3, fixed costs in B4, and profit in B5. With units in B2, price in B3, and fixed costs in B4, enter =B2*B3-B4 in B5. If B2 is 100, B3 is 12, and B4 is 500, the result is 700: 100 × 12 − 500.
To see how profit responds to different prices, Excel needs a list of candidate prices and a reference to the model’s result. For example, put the output reference in E2, candidate prices in D3:D6, and leave D2 blank. With the model above, enter =B5 in E2; this points to the current profit result of 700. The Data Table will place the recalculated results beside the candidate prices.
When the selected range is D2:E6, the candidate values are in the first column, so use the dialog’s Column input cell field and select B3, the model’s price input. Excel substitutes each price into B3 in turn, recalculates B5, and fills E3:E6. At prices of 10, 12, 15, and 18, the results are 500, 700, 1,000, and 1,300 respectively, assuming the other model inputs remain unchanged.
Create a one-variable Data Table
- Set up a working model with an output formula that depends on the input you want to vary. Keep the input cell distinct from the output cell.
- Arrange candidate values in a single column or row. Put a direct reference to the output formula in the cell at the corner of the range, as in E2 above.
- Select the whole rectangular range, including the output reference, the candidate values, and the empty cells where results will go. In the example, select D2:E6.
- Choose Data > What-If Analysis > Data Table. Since the trial values are in a column, set Column input cell to the model’s input cell, B3. Leave Row input cell empty, then select OK.
If candidate values run horizontally instead, use the Row input cell field. The row input cell is the model cell that should receive each value from that horizontal list. Do not select the output cell as the input cell: the input cell must be the assumption the model uses to calculate the output.
Create a two-variable Data Table
A two-variable table compares combinations of two assumptions. Reuse the profit model, with units in B2, price in B3, fixed costs in B4, and profit in B5. Put a reference to B5 in D10, prices of 10, 12, and 15 across E10:G10, and unit counts of 80, 100, and 150 down D11:D13. The formula in D10 should be =B5; with the model’s current assumptions, it points to profit in B5.
Select D10:G13 and open Data > What-If Analysis > Data Table. The prices are across the top, so set Row input cell to B3. The unit counts are down the first column, so set Column input cell to B2. Excel fills the inside of the grid with profit results. For example, 80 units at a price of 10 gives 300 (80 × 10 − 500), while 150 units at a price of 15 gives 1,750. The table makes the interaction visible: changing either price or volume changes the outcome.
Only two input cells can be varied in one Data Table. If you want to explore a third assumption, you can make separate tables for different fixed values of that assumption, or use another analysis approach. Keep the row and column labels clear, with units such as dollars or units sold, so readers can tell what each scenario represents.
Read, format, and manage the results
Data Table outputs are generated cells, not a set of individually typed formulas. You can apply number formats, borders, and conditional formatting to make patterns easier to read. For the profit example, currency formatting communicates that the results are dollar amounts; a color scale can help identify higher and lower values. Formatting changes appearance, not the underlying assumptions or calculations.
Because Excel calculates a result for each candidate value or combination, larger tables can increase recalculation time, especially when the workbook contains complex formulas. If calculation becomes sluggish, check whether Data Tables are needed throughout the workbook and consider reducing the number of scenarios. Excel desktop also has a calculation setting called Automatic Except for Data Tables, which can reduce repeated recalculation of these tables while other formulas continue to calculate automatically. Find calculation options on the Formulas tab.
To refresh a table when calculation is set to manual or automatic except for Data Tables, use Excel’s calculation commands or switch the workbook back to automatic calculation as appropriate. Calculation behavior can depend on workbook settings, so if results appear stale, check the calculation mode before assuming the model formula is wrong.
When Data Tables are useful
Data Tables are best for compact “what if?” comparisons built around an existing formula. Common uses include estimating how sales revenue changes with price and quantity, checking a project’s output under different operating assumptions, comparing a forecast across alternative growth rates, or seeing how a measurement changes across two experimental settings. They display a grid of outcomes without requiring a separate copy of the model for every scenario.
They are less suitable when each scenario needs many independent assumptions, descriptive notes, or a permanent record of actual cases. In those situations, a conventional table with one row per scenario may be easier to audit. A Data Table also does not decide which assumption is most realistic; it only calculates outputs from the values supplied. The quality of its results depends on the model and inputs being sensible.
Common problems and fixes
- The results are identical or unexpected. Check that the model’s output formula actually depends on the input cell you selected. A hard-coded number or a formula that refers to a different cell will not respond to the table’s substitutions.
- The table is blank or the range is wrong. Include the output reference, every candidate value, and the full destination area when selecting the range. In a one-variable layout, leave the opposite input field in the dialog empty.
- Rows and columns seem reversed. Values across the top map to Row input cell; values down the first column map to Column input cell. Choose the model input that corresponds to those values, not simply the nearest cell.
- Results do not update. Check Formulas > Calculation Options and make sure the workbook’s calculation mode is suitable. A manual setting or Automatic Except for Data Tables can make a Data Table appear not to refresh until recalculation.
- The model’s original assumption appears changed. A Data Table is designed to substitute trial values temporarily and return to the original model input. Check the input and output references and recalculate; avoid manually typing over the generated result cells.
Data Tables versus Excel Tables and other tools
To turn a data range into a structured Excel Table, select the range and choose Insert > Table, then confirm whether it has headers. Excel Tables help organize and filter records, apply consistent formatting, and use column names in formulas. They are a good choice for sales logs, inventory, and other row-based data. The What-If Analysis Data Table is for calculating the effect of alternative inputs, not for organizing a list.
Other What-If Analysis tools serve different purposes. Goal Seek works backward from a desired formula result to find one input value that produces it. Scenario Manager stores sets of multiple input values that can be switched and summarized. A Data Table is convenient when the goal is a visible grid of results for one or two changing inputs. Choose based on the question: a range of combinations, one target value, or named sets of assumptions.
In Microsoft 365 for Windows, the Data Table command is on Data > What-If Analysis > Data Table. Excel for Mac includes What-If Analysis tools in its desktop ribbon, though ribbon presentation can vary by release. Excel for the web does not provide the desktop Data Table command for creating or changing these What-If tables, so use the desktop application for that work.
Frequently Asked Questions
What is a data table in Excel?
A What-If Analysis Data Table shows the result of a formula for different values in one or two model input cells. People may also use “Excel table” to mean a formatted, filterable range created with Insert > Table; that is a separate feature.
How do I create a data table in Excel?
Set up a formula linked to the input you want to test, arrange candidate values in a row or column, select the full range, and choose Data > What-If Analysis > Data Table. Assign the matching row or column input cell in the dialog.
Can an Excel Data Table use more than two variables?
No. A What-If Analysis Data Table varies one or two input cells. For additional assumptions, use multiple tables, a list of scenarios, or another modeling approach.
Why is my Excel Data Table not calculating?
Confirm that the output formula depends on the selected input cell, the correct row or column input cell is assigned, and the full table range was selected. Also inspect Formulas > Calculation Options, since calculation mode can affect refreshes.
Is an Excel Data Table the same as an Excel Table?
No. A Data Table runs formula scenarios for What-If Analysis. An Excel Table organizes records and offers features such as filters, automatic expansion, and structured references.
Related
- How to Run Goal Seek in Excel to Find a Target Value
- How to Run Goal Seek Automatically in Excel
- How to Remove Group Rows in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Text to Columns: How to Undo and Restore DataTo undo Text to Columns in Excel for Microsoft 365 on Windows, press Ctrl+Z immediately after running it;…
- Instrument Inventory Spreadsheet: Setup and Key FieldsAn instrument inventory spreadsheet is a structured list of the musical instruments and related equipment an organization owns,…
- Excel Table Themes: Styles, Colors, and How to Change ThemIn Excel, “table themes” usually means the built-in table styles that control a table’s colors, borders, and visual…
- Excel Freeze Panes in Two Directions: Rows and ColumnsYes. In Excel for Microsoft 365 on Windows, you can freeze rows and columns at the same time:…
- Export Phone Contacts to Outlook on iPhone and AndroidTo export phone contacts to Outlook, save them from your iPhone or Android phone as a contact file,…
- Kids Chore List Excel Template: Build a Simple TrackerA kids chore list Excel template is a simple spreadsheet for listing household jobs, assigning them to children,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.