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.

**Table of Contents**hide

## 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!