How to Use SUM and 3D Reference in Excel (Detailed Analysis)

Sometimes you have several worksheets to work on and finally, you need to have the SUM of a certain value from different worksheets. In that case, the 3D reference will help to solve the issue and get the required result as a summary. This article will show how to use SUM and a 3D reference in Excel. I hope you find this article very interesting and gain knowledge about the topic.

What Is 3D Reference in Excel?

3D reference helps us to refer same cells or ranges from several worksheets. When you work with several worksheets and try to create a summary from these, you can use 3D reference there. It will provide you with a dynamic solution for several worksheets. For example, You have sales details in several worksheets but you want to get the grand sum of these sales amounts in another worksheet. In this case, you can do this manually but it takes a lot of time rather you can the 3D reference using the worksheet name and cell number. It will eventually provide you with the required results. If you include another worksheet in the middle of the given worksheet name, it will take that value also. So, overall it will give you a dynamic solution.

How to Create 3D Reference in Excel

To create a 3D reference in Excel, you need to have multiple worksheets with different data. Here, we take several worksheets that include student marks in different subjects and in different exams. If you want to add numbers for a certain subject, you can easily use the 3D reference.

First, we need to create a dataset for the quarter 1 exam where we include marks in several subjects.

Next, we would like to create 2nd worksheet where we will include marks for half yearly exam.

Then, we will create the 3rd worksheet which represents marks for the quarter 3 exam.

Now, we would like to create a 3D reference and calculate the total number of certain subjects using the SUM function. To do this, you need to create a new worksheet where you would like to put the total marks. In order to calculate the total marks in physics in those three exams, first, select cell C5 and write down the following formula.

=SUM('Quarter 1:Quarter 2'!C5)

Then, press Enter to apply the formula.

After that, drag the Fill Handle icon down the column. As a result, we will get the total marks in other subjects also. That’s how you can create a 3D reference in Excel effectively.

You can calculate the sum by selecting it individually like the following formula.

=SUM('Quarter 1'!C5,'Half Yearly'!C5,'Quarter 2'!C5)

But when you large amount of worksheets, then, it will become tedious. That’s why the 3D reference gives us a dynamic solution as well as saves lots of time.

Steps to Use SUM and 3D Reference in Excel

To use SUM and 3D references in Excel, we need to have several worksheets. From there, you need to pick up certain data. Then, using the SUM function and the 3D reference in a new worksheet, we would like to calculate the total amount of a certain thing. It can be the sales amount or the total profit of several worksheets. You can easily do it by using the 3D reference. To understand the process clearly, we take several worksheets including data. Then, we would like to like the 3D reference. Follow the steps carefully.

Step

First, we would like to create a worksheet for sales details for January

Then, create the second worksheet including sales details for March.

After that, we take a dataset that includes the sales amount, region, and profit for May.

Next, our last dataset includes the sales amount, region, and profit for July.

After that, take a new worksheet.
In this worksheet, you would like to calculate the grand total sales amount and profit.
In the previous four worksheets, we included the total sales amount and total profit of that certain month.
Now, we would like to calculate the grand total sales amount for all the worksheets.
In this case, the 3D reference can play a vital part.
To do this, select cell C4 in the Summary worksheet.
Then, write down the following formula.

=SUM('Jan2022:July2022'!H4)

Then, press Enter to apply the formula.

After that, to calculate the grand total profit, we need to select cell C5.
Then, write down the following formula.

=SUM('Jan2022:July2022'!H5)

Finally, press Enter to apply the formula.

How to Include a New Worksheet in Existing 3D Reference in Excel

After using the 3D formula to get the grand total, we may need to include a new worksheet in our Excel workbook. There is no need to worry about this. If you insert your new worksheet in the middle of the first and last worksheet reference, then, the 3D formula will take the new worksheet into consideration and work effectively. Follow the steps carefully.

Steps

First, you need to create a dataset that includes the sales amount, region, and profit for April.

As we include the new worksheet in the medium of the two given references, the 3D reference can easily take this into account and calculate effectively.
So, you will see a change of values in the summary worksheet where the grand total sales amount increases from $83381 to $106706.
Then, the grand total profit increases from $22926 to $30854.
So, you can easily assume that when you include a new worksheet in the middle of the two references, the 3D reference will take this new amount automatically and modify the result accordingly.

How 3D Reference Changes While Inserting, Deleting or Moving Sheets in Excel

In this section, we would like to discuss the changes that occur in 3D references while inserting, deleting or moving sheets. You may need to alter the worksheet for some valid reason, in this case, how will the 3D reference react that is the main focus. Every 3D reference has a starting and ending sheet. When we change the ending sheet reference, the 3D formula will change eventually.

1. Inserting a Sheet

In our previous example, we have Jan2022 as starting sheet and July2022 as the ending sheet. If we include or insert a new worksheet in the middle of the starting and ending sheet, the 3D reference will take this value and add it to the calculation. But if you insert a worksheet outside the end sheet, the 3D reference won’t make a change in the calculation. Our starting worksheet is Jan2022 and the ending worksheet is July2022. Now, we would like to insert a new worksheet outside the end sheet.

The result of the grand total will be the same until you change the reference or move the new sheet in between the previous start and end sheet.

2. Deleting a Sheet

Next, when you delete any worksheet, the 3D reference will estimate whether the worksheet is within the start and end sheet or not. If it is in the middle of the start and end sheet, then the whole calculation will change. Otherwise, it will remain the same. In our previous example, if we delete the sales details for May2022, the 3D reference will work for it.

If we look at the summary, you will see the change in the grand total sales amount from $106706 to $84709, and the grand total profit changes from $30854 to $25721.

3. Changing the Sequence of Sheets

Next, when we move the worksheet to a different place, the 3D reference will change accordingly. For example, if you shift a worksheet after that end sheet, the 3D reference will remove the sheet value and do the recalculation. Again if you move a worksheet that is outside of the given range to the middle of the start and end sheet, the 3D reference will add this worksheet value and do the required recalculation.

Here, we move the Sept2022 to the middle of the given range.

Now, if you see the summary, you will get the change in grand total value from the previous one. That means the 3D reference takes the new worksheet in its calculation. So, you will see a change of values in the summary worksheet where the grand total sales amount increases from $83381 to $106706.Then, the grand total profit increases from $22926 to $30854.

Then, if you delete any end sheet, the 3D reference will take the preceding sheet and do the calculation accordingly. That means 3D reference will provide a dynamic solution.

Things to Remember

To use the 3D reference effectively, you have to provide the start and end worksheet reference effectively.
While moving any worksheet, you have to pay special attention because if you move the worksheet outside of the end sheet, the 3D reference will neglect it while doing the calculation.

Download Practice Workbook

Download the practice workbook below

Use SUM and 3D Reference.xlsx

Conclusion

We have shown how to use SUM and a 3D reference in Excel effectively. In this article, we have also discussed the way of including a new worksheet after applying the 3D reference formula. The change of 3D reference after inserting, deleting, or moving sheets was further discussed. So, I hope you get the complete overview of 3D references in Excel.

<< Go Back to 3D Reference | Cell Reference in Excel | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!