Excel Data Table Not Working? Fix the What-If Tool

If an Excel What-If Analysis data table is not working, first check that its output formula and test values are arranged correctly, then confirm the row or column input cell points to a changeable model input. In Microsoft 365 for Windows, use Data > What-If Analysis > Data Table; also check calculation settings, because Excel can be configured not to recalculate data tables automatically.

Make sure you mean a What-If Analysis data table

Excel uses the word table for two different features. A What-If Analysis data table substitutes possible values into one or two model input cells and displays the results of a formula for each combination. It is a sensitivity-analysis tool, useful for questions such as how profit changes with price or how a payment changes with interest rate.

An Excel table, by contrast, is a formatted range of records with features such as filter buttons, structured references, and automatic formula filling. You create one with Insert > Table or Home > Format as Table. If formulas are not filling down in that kind of table, the problem is not the What-If Analysis Data Table command. The table may have a calculated-column setting or formula issue instead. Look for the Table Design tab when the formatted table is selected.

How a What-If data table works

A data table does not contain a separate formula for every scenario. Excel takes the values you provide, temporarily places them in the selected model input cell or cells, recalculates the workbook, and records the result of a formula. That is why a working model and a correctly linked output formula are essential: if the result formula does not depend on the input being varied, the displayed values will not meaningfully change.

For example, suppose a simple sales model stores unit price in B2, units sold in B3, fixed costs in B4, and profit in B5. With those labels and values in place, and with B2 set to 20, B3 set to 100, and B4 set to 500, the formula in B5 is =B2*B3-B4. The result is 1,500 because 20 times 100, minus 500, equals 1,500. Before using this formula, the assumed layout is price in B2, quantity in B3, fixed costs in B4, and calculated profit in B5.

A one-variable table changes either price or quantity while keeping the other assumptions fixed. A two-variable table changes both at once. The row input cell and column input cell tell Excel which model inputs to substitute; they are not simply labels for the test values.

Check the layout and selected range

A misplaced formula or an incomplete selection is one of the most common causes of blank, repeated, or unexpected results. The layout depends on whether the trial values run across a row or down a column.

One-variable table with values in a column: Put the output formula one cell above and one cell to the right of the trial-value column. Put the candidate values below the blank corner cell, select the full rectangular range containing the corner, formula, values, and output cells, then choose Data > What-If Analysis > Data Table. Fill in Column input cell.
One-variable table with values in a row: Put the output formula one cell below and one cell to the left of the trial-value row. Select the full rectangle, including the formula, test values, and result cells, then fill in Row input cell.
Two-variable table: Put the output formula in the upper-left corner. Put one set of trial values across the top row and the other set down the first column. Select the whole grid, including the formula and both sets of values, then specify the Row input cell and Column input cell.

For the profit example, suppose B5 contains the calculated profit and A2:A4 contains test prices of 15, 20, and 25. Put the output reference in B1 and select A1:B4, leaving A1 as the corner cell. The assumed layout is the model output in B5, the trial prices in A2:A4, and the table formula in B1; that reference is =B5, which returns 1,500 before Excel substitutes a test price. Set Column input cell to B2, the price input. The resulting profits should be 1,000, 1,500, and 2,000, respectively, if quantity remains 100 and fixed costs remain 500.

If the table is shifted by a row or column, Excel may still accept the selection but interpret the values and formula incorrectly. Check that the selected rectangle includes all intended result cells and that the formula occupies the proper corner position. For a two-variable table, the top-row values must correspond to the input selected as Row input cell, and the first-column values must correspond to the Column input cell.

Verify the input cell is the right kind

The input cell must be the model cell whose value the test numbers are intended to replace. In the example, changing candidate prices requires B2 as the input cell, not B5, which is the calculated output. The input should be a single editable assumption such as price, quantity, rate, or cost—not a cell containing a formula that calculates another value.

Also check that the model’s output formula actually depends on that input. If B5 instead calculates profit from a different price cell, varying B2 will not affect the result. Trace the formula references or change the input manually and confirm that the output changes before creating the table. Keep the input cell on the same worksheet as the data table; if the model is on another sheet, a practical fix is to create a local input cell on the table sheet and link the model to it.

