What If Analysis Data Table Not Working (Issues with Solutions)

Get FREE Advanced Excel Exercises with Solutions!

Have you ever faced a scenario where What If Analysis Data Table is not working and you’re unsure how to fix it? Then, don’t worry because, in this article, you’ll learn 6 working solutions for the What If Analysis Data Table not Working.


What If Analysis Data Table Not Working: Issues with Solutions

Now, let’s consider the Fruit Sales Data shown in the B4:E13 cells. Here, we have the name of the Items, their Unit Price, the Quantity sold, and the Total Cost. So, without further delay, let’s first observe each issue and then see the solutions one by one.

Dataset

Here, we’ve used Microsoft Excel 365 version, you can use any other version according to your convenience.


Issue-1: What If Analysis Data Table Returns Same Value

Let’s start with the most common issue when using the What If Analysis tool i.e. the Data Table returns the same value. The screenshot below shows the problem.

Changing Calculation Options

What could be the reason for this error?
Well, there may be several reasons and solutions, so let’s go through them individually.

Solution-1: Changing Calculation Options to Automatic

In fairness, the solution to this issue is rather straightforward hence, follow the steps shown below.

📌 Steps:

  • Initially, go to the Formulas tab >> next, click the Calculation Options drop-down >> select the Automatic option.

If you’re wondering why Excel does this?
The answer is to prevent slowing down the data model if you have a large dataset.

What If Analysis Data Table not Working by Changing Calculation Options

  • Next, go to the Data tab >> next, click the What if Analysis drop-down >> select the Data Table option.

What If Analysis Data Table not Working by Changing Calculation Options

  • In the next step, enter the cell reference for the Row input cells and the Column input cells. For instance, the D7 and C7 cells respectively.

What If Analysis Data Table not Working by Changing Calculation Options

Voila! Your What if Analysis displays the correct values as shown in the picture below.

Changing Calculation Options

Solution-2: Checking Number Format of the Reference Cell

Another common error that drives people crazy is when we have a number stored as text. In fact, this also returns the same value in the data table.

📌 Steps:

  • Firstly, select the reference cell. Here, it is the H5 cell.
  • Next, check the Number Format as shown below. In this case, the value $24 is stored as text.

Checking Number Format

  • Now, open the Format Cells option by pressing CTRL + 1 on your keyboard.

This opens the Format Cells dialog box.

  • Following this, select the Currency option >> Set the Decimal places according to your preference >> click the OK button.

Checking Number Format

  • In turn, move to the H5 cell and enter the cell reference for the Total Cost as given below.

=E7

Here, the E7 cell refers to the Total Cost for Avocadoes.

What If Analysis Data Table not Working by Checking Number Format

Eventually, your output should look like the image given below.

What If Analysis Data Table not Working by Checking Number Format

Solution-3: Correcting Formula in the Reference Cell

Sometimes instead of formulas, you may have a number in the reference cell. This means that Excel does not understand what to calculate in the data table. So, it just copies the same value throughout the entire table.

📌 Steps:

  • Firstly, check if the formula for the E7 cell is correct since it is the reference cell for the Data Table.

Correcting Formula

  • Next, enter the correct expression in the E7 cell as given below.

=C7*D7

Here, the C7 and D7 cells indicate the Unit Price and the Quantity respectively.

What If Analysis Data Table not Working by Correcting Formula

Lastly, the results should look like the image given below.

What If Analysis Data Table not Working by Correcting Formula

Read More: How to Use What If Analysis in Excel


Issue-2: What If Analysis Data Table Returns Wrong Values

The next issue faced by people when working with the What If Analysis tool is that it returns incorrect answers. Now, the picture below depicts such a scenario.

Returns Wrong Value

As you can see, the What if Analysis returns the wrong answers because the given input rows and columns are incorrect.

Returns Wrong Value

Solution:

Luckily, this problem is very easy to fix. Now, allow me to demonstrate the solution in the steps below.

📌 Steps:

  • To begin with, go to the Data tab >> next, click the What if Analysis drop-down >> select the Data Table option.

What If Analysis Data Table not Working Returns Wrong Value

  • Secondly, for the Row input cell select the D7 cell since we have the Quantities along the row.
  • Following this, for the Column input cell choose the C7 cell since we have the Unit Price along the columns.

What If Analysis Data Table not Working Returns Wrong Value

Consequently, the output should look like the screenshot shown below.

What If Analysis Data Table not Working Returns Wrong Value

Read More: How to Do Sensitivity Analysis in Excel


Issue-3: What If Analysis Data Table Returns Invalid Input Cell Reference

The invalid input cell error occurs if you try to enter the cell reference for the rows and columns from a different worksheet.

Invalid Input Cell Reference

Solution:

By now you’ve probably figured out that we need to bring the data table over to the same spreadsheet.

📌 Steps:

  • At the very beginning, copy and paste the data table into the same worksheet.
  • Then, navigate to the Data tab >> click the What if Analysis drop-down >> choose Data Table.
  • Now, enter the proper cell reference for the Row input cells and the Column input cells. In this case, it is the D7 and C7 cells respectively.

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

After completing the above steps, the result should appear in the picture given below.

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: What If Analysis Data Table Returns Blank Cells

At times, a data table may be located on a different worksheet. However, when working with the What if Analysis tool, it is necessary to have the data on the same worksheet. Otherwise, you’ll get blank cells.

Returns Blank Cells

Solution:

Fortunately, the solution to this problem is straightforward. So, just follow along.

📌 Steps:

  • To start, copy and paste the data table into the same worksheet.
  • Next, navigate to the Data tab >> click the What if Analysis drop-down >> choose Data Table to run the analysis.

Subsequently, you should get the following output as shown below.

What If Analysis Data Table not Working Returns Blank Cells

The methods above are all possible ways to resolve the errors when using the What If Analysis tool. However, if the problem still persists as the last option, you can contact Microsoft Support. Here, you can find many Excel experts who will provide solutions for your particular problems.

Read More: Types of What-If analysis in Excel


Download Practice Workbook

You can download the practice workbook from the link below.


Conclusion

This article provides quick and easy fixes for the What If Analysis Data Table not Working. Hope you found it helpful. Please inform us in the comment section about your experience. We, the ExcelDemy team, are happy to answer your queries. Keep learning and keep growing!


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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