If an Excel what-if data table is not calculating, first check Formulas > Calculation Options and choose Automatic; the setting Automatic Except for Data Tables deliberately leaves these results unchanged. Then confirm that the table’s formula and row or column input cells are set up correctly, and recalculate the workbook.
First, identify which kind of table you mean
Excel users call two different features a data table. A formatted Excel table is a range of records with filter buttons, banded rows, and often a name such as Table1. A what-if data table is a calculation tool that substitutes trial values into one or two input cells and records the resulting formula outputs. It is created from Data > What-If Analysis > Data Table in Excel for Microsoft 365 on Windows.
The what-if tool is the subject of this troubleshooting guide. If you mean a formatted table, a formula that does not update there is more likely to involve calculation mode, a formula entered as text, or a structured reference—not the What-If Analysis command. Identifying the feature matters because the fixes are different.
How a what-if data table works
A data table is a compact sensitivity analysis: Excel temporarily puts each trial value into a model’s input cell, recalculates the formula that depends on it, and displays the result. A one-variable table varies one input. A two-variable table varies two inputs and displays their combined effects. It does not replace the model formula or permanently change the input cell.
For example, suppose a simple profit model has units in B2, selling price in B3, variable cost per unit in B4, fixed costs in B5, and profit in B6. With those cells arranged as described, enter this formula in B6:
=B2*(B3-B4)-B5
If B2 is 100, B3 is 20, B4 is 8, and B5 is 500, the result is 700: 100 multiplied by the 12 difference between price and variable cost, minus 500 in fixed costs. A data table can show how that profit changes for other unit volumes, prices, or combinations of both.
Check calculation mode and recalculate
The most common reason a data table appears frozen is the workbook calculation setting. In Excel for Microsoft 365 on Windows, go to Formulas > Calculation Options and select Automatic. The option Automatic Except for Data Tables calculates ordinary formulas automatically but skips what-if data tables, which can be useful in large workbooks because tables may require many repeated calculations. Manual mode also delays recalculation.
After changing the setting, recalculate. On Windows, F9 calculates formulas in all open workbooks, while Shift+F9 calculates the active worksheet. Ctrl+Alt+F9 forces a full calculation, and Ctrl+Alt+Shift+F9 rebuilds dependencies before calculating. On a Mac, function keys may require the Fn key or may be assigned to macOS; use the calculation commands on the Formulas tab if a shortcut does not work. Calculation settings can affect other open workbooks, so consider restoring the original setting if the workbook is large or recalculating everything is slow.
Automatic calculation is not a guarantee that every displayed result will change immediately: if the model’s input value has not changed, its output may correctly remain the same. Also check whether calculation is set to manual in a workbook opened earlier in the same Excel session. Save the workbook after confirming the desired setting and results.
Verify the one-variable table layout
For a one-variable table that tests units, put a reference to the model’s output just above the results column, and place the trial unit counts down the column immediately to its left. For example, put =B6 in D2, put trial values such as 50, 100, 150, 200, and 250 in C3:C7, and select C2:D7. The formula in D2 must link to the output being analyzed; it should not be a separate calculation that bypasses the model.
With that layout, open Data > What-If Analysis > Data Table. Leave Row input cell blank and set Column input cell to B2, the model’s units input. Select OK. Excel substitutes each value from C3:C7 into B2 and writes the corresponding profit in D3:D7. At 100 units, the example model returns 700, assuming the other inputs remain as above.
For values arranged horizontally, the trial values go across a row, and the Row input cell should point to the model input being varied. The table’s output formula and the row of trial values must be placed in the correct relationship before opening the dialog. Selecting a range that omits the formula or one of the trial values commonly produces blanks or unexpected results.
Set up a two-variable table correctly
A two-variable table varies one model input across the top and another down the left side. For the profit example, put =B6 in C2, candidate prices across D2:H2, and candidate unit counts down C3:C7. Select C2:H7 and open Data > What-If Analysis > Data Table. Set Row input cell to B3, because prices are across the top, and Column input cell to B2, because units are down the side.
Excel fills the inner grid with the profit for each price-and-volume combination. The row and column input references are easy to reverse accidentally. If the results look plausible but correspond to the wrong scenarios, check the orientation: top-row trial values map to the row input cell; first-column trial values map to the column input cell. Both input cells must be cells in the model that the output formula actually uses.
Inspect the formula, references, and worksheet
A correctly arranged table still cannot produce useful results if its output formula or input references are wrong. Check the following before rebuilding it:
- The output formula is linked to the model. The cell at the corner of the table should refer to the result you want to analyze, such as profit, payment, or total cost.
- The chosen input cell drives that result. If the output formula does not depend on B2, changing B2 will not meaningfully affect the result.
- The row and column assignments match the layout. Values across the top use the row input cell; values down the left use the column input cell.
- The input cell is on the same worksheet as the data table. If the model is elsewhere, consider placing a linked input or model arrangement on the table’s worksheet, then verify that the model still responds as intended.
- The trial values are real numbers or dates as intended. Values imported or entered as text may not behave like numeric inputs. Check number formats and, where appropriate, convert text values to numbers.
Also check for errors in the underlying model, such as #VALUE!, #REF!, or a division-by-zero error. A data table repeats the model’s calculation; it cannot repair a broken formula. If the output is identical for every trial, test the model manually by changing the input cell temporarily, then undo the change. If the output does not respond, investigate the model’s dependencies rather than the data table.
Common symptoms and what they usually mean
- Every result is blank or stale: Check for Automatic Except for Data Tables or Manual calculation, and confirm that the selected range includes the corner formula and trial values.
- Every result is the same: Verify that the selected input cell is actually used by the output formula. Also check whether the scenario values differ as expected.
- The results appear in the wrong direction: Recheck row versus column input cells and the position of the trial values.
- The table shows an error: Test the model formula with the same input. The error may come from a scenario that makes the underlying calculation invalid, such as a zero denominator.
- A result cell cannot be edited normally: Data table output cells are managed as a group. Change the source layout or recreate the table instead of typing into one result cell.
Another frequent mix-up is expecting a what-if table to behave like a normal formula grid. The result cells are generated by the data-table operation, not by copying a conventional formula down or across. Avoid overwriting individual results; edit the model or scenario values, then rerun the Data Table command if the layout needs repair.
When a data table is slow or unsuitable
Each scenario requires Excel to recalculate the relevant model, so a two-variable table with hundreds of row-and-column combinations can be expensive—especially if the workbook contains many complex formulas or other data tables. Reduce the number of scenarios, simplify dependent calculations, or use Automatic Except for Data Tables while editing, then switch back or calculate deliberately when you need refreshed results. Be clear about the setting before sharing a workbook so another person does not mistake intentionally stale results for current ones.
For a small number of scenarios, direct formulas or separate model copies may be easier to audit. For larger analyses, a PivotTable, a chart based on a prepared scenario range, or a purpose-built formula model may be more suitable, depending on the data and question. Those options are not identical replacements: a what-if data table is convenient when you want Excel to substitute values into one or two specific model inputs and return the same output formula for each combination.
Excel for Mac and Excel for the web
The instructions above target Excel for Microsoft 365 on Windows. Excel for Mac also provides what-if data tables and calculation options, though ribbon labels, dialog layout, and keyboard shortcuts can vary by release and keyboard configuration. Look for the Data tab’s What-If Analysis or Data Table command and the Formulas tab’s calculation controls. If a shortcut behaves differently, use the ribbon command rather than assuming the Windows key combination applies.
Excel for the web may not expose every desktop What-If Analysis command or editing capability. If you cannot create or repair the data table in the browser, open the workbook in the desktop app. Existing workbook results may also depend on when the desktop version last recalculated them, so verify them in Excel before relying on a scenario comparison.
Frequently Asked Questions
Why does my Excel data table show the same number in every cell?
Usually, the row or column input cell is incorrect, the table is oriented differently than expected, or the output formula does not depend on the input being varied. Test the model by changing that input temporarily and checking whether the output changes.
How do I force an Excel data table to recalculate?
First choose Formulas > Calculation Options > Automatic. Then press F9 on Windows or use the calculation command on the Formulas tab. If results remain stale, verify the table’s input cells and formula layout.
What is the difference between an Excel table and a data table?
An Excel table is a structured, filterable range of records. A what-if data table is an analysis tool that substitutes trial values into model input cells and records calculated outputs. The Data tab’s What-If Analysis command creates the latter.
Can an Excel data table use more than two input cells?
No. What-if data tables support one or two variable input cells. To analyze more changing assumptions, use another modeling approach, such as separate scenarios or a formula-based analysis.
Why can’t I edit one result inside the data table?
The output range is generated by the data-table operation rather than being a set of independent formulas. Change the trial values, model, or table setup and recalculate; do not treat an individual result as an ordinary cell.
Related
- Excel Circular Reference Cannot Be Listed? How to Find It
- Excel Circular Reference Greyed Out? Find the Cause
- Excel Named Range Scope Greyed Out? How to Change It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Insert Page Break Not Working? Fixes That HelpIf Excel’s Insert Page Break command is unavailable or the break does not appear where you expect, first…
- Excel New Line in Cell: Add Line Breaks and Fix ThemTo start a new line inside an Excel cell, edit the cell, place the cursor where the break…
- Excel Error Symbols: What They Mean and How to Fix ThemExcel’s error symbols are values such as #VALUE! and #DIV/0! that explain why a formula cannot return a…
- Excel FILTER Spill Error: Causes and FixesA #SPILL! error in an Excel FILTER formula means Excel found results to return but cannot place the…
- Excel Cell Not Showing All Text? Fix ItIf an Excel cell is not showing all its text, first select it and choose Home > Wrap…
- Remove a Circular Reference Error in Excel QuicklyTo remove a circular reference in Excel, select Formulas > Error Checking > Circular References, open the listed…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.