For a two-variable table, the row input and column input must be two different model input cells, each matching the orientation of its test values. Reversing them can produce results that look plausible but answer the wrong question. Start with a small grid and check one result manually against the model before building a larger table.

Review calculation settings and refresh results

Calculation settings can make a correct table appear frozen. In Excel for Windows, open Formulas > Calculation Options and check whether the workbook is set to Automatic Except for Data Tables or Manual. With Automatic Except for Data Tables, ordinary formulas recalculate automatically but data tables do not. Choose Automatic if you want data tables included in automatic recalculation. In Manual mode, results may remain unchanged until calculation is requested.

To recalculate, use Formulas > Calculate Now. If the workbook has many formulas, data tables can take time because Excel recalculates the model for each trial value or combination. A large two-variable grid can therefore feel slow, especially when the model contains complex calculations. Reduce the grid while troubleshooting, and avoid building more scenarios than you need. Switching calculation mode can affect the whole workbook, so note the original setting before changing it if other users rely on a particular mode.

If values remain stale after checking the setting, save the workbook, close and reopen it in the Excel desktop app, and calculate again. Confirm that the formula result changes when you change an input manually. If it does not, investigate the model formula or its references rather than the data table. Also look for circular references or formulas that depend on external data that has not refreshed.

Match the symptom to the likely cause

The table is blank: Check whether the output formula is in the correct corner, whether the selection includes result cells, and whether the dialog’s input cell was entered. A formula returning an empty string can also make outputs look blank.
Every result is identical: The output may not depend on the input being varied, or the wrong input cell may have been selected. Test one candidate value manually in the model.
Results are wrong but not blank: Check row-versus-column orientation, the model’s starting assumptions, and whether a two-variable table has its row and column inputs reversed.
The table does not update: Check Formulas > Calculation Options, then use Calculate Now. Ensure the workbook is not set to Manual or Automatic Except for Data Tables.
Excel says the input cell is invalid: Select the actual single-cell assumption used by the model, not a range, output formula, or label. Confirm that it is on the same sheet as the data table.
Excel becomes slow: Reduce the number of test values, simplify expensive formulas where possible, and avoid recalculating a large table unnecessarily.

Desktop, web, and other What-If tools

These steps are for Excel for Microsoft 365 on Windows. Excel for Mac has What-If Analysis data tables in the desktop app as well, though ribbon placement and dialog details can vary with the build. Excel for the web is not the right place to create or edit a What-If Analysis data table; open the workbook in desktop Excel for that work. A workbook can still contain other kinds of tables and formulas that work in a browser.

Use a data table when you want to compare the output of one formula across one or two changing assumptions. It is not the only What-If Analysis option. Goal Seek works backward from a desired result to find one input value. Scenario Manager stores named sets of values, including scenarios that change multiple assumptions. Solver, when available and enabled, addresses optimization problems with constraints. These tools answer different questions, so a missing Data Table result is not necessarily a reason to replace the feature; first decide whether the task is sensitivity analysis, a target search, scenario comparison, or optimization.

Frequently Asked Questions

Why is my Excel data table showing the same number in every cell?
Usually the output formula does not depend on the input being varied, or the Row input cell or Column input cell points to the wrong model assumption. Change that assumption manually and check whether the output changes.

Where is the Data Table command in Excel?
In Excel for Microsoft 365 on Windows, select the prepared range and choose Data > What-If Analysis > Data Table. The command is for scenario calculations, not the formatted Excel table created from Insert > Table.

Why does my Excel data table not recalculate?
Check Formulas > Calculation Options. Automatic Except for Data Tables and Manual can prevent the table from updating automatically. Choose Automatic or use Formulas > Calculate Now, then confirm the model itself recalculates.

Can a data table change more than two inputs?
No. A What-If Analysis data table varies one or two input cells. For multiple assumptions, consider Scenario Manager or another modeling approach; Solver is intended for optimization problems rather than a simple grid of trial values.

Can I create a What-If data table in Excel for the web?
Use the Excel desktop app to create or edit this feature. If the workbook is open in a browser and the data table is not behaving as expected, open it in desktop Excel and check the layout and calculation settings there.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

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

Spotted an error in this guide? Let us know.