Excel Data Table Shortcut: What It Does and How to Use It

Excel has no single universal keyboard shortcut for the What-If Analysis Data Table command: in Windows, choose Data > What-If Analysis > Data Table. If you mean a formatted Excel table, select your data and press Ctrl+T instead. These are different features, and knowing which one you want is the key to using the right shortcut.

What “data table” means in Excel

People use “data table” to refer to two Excel features. A formatted table organizes a list of records, adds filter buttons, and can expand as you add rows. A What-If Analysis Data Table calculates how one or two changing inputs affect a formula. It lays out the results in a grid so you can compare scenarios at a glance.

The distinction matters because Ctrl+T creates the first kind, not the second. A What-If Analysis Data Table is a sensitivity-analysis tool: it temporarily substitutes test values into specified input cells, recalculates a formula for each combination, and displays the outputs. It is useful for exploring a model without manually changing its assumptions one by one.

Shortcut for a formatted Excel table

To turn a range of records into a formatted table in Excel for Microsoft 365 on Windows, click inside the range and press Ctrl+T. You can also select the range and choose Insert > Table. Confirm that the suggested range is correct and check My table has headers if the first row contains column names.

On Excel for Mac, Control+T is commonly used to create a table; if that shortcut is intercepted by your system or differs with your keyboard settings, choose Insert > Table. In Excel for the web, use Insert > Table if the keyboard combination conflicts with the browser. This formatted-table shortcut does not open What-If Analysis.

Formatted tables are a good fit for ordinary lists such as inventory, survey responses, or sales records. They provide filter and sort controls, carry formatting and formulas into new rows, and support structured references in formulas. They do not automatically calculate a grid of alternative outcomes for a model.

How to open the What-If Analysis Data Table

In the Windows desktop version of Microsoft 365 Excel, select the cells for the table, then choose Data > What-If Analysis > Data Table. The command is on the Data tab, in the Forecast or Data Tools area depending on the ribbon layout. The dialog asks for a Row input cell, a Column input cell, or both. There is no universal default Ctrl-key shortcut for this command; use the ribbon path rather than assuming Ctrl+T will open it.

Before opening the dialog, arrange the worksheet so Excel can identify the formula and the test values. For a one-variable table, put the formula beside or above the list of values. For a two-variable table, put the formula at the corner of a grid, with one set of test values across the top and the other down the side. The cells you name in the dialog must be the actual input cells used by the model formula.

What-If Analysis Data Tables are a desktop Excel feature. Excel for the web can be useful for viewing and editing many workbook elements, but use desktop Excel to create or manage this particular What-If Data Table if its command is unavailable in the browser.

Build a one-variable data table

Suppose a model calculates the area of a square from a side-length input. Put the side length in B1 and the result in B3. With the input in B1, enter this formula in B3:

=B1^2

For a simple example, set B1 to 10; the formula returns 100. To compare other side lengths, put the test values 5, 10, and 15 in A6:A8. Put a reference to the model result in B5. With the output cell in B3 and the scenario values in A6:A8, enter this formula in B5:

=B3

Now select A5:B8, choose Data > What-If Analysis > Data Table, and enter B1 as the Column input cell. Leave Row input cell blank, then choose OK. Excel substitutes each value from column A into B1 and returns the corresponding result in column B: 25, 100, and 225. The original input value in B1 is restored after the calculations.

Use a column input cell when the trial values run vertically in a column. If you instead lay the test values across a row, specify the model input as the Row input cell. The reference formula belongs in the corner of the selected range, not in the list of trial values.

Build a two-variable data table

A two-variable table compares every combination of two inputs. For example, put a rectangle’s length in B1, its width in B2, and the calculated area in B3. With length in B1 and width in B2, enter this formula in B3:

=B1*B2

If B1 is 5 and B2 is 4, the model returns an area of 20. For the scenario grid, put the model-result reference in D5, widths 2, 4, and 6 across E5:G5, and lengths 3, 5, and 7 down D6:D8. With the result in B3, enter this formula in D5:

=B3

Select D5:G8 and open the Data Table dialog. Set Row input cell to B2, because widths run across the top, and Column input cell to B1, because lengths run down the side. The results are 6, 12, and 18 for length 3; 10, 20, and 30 for length 5; and 14, 28, and 42 for length 7. Each number is the product of its row’s length and column’s width.

