Excel Data Table Not Working? Fix Blank Results

If an Excel what-if Data Table shows blank results, first check that its source formula actually returns a value and that the Data Table dialog uses the correct row or column input cell. Then set calculation to automatic and recalculate; a wrong input-cell mapping or a workbook set to manual calculation is a common reason the table does not update.

First, identify which kind of data table you have

Excel uses “table” to describe two different features. A formatted Excel Table organizes a list of records and can provide filters, banded rows, and structured references. A what-if Data Table, found under Data > What-If Analysis > Data Table, recalculates a formula for different input values. Blank results in the second feature are the focus here.

If you meant a formatted table, its cells are not supposed to calculate scenarios by themselves. Check whether the cells contain formulas or data, and whether a filter is hiding rows. If you meant the what-if feature, look for a grid with trial values along its top row or left column and a formula in a corner cell. That grid is the scenario table whose results can go blank.

A what-if Data Table temporarily substitutes each trial value into one or two input cells in a model, recalculates the model, and records the result. It is useful when you want to see how a formula changes across a range of assumptions without writing a separate formula for each case.

Check the formula that supplies the results

The upper-left corner of a one-variable table, or the upper-left corner of a two-variable table, must contain a formula that refers to the model’s result. A value typed into that corner is not enough. The formula also needs to depend, directly or indirectly, on the input cell you plan to substitute.

For example, suppose B4 contains a unit price and B5 contains units sold. With the unit price in B4 and units sold in B5, put this formula in B2 to calculate revenue: =B4*B5. If B4 is 12 and B5 is 50, the displayed result is 600. If the supposed scenario input is instead a cell that B2 never uses, changing it cannot change the result.

Check the formula bar for the corner cell and the model’s input cells. Also check whether the source formula deliberately returns an empty string. A formula that returns "" looks blank even though a formula exists; the Data Table may simply be repeating that blank output. Temporarily test the model with an ordinary input value and confirm that the result cell displays a number or text before troubleshooting the scenario grid.

Match trial values to the right input cell

For a one-variable table with trial values arranged down a column, put the result formula one row above and one column to the right of the first trial value. In the Data Table dialog, set Column input cell to the model cell that those vertical values should replace; leave Row input cell empty. For values arranged across a row, use Row input cell instead and leave Column input cell empty.

For a two-variable table, put the result formula at the top-left corner, trial values for one model input across the top, and trial values for the other input down the first column. In the dialog, Row input cell corresponds to the values across the top, while Column input cell corresponds to the values down the side. Mixing them up can produce unexpected results; if the output is blank, confirm each box points to the intended model input.

To inspect or rebuild the setup:

Confirm the corner formula and the row or column of trial values are in the expected positions.
Select the complete table range, including the formula corner, all trial values, and the empty result cells.
Choose Data > What-If Analysis > Data Table.
Enter the correct Row input cell, Column input cell, or both, then select OK.

Use a single model input for a one-variable table. For a two-variable table, make sure the top-row values represent the row input cell and the first-column values represent the column input cell. The input cell should be a genuine assumption in the model, not a result cell that the formula calculates.

Make sure Excel is recalculating the table

Calculation settings can make results look frozen or incomplete. In Excel for Microsoft 365 on Windows, open Formulas > Calculation Options and choose Automatic. If the workbook is set to Manual or Automatic Except for Data Tables, a what-if table may not recalculate when you expect. “Automatic Except for Data Tables” is particularly easy to miss because ordinary formulas can update while scenario results remain stale.

After changing the setting, save the workbook and allow calculation to finish. Large tables may take time because Excel recalculates the model for each combination of inputs. A two-variable table with 30 values across and 20 down requires 600 scenario calculations, so a complex workbook can appear unresponsive before results appear. Reducing the trial values temporarily can help distinguish a slow calculation from a faulty setup.

On Mac, use the Formulas tab and its calculation options to select Automatic; labels and placement can vary slightly by Excel release. Excel for the web has more limited support for what-if analysis than the desktop application. If you need to create or repair a what-if Data Table, open the workbook in desktop Excel rather than relying on the browser version.

Inspect the selected range and the result cells

A Data Table is a single array-style result area. When creating one, select the whole rectangular range, not just the blank cells where you want answers. Include the corner formula and all input values. If you select the wrong block, Excel may place results elsewhere, omit part of the grid, or fail to set up the table as intended.

