A 3-D reference is a reference to a range of cells across multiple worksheets in a workbook. For example, if you have a workbook with multiple worksheets, Sheet1, Sheet2, and Sheet3, and you want to reference a range of cells on each of those worksheets, you could use a 3-D reference.
Contents:
What Are 3-D References?
A 3-D reference in Excel is a reference to a range of cells across multiple worksheets in a workbook. It allows you to reference a range of cells on multiple worksheets using a single formula or function, making it easier to perform calculations and analysis on data that is organized in a similar way across those worksheets.
To create a 3-D reference, you use the sheet names and cell references of the ranges you want to include, separated by the : symbol. For example, if you want to reference the range A1:B2 on Sheet1, Sheet2, and Sheet3, you would use the following 3-D reference:
Sheet1:Sheet3!A1:B2This 3-D reference would include all the cells in the range A1:B2 on each of the three worksheets, Sheet1, Sheet2, and Sheet3.
Where Should 3-D References be Used?
In general, 3-D references can be useful any time you have data organized in a similar way across multiple worksheets, and you want to work with that data in a consistent way. They can save you time and effort by allowing you to reference ranges of cells on multiple worksheets with a single formula or function.
The most common use is when you have data that is organized in a similar way across multiple worksheets, and you want to perform the same calculations on each of those worksheets. For example, you might have a workbook with data from different quarters on separate worksheets, and you want to combine that data into a single worksheet to create a yearly report. Using 3-D references, you could reference the data from each quarter on its own worksheet, and then combine that data into a single worksheet to create the yearly report.
How to Create 3-D References
To create a 3-D reference in Excel, follow these steps:
Open the workbook that contains the sheets you want to include in the 3-D reference.
Select the cell where you want to enter the 3-D reference.
Type the sheet names of the sheets you want to include in the 3-D reference, separated by the : symbol. For example, if you want to include Sheet1, Sheet2, and Sheet3, you would type Sheet1:Sheet3.
Type the ! symbol after the sheet names to separate the sheet names from the cell references.
Type the cell references of the range you want to include in the 3-D reference. For example, if you want to include the range A1:B2, you would type A1:B2.
Add any functions to the formula like SUM, PRODUCT, or any of the basic functions. More advanced functions generally will not work.
Press Enter to complete the 3-D reference.
Here is an example of how the formula might look as you enter it:
=SUM(Sheet1:Sheet3!A1:B2)They can also be created a little bit easier by using the Shift key to select multiple worksheets once you've started writing your formula. Excel will understand that you wish to include any highlighted worksheet in the calculation. Like so:
Once you have entered the 3-D reference, you can use it in a formula or function to perform calculations or analysis on the range of cells across all the worksheets included in the reference.
How Moving, Inserting, and Deleting Sheets Effects a 3-D Reference
When you move sheets around in a workbook, any 3-D references that include those sheets will be automatically updated to reflect the new sheet order.
= SUM('Region 1:Region 4'!B2:B10)For example, if you have a 3-D reference to the range B2:B10 on the worksheets Region 1, Region 2, Region 3, and Region 4, you move the Region 2 to the beginning of the workbook, the 3-D reference will be updated to only include the range B2:10 on Regions 1, 3, and 4.
The 'Region 1' and 'Region 4' act as the beginning and end of the reference. Any sheet that falls between those worksheets will be included. Moving Region 2 out from between them will remove it.
This goes the same for adding in new worksheets. If we have some supplemental sales in Region 3 after the fact, we can simply drag the new worksheet into the established formula range:
This means that you can rearrange the sheets in your workbook without having to manually update any 3-D references that include those sheets. This can be convenient and save you time, especially if you have a large number of 3-D references in your workbook.
However, it's important to note that if you move a sheet to a different workbook, any 3-D references that include that sheet will no longer be valid, and you will need to update them manually. This is because 3-D references can only include sheets within the same workbook.
Example
How to Sum the Same Range of Cells Across Multiple Worksheets
Here is an example of how you might use a 3-D reference in a formula in Excel to sum across the same range of cells across multiple work sheets. In this case, calculate the quarterly sales from separate worksheets containing monthly sales numbers:
= SUM(July:September!B3:B10)In this formula, the SUM function is used to calculate the total of the values in the range B3:B10 on each of the worksheets July, August, and September. The : symbol is used to indicate that the range includes all the worksheets between July and September, and the ! symbol is used to separate the sheet names from the cell references.
To calculate fourth quarter sales, our formula would change slightly to:
= SUM(October:December!B3:B10)