Build a Data Table in Excel: Methods and Examples

To build a regular data table in Excel for Microsoft 365 on Windows, select your range and choose Insert > Table, or press Ctrl+T. If you mean a What-If Analysis data table that tests different input values in a calculation, use Data > What-If Analysis > Data Table after setting up a formula and input cells. These are different features, and choosing the right one is the first step.

Two Excel features called data tables

An Excel Table is a structured way to store and manage a list of records, such as orders, inventory, or survey responses. It adds features such as filter buttons, consistent formatting, expandable ranges, and named columns. It does not automatically calculate a series of hypothetical results.

A What-If Analysis Data Table is a calculation tool. It substitutes one or two sets of trial values into designated model inputs and shows the resulting output for each combination. For example, it can show how revenue changes at several prices and sales quantities. It is not the same thing as a formatted table of records, even though both features use the word “table.”

For a list you want to sort, filter, and extend, create an Excel Table. For a grid of possible outcomes from an existing formula, use a What-If Data Table. You can also use both in one workbook: a regular Table can hold source data, while a What-If Data Table explores a calculation built from that data.

Create a regular Excel Table

Arrange the data in a simple rectangle, with one header row and one record per row. For example, columns could be Order Date, Product, Quantity, and Unit Price. Avoid blank rows or columns inside the range, and give each column a distinct, nonblank header. Then create the table:

Click a cell inside the data range.
Choose Insert > Table, or press Ctrl+T on Windows.
Check that Excel selected the full range.
Check My table has headers if the first row contains column names, then select OK.

Excel adds filter arrows to the header row and applies a table style. To change its appearance or rename it, click inside the table and use the Table Design tab. A short, descriptive table name, such as Orders, is easier to understand later than the default name Table1. Table names cannot contain spaces; use an underscore if needed.

To include more records, type in the row directly below the table or paste adjacent data. Excel normally expands the table to include the new rows. You can also use Table Design > Resize Table to adjust its range explicitly. On Mac, use Insert > Table to create one; ribbon labels and shortcuts can vary with the Excel version and keyboard settings, so the menu route is the dependable option.

Use the features of a regular table

Table filter buttons let you sort a column or narrow the records shown, for example to display only orders for one product. Filtering hides rows from view; it does not delete those records. Table styles and banded rows can make a wide list easier to scan, but formatting is separate from the underlying values.

Tables also make formulas and references easier to maintain. For instance, a formula in a new calculated column can fill down automatically, and references based on column names are less dependent on fixed cell addresses. If you add a Total column to an Orders table, Excel can use that column consistently as rows are added. The exact formula depends on your headers and calculation, so check that it uses the intended columns.

To summarize a table, click inside it and select Table Design > Total Row. The row can show options such as a sum, average, or count for a column. A total row is useful for a quick summary, but it is not a substitute for a separate analysis when you need multiple grouped results or a report with several measures.

Tables work well with sorting, filtering, charts, and PivotTables. They are especially useful when a dataset grows over time, because ranges and formulas are easier to keep aligned than with a fixed block of cells. They are not required for every worksheet: a small one-off calculation may be clearer as ordinary cells.

Build a one-variable What-If Data Table

A one-variable Data Table tests a list of values in one model input cell and records the resulting formula output. First build a small model with an input cell and a result formula. Keep the model and What-If table on the same worksheet for a straightforward setup.

For example, put a unit price of 10 in B2 and a quantity of 100 in B3. With price in B2 and quantity in B3, enter =B2*B3 in B4. The result is 1,000. This is the model output that the table will recalculate when it temporarily substitutes other prices into B2.

Set up the What-If table in D1:E5. Put the heading Price in D1, enter =B4 in E1 to link to the output, and enter trial prices 8, 10, 12, and 15 in D2:D5. With the model output in B4, the link formula in E1 is =B4, which returns 1,000 before the table is calculated.

Select D1:E5, including the output link and the trial values.
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 the result column with the outcomes for the listed prices.

The calculated revenues are 800, 1,000, 1,200, and 1,500 for prices 8, 10, 12, and 15, respectively, with quantity held at 100. The trial values are not permanent replacements for B2: the Data Table evaluates the model for each value and displays the results while preserving the original model input.

Build a two-variable What-If Data Table

A two-variable Data Table tests combinations of values for two model inputs. Continue with the example where B2 is price, B3 is quantity, and B4 calculates revenue. Across the top row of the grid, enter trial prices such as 8, 10, and 12. Down the first column, enter trial quantities such as 50, 100, and 150. Put =B4 in the top-left corner of the grid, above the quantity values and to the left of the price values.

Select the entire grid, including the output link, both sets of trial values, and the blank cells where results should appear. Choose Data > What-If Analysis > Data Table. Set Row input cell to B2 because the prices are arranged across a row, and set Column input cell to B3 because the quantities are arranged down a column. Select OK.

The resulting grid contains the revenue for each price-and-quantity combination. For example, price 10 and quantity 100 produce 1,000; price 12 and quantity 150 produce 1,800. These results follow directly from price multiplied by quantity. Row input and column input refer to the direction of the trial values in the grid, not to the position of the model cells.

Choose the right method for your task

Use an Excel Table for a growing list of records that you want to sort, filter, format, or reference by column name.
Use a one-variable Data Table when you want to see how one formula result changes across many possible values for one input.
Use a two-variable Data Table when you need to compare combinations of two inputs in a grid.
Use formulas or a PivotTable when the question is about summaries by category, repeated calculations, or results that need to be easy to audit and customize.

What-If Data Tables are useful for sensitivity analysis, such as comparing revenue under different prices and volumes. They are not forecasts: the table only evaluates the formulas and assumptions in the workbook. If the model leaves out costs, constraints, or other important factors, the displayed output will not account for them.

Common problems and fixes

If a What-If Data Table returns unexpected results, check the layout before changing the formula. The top-left cell of the selected grid must refer to the output formula, and the trial values must be in the row or column that matches the selected input cell. In a two-variable table, using the wrong row or column input cell can produce plausible-looking but incorrect results.

Wrong input cell: Choose the original model input that the formula actually depends on, not a cell that merely displays a trial value.
Wrong grid selection: Include the output link and every trial value, as well as the empty result area.
Missing output formula: Put a reference to the model result in the grid’s corner cell; typing a fixed number there will not link the table to the model.
Unclear regular-table headers: Give each column a unique header and keep the records contiguous so Excel can identify the table range.
Slow recalculation: Large What-If Data Tables can require many model calculations. Start with a smaller set of trial values and expand it only if useful.

What-If Data Tables are a desktop Excel feature. Excel for the web can display workbook content, but use desktop Excel to create or edit this type of What-If table. In Mac desktop Excel, look for Data > What-If Analysis > Data Table; the ribbon layout can differ slightly by version.

Frequently Asked Questions

How do I make a table in Excel quickly?
Click inside a contiguous range with headers and records, then choose Insert > Table. On Windows, Ctrl+T opens the Create Table dialog. Confirm the selected range and whether it has headers, then select OK.

Is an Excel Table the same as a What-If Data Table?
No. An Excel Table organizes records and adds features such as filters and expandable columns. A What-If Data Table substitutes trial values into one or two model inputs and records the resulting formula outputs.

Why is the Data Table option unavailable?
Check that you are using desktop Excel and that you are selecting a range set up as a What-If table, including an output formula reference and trial values. The What-If Data Table feature is not created in Excel for the web.

Can a What-If Data Table test more than two inputs?
No. Excel’s What-If Analysis Data Table supports one or two variable input cells. To study more inputs, you may need separate scenarios, formulas, or another modeling approach.

Can I edit one result inside a What-If Data Table?
Not as an independent result while it belongs to the calculated table. Change the model, trial values, or input-cell settings and recalculate the table instead. If you need to keep a result as a separate value, copy it elsewhere and paste it as a value.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.