Existing what-if tables are generally treated as a unit. If selecting one result cell reveals a TABLE formula in the formula bar, do not try to repair the scenario by typing into each result cell. Recreate the table using the full correct range and input-cell mapping instead. Before replacing an existing grid, keep a copy of its trial values and layout so you can restore the setup if needed.

Also check whether cells only appear blank. Select one and look at the formula bar: a value may be present but hidden by a custom number format, white font, conditional formatting, or a narrow column. Temporarily apply a standard number format and a visible font color, and widen the column. A true blank and a value hidden by formatting need different fixes.

Rule out model errors and workbook complications

Check the source result cell and the ordinary input cells for errors such as #VALUE!, #REF!, or #DIV/0!. A Data Table cannot make an invalid model valid. Fix the underlying formula or input first. If the result formula uses lookup formulas, named ranges, or references on other worksheets, verify that those references still point to the intended cells.

Other workbook features can complicate diagnosis. External links may be unavailable, protected sheets may prevent edits to the setup, and merged cells can make a rectangular range difficult to select correctly. Volatile formulas or a large calculation chain can also make recalculation slow. For a clean test, copy the model and table to an unprotected worksheet in a separate workbook, then try a small range with simple formulas and known input values.

Do not assume that a blank table means Excel has deleted the model. Often the model is intact but the calculation mode, formula reference, selected range, or display format is the actual issue. Testing one scenario manually—entering a trial value in the model input cell and checking the result—helps isolate whether the problem is in the model or in the Data Table setup.

Rebuild a small test table before changing the model

If the original grid remains blank, create a minimal example in a spare area. Put an input value in one cell, a simple formula that uses it in another, and a few test values in a row or column. Set up a one-variable Data Table against that input. If this small test works, the feature is functioning and the original workbook’s layout, formula, or calculation load is the likely cause.

For example, a small revenue model could use B4 for price, B5 for units, and B2 for revenue. Put trial prices down a column beside a formula reference to B2, select the complete grid, and use B4 as the Column input cell. Each result should calculate revenue at that trial price while units remain unchanged. This simple test makes it easier to spot a misplaced formula corner or an incorrect input-cell choice.

When rebuilding, change only one thing at a time. First verify the ordinary result formula, then test the input mapping, then check calculation settings. This keeps you from making several changes at once and losing track of which one fixed the blank output.

Frequently Asked Questions

Why does my Excel data table show blanks instead of zero?
Excel may be returning an empty string from the source formula, or formatting may be hiding a zero. Check the corner formula and the formula bar for a result cell, then temporarily apply a standard number format and visible font color.

Why does my Data Table work only after I press Calculate?
The workbook may use manual calculation or Automatic Except for Data Tables. Choose Formulas > Calculation Options > Automatic, then allow Excel to recalculate. Large scenario grids can take longer than ordinary formulas.

What is the difference between Row input cell and Column input cell?
Row input cell is the model input replaced by trial values across the top of the grid. Column input cell is replaced by values down the first column. For a one-variable table, fill in only the one that matches the direction of your trial values.

Can I type directly into a blank Data Table result cell?
That is not a reliable way to repair the table. The results belong to a what-if table range. Correct the source formula or input-cell settings and recreate the table across the full range rather than entering separate values into its result cells.

Can I create a what-if Data Table in Excel for the web?
For dependable creation and repair, use desktop Excel. The web version does not provide the same full what-if analysis workflow, so a workbook with an existing table may need to be opened in the desktop application to change its setup.

When another approach is a better fit

What-if Data Tables are convenient for compact sensitivity grids, but they are not the only way to compare scenarios. If you need a small number of named cases, Scenario Manager may be easier to maintain. If you need a transparent report or a large set of calculations, ordinary formulas copied down a worksheet can make each assumption and result easier to inspect. For repeated or automated analysis, Power Query or a purpose-built model may suit the task better.

Choose the simplest method that keeps assumptions visible and results auditable. When a Data Table is appropriate, the reliable troubleshooting order is straightforward: confirm the source formula, confirm the trial-value orientation and input cell, select the full table range, switch calculation to Automatic, and check that formatting is not concealing the output.

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.