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

 

Consider the Fruit Sales Data below.

Dataset

 

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

 

Changing Calculation Options

 

Solution 1- Changing the Calculation Options to Automatic

Steps:

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

 

What If Analysis Data Table not Working by Changing Calculation Options

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

What If Analysis Data Table not Working by Changing Calculation Options

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

What If Analysis Data Table not Working by Changing Calculation Options

The What if Analysis displays the correct values.

Changing Calculation Options

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.

Checking Number Format

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

Checking Number Format

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

=E7

Here, E7 refers to the Total Cost for Avocadoes.

What If Analysis Data Table not Working by Checking Number Format

This is the output.

What If Analysis Data Table not Working by Checking Number Format

Solution 3 – Correcting the Formula in the Reference Cell

 Steps:

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

Correcting Formula

  • Enter the formula below.

=C7*D7

C7 and D7 indicate the Unit Price and the Quantity.

What If Analysis Data Table not Working by Correcting Formula

This is the output.

What If Analysis Data Table not Working by Correcting Formula

Read More: How to Use What If Analysis in Excel


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

 

Returns Wrong Value

The input rows and columns are incorrect.

Returns Wrong Value

Solution:

Steps:

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

What If Analysis Data Table not Working Returns Wrong Value

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

What If Analysis Data Table not Working Returns Wrong Value

This is the output.

What If Analysis Data Table not Working Returns Wrong Value

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.

Invalid Input Cell Reference

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.

What If Analysis Data Table not Working Returns Invalid Input Cell Reference

This is the output.

What If Analysis Data Table not Working Returns Invalid Input Cell Reference

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.

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

What If Analysis Data Table not Working Returns Blank Cells

 

Read More: Types of What-If analysis in Excel


Download Practice Workbook

Download the practice workbook here.


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo