Excel Data Table Greyed Out? Causes, Checks, and Fixes

How-to basics · Updated November 19, 2026

If Excel’s What-If Analysis > Data Table command is greyed out, first exit cell-editing mode, ungroup any selected worksheets, and check that you are using the desktop Excel app on an unprotected sheet. Excel for the web cannot create What-If Analysis data tables, so open the workbook in Excel for Windows or Mac to use the feature.

What the Data Table command does

An Excel What-If Analysis data table recalculates a formula using one or two sets of alternative input values. It is useful when you want to compare many possible outcomes without changing an input cell manually each time. For example, a sales model could show how revenue changes at different prices, or how it changes across combinations of price and units sold.

This feature is different from a regular Excel table, which is a formatted range with tools such as filter buttons and structured references. Both are sometimes called a “data table,” but the greyed-out command under What-If Analysis refers to the what-if tool. You do not need to format the range as an Excel table to use it.

A What-If Analysis data table uses existing formula cells and temporary input substitutions. It does not replace the formulas in your model, and it is not a general-purpose way to run an unlimited number of scenarios. It supports one variable or two variables at a time.

Check the most common causes first

Work through these checks before rebuilding the workbook. Some conditions disable ribbon commands outright; others allow the command but prevent a data table from being set up correctly.

  • You are editing a cell. If the insertion point is visible in the formula bar or inside the cell, Excel is in edit mode. Press Esc to cancel the edit, or Enter to accept it, then select a worksheet cell.
  • More than one sheet is grouped. When multiple worksheet tabs are selected, Excel applies many actions across the group and disables some commands. Click a single sheet tab, or right-click a selected tab and choose Ungroup Sheets.
  • The sheet is protected. Check Review > Unprotect Sheet. If Excel asks for a password, you need permission and the correct password from the workbook owner; do not try to bypass the protection.
  • You are in Excel for the web. Open the file in the desktop app. The web version can be useful for viewing and editing many workbook features, but it does not provide the desktop What-If Analysis data-table creation workflow.
  • The selection or location is unsuitable. A data table needs one contiguous rectangular range laid out around the model formula and test values. Keep it outside an existing formatted Excel table, and avoid merged cells in the range.

If none of these explains the problem, save the workbook, close and reopen it in desktop Excel, and check whether the command is still unavailable in a new, unprotected worksheet. That comparison can help distinguish a workbook-specific restriction from an application or platform issue.

Restore the command in Excel for Windows

In Microsoft Excel for Microsoft 365 on Windows, use this sequence. The exact placement of the command can vary slightly with ribbon layout, but the standard path is Data > What-If Analysis > Data Table.

  1. Press Esc or Enter to leave any active cell edit.
  2. Click one worksheet tab so that only one sheet is selected. If tabs are grouped, right-click a selected tab and choose Ungroup Sheets.
  3. Go to Review and, if the sheet is protected, choose Unprotect Sheet. Enter the password only if you are authorized to do so.
  4. Click a cell in a normal worksheet area, then open Data > What-If Analysis. Check whether Data Table is available.
  5. If the command is enabled, arrange a formula and input values in a contiguous range, select the full range, and open Data Table.

For a useful diagnostic, create a blank worksheet in the same workbook and try the command there. If it works on the new sheet but not the original, look for sheet protection, grouped tabs, merged cells, or a range embedded in an Excel table on the original sheet. If the command is unavailable throughout the workbook, confirm you are not in the browser version or in cell-editing mode.

Set up a one-variable data table

Before using the command, make sure the model has a formula that depends on the input you want to vary. In this example, the model calculates revenue from units sold and price. With units in B1 and price in B2, enter this formula in B3:

With units sold in B1 and price per unit in B2, the model formula in B3 is =B1*B2.

If B1 contains 100 and B2 contains 10, B3 returns 1,000. To compare prices from 8 through 12 while keeping units fixed, put the test prices in F2:F6 and put a reference to the model result in G1. With the model result in B3, enter =B3 in G1. The setup looks like this:

  • F1 is blank.
  • G1 contains the formula reference to the result in B3.
  • F2:F6 contains 8, 9, 10, 11, and 12.
  • Select the complete range F1:G6.

Choose Data > What-If Analysis > Data Table. Because the test values run down a column, leave Row input cell blank and set Column input cell to B2, the price input in the original model. Click OK. Excel substitutes each test price into B2 in turn and records the resulting revenue in G2:G6: 800, 900, 1,000, 1,100, and 1,200. The original B2 value is not permanently replaced by each scenario value.

A frequent setup mistake is selecting only the test values, such as F2:F6, instead of the full rectangle containing the result formula and test values. Another is choosing the wrong input cell. The Column input cell must be the original model cell whose value the scenarios replace, not the first cell in the list of test values.

