The “input cell reference is not valid” message usually means Excel cannot use the cell you entered as a What-If Analysis data table input—often because it is not a single valid cell on the same worksheet as the table. Select the correct model input cell, not the result cell or the data table range, then enter it in the matching Row input cell or Column input cell box.
What Excel means by a data table
In Excel, a data table is a What-If Analysis tool that recalculates a formula using one or two sets of alternative input values. It is not the same thing as a formatted Excel table created with Insert > Table. A What-If Analysis data table temporarily substitutes values into one or two model cells and displays the formula’s results for each combination.
For example, a sales model might calculate revenue from units sold and price. A data table can show how revenue changes at several possible unit counts while keeping price fixed, or show results for combinations of units and prices. The input cell is the cell in the model that Excel temporarily changes during those calculations.
This distinction matters because the data table does not take a range of values as its input cell. It expects a reference to one cell in the model. The possible test values go in the worksheet grid beside or above the formula, while the Row input cell and Column input cell boxes identify the model cells to substitute.
Fix the invalid input cell reference
On Excel for Microsoft 365 for Windows, check the layout and then open Data > What-If Analysis > Data Table. In the dialog, enter a single model cell in the appropriate input box. For a one-variable table with test values arranged vertically, use Column input cell. For test values arranged horizontally, use Row input cell. Leave the unused box blank.
- Confirm that the cell you plan to use is a single cell, such as B2, rather than a range such as B2:B3.
- Make sure that cell is on the same worksheet as the data table. If the model input is on another sheet, place a suitable input cell and model formula on the table sheet, or reorganize the model so the data table and input cell are together.
- Choose the cell that the model formula actually depends on, such as the units, rate, or price assumption—not the formula’s output cell.
- Arrange the test values and formula in the correct data-table layout, select the entire table area, and reopen the Data Table dialog.
- Enter the reference in the box matching the direction of the test values, then choose OK.
If the reference is still rejected, click the input box and select the cell directly on the worksheet rather than typing a complicated reference. Check for merged cells, an accidental multi-cell selection, or a reference to a different worksheet. A cell that is valid in a normal formula may still be unsuitable for a data table if it is not a permitted input cell for that table’s location.
How the row and column input cells work
The box names describe where the trial values are laid out, not the direction in which Excel displays the final results. Use Row input cell when the alternative values run across a row. Excel substitutes each value into the selected model input cell. Use Column input cell when alternatives run down a column. In a two-variable table, both boxes are used: one model cell is substituted with the top-row values and the other with the first-column values.
Here is a one-variable example. Assume B2 contains units sold and currently holds 100, B3 contains price and holds 10, and B4 calculates revenue. With units in B2, price in B3, and revenue in B4, the formula in B4 is =B2*B3. It returns 1,000. Put =B4 in D2, leave C2 blank, and enter 50, 100, and 150 in C3:C5. Select C2:D5, open the Data Table dialog, and set Column input cell to B2. The output cells in D3:D5 should show 500, 1,000, and 1,500, respectively, because the price remains 10 while the trial unit counts replace B2.
For a horizontal version, place the formula in the top-left position of the table layout, put trial values across a row, select the complete range, and set Row input cell to B2. The key is to keep the layout consistent: the values are the scenarios, while the input cell is the existing model cell that each scenario value temporarily replaces.
Common causes beyond a typo
A reference can look plausible yet fail because the table or model is arranged incorrectly. Check these common issues before rebuilding the workbook:
- Wrong kind of cell: The input box points to a calculated result or an unrelated cell instead of a model assumption. Choose the cell whose value the formula uses as an input.
- Range instead of cell: A reference such as B2:B5 identifies several cells. A data table input field needs one cell, for example B2.
- Different worksheet: The model input and data table are on separate sheets. Put the relevant model and table on the same sheet for the data-table operation.
- Wrong input box: Values run vertically but the reference was entered as Row input cell, or values run horizontally but it was entered as Column input cell. Match the box to the orientation of the scenario values.
- Incorrect selection: The selected range omits the formula, the test values, or the cells where results should appear. Select the whole data-table block, including the formula and scenario values.
- Confusing a table with a data table: A structured Excel table made with Insert > Table does not use the What-If Analysis input-cell dialog. Use Data > What-If Analysis > Data Table for scenario substitution.
Not every problem produces the exact invalid-reference message. If Excel accepts the reference but every result is identical, the chosen input cell may not affect the formula being evaluated, or the formula in the table may not point to the intended model result. Check the dependency chain: changing the model input manually should change the formula result before you expect a data table to show different outcomes.
Check the table layout before entering references
For a one-variable table, there is one formula and one list of trial values. In a vertical layout, the trial values appear below the formula’s row and the results appear beside them. In a horizontal layout, values extend across the row and results appear in the row below. The formula cell should refer to the result you want to compare, such as total revenue or monthly payment—not to the input cell itself.
A two-variable table adds a second list of values. Typically, one set runs across the top row and the other runs down the first column, with the formula in the corner above the result grid. Excel substitutes the top-row values into the Row input cell and the first-column values into the Column input cell. If those roles are reversed, the table may calculate an unintended set of scenarios even if the references are valid.
Before applying the data table, test the model with ordinary values. Change one input cell manually and verify that the output formula updates. Then restore the original assumption and build the table. This simple check separates a bad input reference from a formula that is disconnected from the model.
Excel version and platform considerations
In Excel for Microsoft 365 on Windows, create the feature from Data > What-If Analysis > Data Table. Excel for Mac is also a desktop Excel application, but ribbon and dialog placement can vary with the version; look on the Data tab for What-If Analysis and Data Table. The cell-layout rules are the same. If the command is unavailable, confirm that you are using desktop Excel and that the workbook is open in an edition that supports What-If Analysis data tables.
Excel for the web does not provide the full desktop authoring experience for What-If Analysis data tables. If you need to create or repair one, open the workbook in the Excel desktop app. A workbook may contain results prepared in desktop Excel, but web editing should not be treated as an equivalent way to build the data table.
Data tables can also add calculation work. A two-variable table with many row and column values requires Excel to evaluate many combinations, so a large grid can slow recalculation. Keep the scenario grid to the cases you need. If calculation is set to Manual or Automatic Except for Data Tables, results may not refresh when expected; review Formulas > Calculation Options and recalculate as appropriate.
Alternatives when a data table is not the right tool
If the data-table dialog continues to be awkward, choose an alternative based on the question you need answered. Goal Seek is useful when you want Excel to find one input value that produces a chosen result. Open Data > What-If Analysis > Goal Seek, specify the formula cell, target value, and the single cell Excel may change. Scenario Manager can store and compare named groups of assumptions, though it is less convenient for a large grid of combinations.
For a small number of scenarios, you can also calculate results in ordinary worksheet formulas by placing the assumptions in separate cells and referring to them explicitly. That approach is easier to audit when every case needs its own visible inputs, but it requires more setup and does not automatically substitute values into a model input cell like a data table does. Avoid replacing a data table with a regular Excel table unless you specifically want structured records rather than what-if calculations.
Frequently Asked Questions
Why does Excel say the data table input cell is not valid?
Usually the reference is not a single suitable model cell, the cell is on another worksheet, or the reference was entered in the wrong input box. Check the data-table layout and select the model input cell directly.
Should I use Row input cell or Column input cell?
Use Row input cell when the trial values are arranged across a row. Use Column input cell when they are arranged down a column. A two-variable table uses both boxes.
Can the input cell be on another worksheet?
Keep the data table and its input cell on the same worksheet. If the assumption is currently elsewhere, reorganize or link the model so the relevant input used by the table is on the table’s sheet.
Why do all the data-table results show the same value?
The selected input may not affect the formula in the table, or the corner formula may point to the wrong result. Change the input manually first and verify that the model output changes.
Can I create a What-If Analysis data table in Excel for the web?
Use desktop Excel to create or repair What-If Analysis data tables. Excel for the web does not offer the same full authoring support for this feature.
Related
- Excel Circular Reference Greyed Out? Find the Cause
- Excel Named Range Scope Greyed Out? How to Change It
- Excel Cannot Insert Row: Causes and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- IFERROR Not Working in Excel? Find and Fix the CauseIf IFERROR is not working in Excel, first check that the formula is written as =IFERROR(your_formula, value_to_show_if_an_error_occurs) and…
- Excel Find Not Working? Fix Search Problems in a WorksheetIf Excel’s Find feature is not working, first open Home > Find & Select > Find, expand the…
- Excel Not Inserting Rows? Fix the Most Common CausesIf Excel is not inserting rows, first select the worksheet row header where the new row should go,…
- VLOOKUP Not Working? Fix Common Excel Lookup ErrorsIf VLOOKUP is not working properly, start by using an exact match with FALSE, checking that the lookup…
- Excel Print Area: Center It on a Page and Fix LayoutTo center a print area on a page in Excel for Microsoft 365 on Windows, open Page Layout…
- Excel Filter Not Working? Common Causes and Fixes GuideIf a filter in Excel is not working, first check that it covers the entire data range, that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.