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.

Check for Non-Contiguous Excel Worksheets When 3D Reference Is Not Working

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

Check for Non-Contiguous Excel Worksheets When 3D Reference Is Not Working

  • Now, press Enter.

Check for Non-Contiguous Excel Worksheets When 3D Reference Is Not Working

  • 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.

Check for Non-Contiguous Excel Worksheets When 3D Reference Is Not Working

  • 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.

3D Reference Does Not Work If Any Cell in Reference Formula Contains Error Value

  • 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.

3D Reference Does Not Work If Any Cell in Reference Formula Contains Error Value

  • 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.

Data Pattern Is Not Same across All Sheets in Excel

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.

Data Pattern Is Not Same across All Sheets in Excel

  • 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.

Data Pattern Is Not Same across All Sheets in Excel

  • 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


Download Practice Workbook

You can download the practice workbook from here.


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.


Related Articles


<< Go Back to 3D Reference | Cell Reference in Excel | Excel Formulas | 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.
Mukesh Dipto
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo