[Fixed] Excel Data Table Input Cell Reference Is Not Valid

Reason 1 – Input Cell Is in Different Sheet

An input cell reference is not valid error message could appear when using a data table where input cell makes a reference to an outside source.

This commonly occurs if a formula is copied or transferred to another cell, or if columns or rows holding the data required in the formula are mistakenly deleted.

Solution – Insert Input Cells on the Same Sheet as Data Table

• Select the range where you want to put your data table.
• Go to the Data tab on the ribbon.
• Click on the What-If Analysis drop-down menu under the Forecast category.
• Select the Data Table.

• Select the input cells and you can see our input cells are in the same sheet where our data table is located.
• Click OK.

Reason 2 – Referencing Wrong Cell

The error can also happen if the cell reference is incorrect or relates to another cell. It can also happen if the active worksheet refers to a cell beyond the table range.

Solution – Use Correct Cell Reference in Input Cells

When you reference the data table input cell be careful while doing this. Using the correct cell reference will solve the problem.

Reason 3 – Duplicate Row & Column Input Cells

If the Row input cell and Column input cell reference the same cell, you will get the error message Input cell reference is not valid.

Solution – Contrast Row & Column Input Cells

Make sure the Row input cell and Column input cell reference are different.

Reason 4 – Inaccurate Table Selection

If you select the incorrect table range where you want to put your data table, an error message will appear.

Solution – Select Table Range Accurately

Ensure that while using What-If Analysis to generate a data table, the range of the table that you select for the data table is accurate.

Reason 5 – Uncategorized Problem

If you try all the above solutions but still get the error, then the data table cannot categorize the range or the input cells.

Solution – Reference Sheet Name While Inputting Row & Column Cell

To solve this include the sheet reference before inputting the Row input cell and Column input cell. In this case, we use =Solution1!$C$4 as our Row input cell & =Solution1!$C$5 as our Column input cell.

Things to Remember

• In a two-variable data table, both the Row input cell and the Column input cell are filled, in contrast to a one-variable data table where the Row input cell is left empty.
• You cannot reverse the action after applying the Data Table.
• It is not possible to alter or edit any of the values in the set of cells once the What-If-Analysis completes and the values are calculated.

Download Practice Workbook

Related Articles

• How to Create a Sensitivity Table in Excel
• One and Two Variables Sensitivity Analysis in Excel
• Data Table Not Working in Excel

<< Go Back to Data Table in Excel | What-If Analysis in Excel | Learn Excel