What If Analysis Data Table Not Working (Issues 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.


Download Practice Workbook

You can download the practice workbook from the link below.


Solutions for What If Analysis Data Table Not Working

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 versions 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 (with All 3 Features)


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


Similar Readings


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: What If Analysis in Excel – A Step by Step Complete Guide


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: How to Perform What If Analysis in Excel (3 Examples)


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

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo