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.

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 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.

Read More: How to Get Sensitivity Report from Solver in Excel

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.

Download Practice Workbook

Download the practice workbook here.

Related Articles

• How to Delete What If Analysis in Excel
• What-If Analysis in Excel with Example
• How to Perform Sensitivity Analysis for Capital Budgeting in Excel
• Sensitivity Analysis for NPV in Excel
• How to Build a Sensitivity Analysis Table in Excel
• How to Do IRR Sensitivity Analysis in Excel

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