# 3D Reference Not Working in Excel (3 Reasons & Solutions)

Get FREE Advanced Excel Exercises with Solutions!

This article will show you why 3D reference in Excel is not working properly. Due to the orientation of data or the presence of any error value in cells, a 3D reference does not always work effectively when dealing with a dataset. Weâ€™ll discuss the possible reasons for this issue. After that, I will also propose solutions to these problems.

## What Is a 3D Reference in Excel?

A 3D reference in Excel links to the same cell or set of cells across several worksheets. Itâ€™s a quick and easy way to combine data from several worksheets that have the same format. Instead of using Excelâ€™s Consolidate feature, we can leverage 3D references in Excel.

## 3 Reasons with Solutions If 3D Reference Is Not Working in Excel

In this article, we will discuss 3 reasons for the problem of a 3D reference not working in Excel. First, we will explain the reasons for the problems to you. Then we will show you how to solve the problem.

### Reason-1: Check for Non-Contiguous Excel Worksheets When 3D Reference Is Not Working

A 3D reference does not work properly if our Excel worksheets are not contiguous. More precisely, the sequence of worksheets in our reference formula does not match the sequence of worksheets in our workbook. We will illustrate this method in the following steps.

STEPS:

• To begin with, in cell C5 of the sheet named Total, we will calculate the total sales amount of John from the years 2019 to 2021.
• In addition, we can see that the worksheets are not contiguous.

• Furthermore, go to the sheet named Total.
• Then, type the following formula in that cell:
`=SUM('2019:2021'!C5)`

• Now, press Enter.

• The formula gives output but itâ€™s not correct. The above formula skips the value of cell C5 in worksheet 2020. It happens because the formula takes the range 2019:2021 and the sheet 2020 is not between the two worksheets.

Solution:

To solve this problem, we need to follow the below steps.

STEPS:

• First, make the worksheets contiguous as we use in the formula. To do this drag the worksheet 2020 in between worksheet 2019 and 2021.

• Next, go to the worksheet Total. We can see that the value in cell C5 modifies automatically.

• After that, drag the Fill Handle tool from cell C5 to C8.
• Finally, we get results like the following image.

### Reason-2: 3D Reference Does Not Work If Any Cell in Reference Formula Contains Error Value

Another reason for 3D reference not working in Excel is the presence of one or multiple error values in the working dataset. Letâ€™s see this reason in different steps.

STEPS:

• Firstly, we can see a #DIV/0! error in cell C5 of the worksheet 2020.

• Secondly, go to the worksheet named Total.
• Thirdly, in the following image, we can see that the reference formula used in cell C5 returns #DIV/0! Error.

• So, a 3D reference does not work properly if any cell value in the formula range contains any type of error value.

Solution:

Follow the below steps to solve this.

STEPS:

• To solve this problem go to cell C5 of the worksheet 2020 which contains the error value.
• Then, type a valid value in that cell like the following image.

• After that, go back to the worksheet Total.
• Lastly, we can see our desired result in the following image.

### Reason-3: Data Pattern Is Not Same Across All Sheets in Excel

Most of the time, a 3D reference formula will return a result. But that might not be correct or our desired result. It can happen due to the difference in data patterns among the reference worksheets. Suppose from the following images of a dataset we want to calculate the total sales amount of John in another worksheet. If we notice we will see that the data pattern is not the same in all the worksheets. To do this, we will create a reference for these worksheets in another worksheet.

Letâ€™s see the steps to face this problem.

STEPS:

• In the beginning, go to the worksheet named Total.
• Insert the following formula in that cell:
`=SUM('2019:2021'!C5)`
• Press Enter.
• So, we get the value of \$44500 in cell C5.

• But, it is not our desired value. Because it counts cell C5 from worksheet 2020 which is the sales amount for Rick.

Solution:

By following the steps below, we can solve this problem.

STEPS:

• First, arrange the pattern of salespeople like the other two worksheets.

• Next, go to the sheet named Total.
• Then, in cell C5 we can see that the value updates automatically from \$44500 to \$47500. The updated value is our desired value whereas the previous value was wrong.

• After that, drag the Fill Handle tool from cell C5 to C8.
• Finally, we get results like the following image.

Read More: How to Use SUM and 3D Reference in Excel

## Conclusion

In conclusion, this tutorial will give you a clear idea of why 3D reference is not working in Excel. To test your skills, use the practice worksheet included in this article. Please leave a comment below if you have any questions.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF