# How to Use the SUM Function and a 3D Reference in Excel – A Guide

## How to Create a 3D Reference in Excel

The workbook contains students’ marks in different subjects and in different exams.

• Create a dataset for the quarter 1 exam to include marks in different subjects.

• Create a second worksheet to include marks in the half yearly exam.

• In the third worksheet, enter marks in the quarter 3 exam.

• Create a new worksheet to see the total marks in Physics: select C5 and use the following formula.
`=SUM('Quarter 1:Quarter 2'!C5)`

• Press Enter.

• Drag down the Fill Handle to see the result in the rest of the cells.

You can also calculate the sum by selecting marks individually:

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

## Use the SUM Function and a 3D Reference in Excel

Step

• Create a worksheet and enter sales details in January.

• In the second worksheet include sales details in March.

• Create a dataset including sales amount, region, and profit in May.

• Create a dataset including the sales amount, region, and profit in July.

• Open a new worksheet (Summary) to calculate the grand total sales amount and profit.
• Select C4 and enter the following formula.
`=SUM('Jan2022:July2022'!H4)`

• Press Enter.

• To calculate the grand total profit, select C5.
• Enter the following formula.
`=SUM('Jan2022:July2022'!H5)`

• Press Enter.

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

Steps

• Create a dataset including the sales amount, region, and profit in April.

You will see a change of values in the summary worksheet: the grand total sales amount increases from \$83381 to \$106706 and the grand total profit increases from \$22926 to \$30854. (the 3D reference automatically calculates the new data).

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

### 1. Inserting a Sheet

• Insert a new worksheet after the last sheet.

The grand total will be the same. You need to change the reference or move the new sheet.

### 2. Deleting a Sheet

If you delete a worksheet between the first and the last sheet, the 3D reference recalculate.

May 2022 was deleted: the grand total sales amount changed from \$106706 to \$84709, and the grand total profit changed from \$30854 to \$25721.

### 3. Changing the Sequence of Sheets

If you move a worksheet between the first and the last sheet, the 3D reference recalculate.

Sept 2022 was moved: the grand total sales amount increased from \$83381 to \$106706 and the grand total profit increased from \$22926 to \$30854.

## Related Article

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

Get FREE Advanced Excel Exercises with Solutions!
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF