# The What If Analysis Data Table is Not Working – 4 Issues and Solutions

Consider the Fruit Sales Data below.

### Issue 1 – The What if Analysis Data Table Returns the Same Value

Solution 1-Â Changing the Calculation Options to Automatic

Steps:

• Go to Formulas >>Â Â Calculation Options >> Automatic.

• Go to the Data tab >>Â  What if Analysis >> Data Table.

• Enter the cell reference in Row input cells and in Column input cells.

The What if Analysis displays the correct values.

Solution 2 – Checking the Number Format of the Reference Cell

Steps:

• Select the reference cell. Here, H5.
• Enter the Number Format as shown below. Here, \$24 is stored as text.

• Press CTRL + 1 to go to Format Cells.
• Select CurrencyÂ >> Set the Decimal places >> click OK.

• In H5, enter the cell reference for Total Cost.

`=E7`

Here, E7 refers to the Total Cost for Avocadoes.

This is the output.

Solution 3 – Correcting the Formula in the Reference Cell

Â Steps:

• Check if the formula in E7 (reference cell) is correct.

• Enter the formulaÂ below.

`=C7*D7`

C7 and D7Â indicate the Unit Price and the Quantity.

This is the output.

Read More: How to Use What If Analysis in Excel

### Issue 2 – The What If Analysis Data Table Returns Wrong Values

The input rows and columns are incorrect.

Solution:

Steps:

• Go to the Data tab >> What if Analysis >> Data Table.

• In Row input cell enter D7.
• In Column input cell enter C7.

This is the output.

Read More: How to Do Sensitivity Analysis in Excel

### Issue 3 – The What If Analysis Data Table Returns an Invalid Input Cell Reference

The error occurs if you try to enter the cell reference from a different worksheet.

Solution:

Steps:

• Copy and paste the data table into the worksheet.
• Go to the Data tab >>Â  What if Analysis >> Data Table.
• Enter the correct cell reference in Row input cells and in Column input cells. Here, D7 and C7.

This is the output.

### Issue 4 – The What If Analysis Data Table Returns Blank Cells

When working with the What if Analysis tool, it is necessary to have the data in the same worksheet. Otherwise, youâ€™ll get blank cells.

Solution:

Steps:

• Copy and paste the data table into the worksheet.
• Go to the Data tab >>Â  What if Analysis >> Data Table.

This is the output.

Read More: Types of What-If analysis in Excel

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!