Set up a two-variable data table

A two-variable table tests combinations of two inputs. For the same revenue model, place a reference to B3 in D1, price scenarios across E1:H1, and units scenarios down D2:D5. Select D1:H5, then open Data > What-If Analysis > Data Table. Set Row input cell to B2, because the price scenarios are across the top row, and Column input cell to B1, because the unit scenarios run down the first column.

Excel fills the interior cells with revenue for each price-and-units combination. For instance, a price of 10 and 100 units returns 1,000. Keep the orientation straight: values across the top replace the Row input cell, while values down the left replace the Column input cell. The “row” and “column” labels refer to where scenario values sit in the table, not to the direction of the formula’s calculation.

When the command is enabled but the table does not work

A greyed-out ribbon command is not the same problem as a table that opens but returns unexpected results. Once Data Table is available, check the setup and calculation behavior as well:

  • The output does not change. Confirm that the formula actually depends on the input cell assigned in the dialog. If your result formula references a different cell, substituting values into the selected input will have no effect.
  • The results are in the wrong cells. Select the full rectangle, including the formula cell and the row or column of scenarios. For a one-variable table, put the formula above a vertical list or to the left of a horizontal list.
  • The values look stale. Go to Formulas > Calculation Options and check the calculation setting. Automatic Except for Data Tables can leave data-table results unchanged until recalculation; it does not normally disable the Data Table command. Choose Automatic if appropriate for the workbook, or recalculate the workbook.
  • The table behaves oddly inside a structured table. Put the What-If Analysis range in ordinary worksheet cells outside the formatted Excel table. The two features have different structures and purposes.
  • The model uses other formulas or linked data. Inspect the model formula and dependencies. A data table only supplies replacement values to its designated input cell; it cannot correct broken references, unavailable external links, or errors already present in the model.

Data tables can require repeated recalculation, especially in large workbooks with complex formulas. If the workbook becomes slow, test a smaller set of scenarios or use a simpler model while diagnosing the issue. Avoid switching calculation to Manual without understanding the effect: other formulas may also stop updating until you recalculate.

Excel desktop, Mac, and web differences

The instructions above target Excel for Microsoft 365 on Windows. Excel for Mac also has What-If Analysis data tables in the desktop application, though ribbon arrangement and dialog presentation can vary by version. Look on the Data tab for What-If Analysis and Data Table. The core layout rules and the meaning of the row and column input cells are the same.

Excel for the web is the main platform distinction: use Open in Desktop App to create or manage a What-If Analysis data table. If a workbook contains one already, do not assume that a browser view gives you the same creation controls or calculation behavior as the desktop app. When the desktop command is disabled too, check worksheet mode and protection rather than repeatedly trying the browser ribbon.

Data tables versus other scenario tools

Choose a What-If Analysis data table when you need a compact grid of results for one or two changing inputs. It is particularly clear for a small sensitivity analysis, such as comparing profit at several selling prices and sales volumes.

For a single alternative value, changing an input manually and reading the model result may be simpler. For named scenarios that change several inputs together, Scenario Manager may be a better fit. For larger or more flexible scenario lists, ordinary formulas or a separate analysis sheet can be easier to maintain. A formatted Excel table is useful for organizing records, but it is not a substitute for a What-If Analysis data table.

These tools answer different questions. A What-If Analysis data table asks, “What happens to this formula if I substitute these values into one or two input cells?” A formatted Excel table organizes rows of related data. Scenario Manager stores sets of input values. Picking the tool that matches the question avoids forcing a data table into a job it was not designed to do.

Frequently Asked Questions

Why is Data Table greyed out in Excel?
Common reasons include editing a cell, having multiple worksheet tabs grouped, working on a protected sheet, or using Excel for the web. Exit edit mode, select one sheet, check Review > Unprotect Sheet, and try the desktop app.

Does an Excel data table mean the same thing as a formatted table?
No. A What-If Analysis data table calculates formula outcomes for alternate input values. A formatted Excel table organizes a range and adds features such as filtering and structured references.

Why does Data Table show the same result for every scenario?
Check that the model formula depends on the input cell chosen in the Data Table dialog. Also verify that the scenario values are positioned correctly and that the input cell is assigned to the correct row or column field.

Can I create a What-If Analysis data table in Excel for the web?
Use the desktop Excel application to create one. In the web app, choose Open in Desktop App if available, then use Data > What-If Analysis > Data Table on a suitable worksheet.

Does Automatic Except for Data Tables cause the command to be greyed out?
Usually, no. That calculation setting affects when existing data-table results recalculate; it is different from whether the Data Table command is available. Check worksheet state, protection, and platform if the command itself is disabled.

Related

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.