Eshrak Kader
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.
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.
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.
- Next, go to the Data tab >> next, click the What if Analysis drop-down >> select the Data Table option.
- 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.
Voila! Your What if Analysis displays the correct values as shown in the picture below.
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.
- 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.
- 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.
Eventually, your output should look like the image given below.
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.
- 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.
Lastly, the results should look like the image given below.
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.
As you can see, the What if Analysis returns the wrong answers because the given input rows and columns are incorrect.
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.
- 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.
Consequently, the output should look like the screenshot shown below.
Similar Readings
- How to Do What-If Analysis Using Scenario Manager in Excel
- How to Do What-If Analysis Using Goal Seek in Excel
- Performing manual what-if 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.
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.
After completing the above steps, the result should appear in the picture given below.
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.
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.
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!