A common mistake is to reverse the row and column input cells. Excel does not infer what the headings mean: the row input cell receives values from the top row, while the column input cell receives values from the first column. Check that orientation before accepting the results.

Choose the right feature for the job

Use a formatted table when your goal is to manage a rectangular list of related records. Use a What-If Analysis Data Table when you already have a formula-based model and want to see its output under different input assumptions. One feature organizes data; the other evaluates scenarios.

Use Ctrl+T to create a formatted table from a list, add filter buttons, or make formulas easier to fill down.
Use What-If Analysis > Data Table to compare a formula’s result for one input or for combinations of two inputs.
Use ordinary formulas or a separate scenario sheet if you need more than two changing inputs, a custom report layout, or a calculation that must be easy to inspect cell by cell.

A Data Table is not the same as a PivotTable either. A PivotTable summarizes and rearranges existing records; a What-If Data Table recalculates a model using substituted inputs. Their names are similar, but their purposes and commands are different.

Common problems and fixes

If the Data Table command is unavailable or the dialog produces unexpected output, check the setup before changing formulas. These are frequent causes:

The formula cell is outside the selected grid or in the wrong corner. For one variable, include the output reference and the trial-value column or row in the selection. For two variables, place the reference at the grid’s top-left corner.
The wrong input cell is assigned. The input cell must be a cell referenced by the model formula. For a two-variable table, top-row values map to Row input cell and first-column values map to Column input cell.
The model does not depend on the input. If the formula never refers to the selected input cell, replacing that cell cannot change the result.
Calculation is set to skip data tables. In Windows desktop Excel, check Formulas > Calculation Options. If Automatic Except for Data Tables is selected, the table may not refresh with other formulas. Choose Automatic or recalculate the workbook as appropriate.
The worksheet is protected or the selection is malformed. Confirm you can edit the relevant cells and that the selected area includes the formula corner and all scenario values.

Data Tables can be computationally demanding because Excel recalculates the model repeatedly. A large grid, complex formulas, or many other calculations can make a workbook slower. Start with a small set of test values, confirm that the mapping works, and expand the grid only if needed.

Limits and alternatives

A What-If Analysis Data Table supports one or two input cells, not an unlimited number of assumptions. It is designed to show the output of a formula for a compact set of scenarios. For three or more variables, you might create separate scenario calculations, use formulas that explicitly reference each scenario, or use Scenario Manager under Data > What-If Analysis. Scenario Manager stores named sets of input values, while a Data Table displays a grid of results.

Data Table outputs are also less transparent than a worksheet filled with separate formulas: the cells are managed as part of the What-If table, rather than being independent calculations you can freely edit one at a time. If readers need to audit every scenario’s logic, explicit formulas in a normal grid may be easier to follow. If you only need to compare a few named cases, a small summary table with clearly labeled assumptions may be clearer than a large grid.

On large models, consider whether every combination is useful. A 20-by-20 table asks Excel to evaluate 400 scenarios, so simplify the model or reduce the number of trial values if recalculation becomes slow. Keep the original assumptions visible and label units, such as centimeters or square meters, so that the results are interpretable.

Frequently Asked Questions

What is the keyboard shortcut for Data Table in Excel?

There is no universal default keyboard shortcut for the What-If Analysis Data Table command in Excel for Microsoft 365 on Windows. Use Data > What-If Analysis > Data Table. Ctrl+T is the shortcut for creating a formatted Excel table, which is a different feature.

Does Ctrl+T create a What-If Analysis table?

No. Ctrl+T creates a formatted table from a range of records. To make a What-If Analysis Data Table, set up a model formula and test values, select the scenario grid, then use Data > What-If Analysis > Data Table.

Can a What-If Data Table use more than two variables?

No. A Data Table can substitute values for one input cell or two input cells. For more assumptions, use separate formulas, create additional scenario grids, or consider Scenario Manager.

Why is my Data Table showing the same result in every cell?

Check that the formula depends on the input cell or cells named in the dialog and that the row and column input cells are assigned in the correct orientation. Also check calculation settings; Automatic Except for Data Tables can prevent the grid from refreshing automatically.

Can I create a What-If Data Table in Excel for the web?

Use the desktop Excel app to create or manage a What-If Analysis Data Table if the command is not available in Excel for the web. The web version may still display workbook content, but the desktop ribbon path is the dependable way to build this feature.

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.