3D references are special use of Excel ranges that refer to the same cell or range through multiple sheets. The third dimension, being across sheets, can be seen like a z-axis. 3D references are great for consolidating data from similarly built sheets. For example, annual reports can be created easily by grouping data into months, each kept on a separate worksheet. Data from all sheets can be used with ease with 3D references. A good practice when utilizing this feature is sticking with a similar structure across all worksheets.
How to create a 3D reference
The colon (:) character used between cells to create ranges, can also be used between sheets. Let’s see how this works with an example. Below, Jan:Dec!C2 denotes all cells that correspond to C2 across sheets “Jan” and “Dec”. Sheets named “Jan” and “Dec” act as the start and end points for the range of sheets. The range of sheets is the actual sheets that you can see on the sheets bar.
Working with ranges follows a similar approach, using the colon character. Jan:Dec!C2:E9 reference points to all C2:E9 ranges in all sheets from “Jan” to “Dec”.
Now, let’s see how a 3D reference can be used in a formula. Below is a formula that can sum values from identical monthly sheets:
This formula basically does the same thing as the two formulas below, but it’s far easier to write and read.
Here is the list of formulas you can use with 3-D references:
|AVERAGE||Calculates average (arithmetic mean) of numbers.|
|AVERAGEA||Calculates average (arithmetic mean) of numbers; includes text and logicals.|
|COUNT||Counts cells that contain numbers.|
|COUNTA||Counts cells that are not empty.|
|MAX||Finds largest value in a set of values.|
|MAXA||Finds largest value in a set of values; includes text and logicals.|
|MIN||Finds smallest value in a set of values.|
|MINA||Finds smallest value in a set of values; includes text and logicals.|
|STDEV||Calculates standard deviation based on a sample.|
|STDEVA||Calculates standard deviation based on a sample; includes text and logicals.|
|STDEVP||Calculates standard deviation of an entire population.|
|STDEVPA||Calculates standard deviation of an entire population; includes text and logicals.|
|VAR||Estimates variance based on a sample.|
|VARA||Estimates variance based on a sample; includes text and logicals.|
|VARP||Calculates variance for an entire population.|
|VARPA||Calculates variance for an entire population; includes text and logicals.|
Sheet dynamics and 3D references
What happens when a sheet is inserted into, or deleted from a 3D reference range, or when the sheet order is changed? These scenarios put 3D references apart than the regular counterparts. Since column or row numbers are predefined you can’t actually move, say row 5, between 1 and 2. Technically you can, but only the cell contents are transferred, not the actual reference. On the other hand, sheets are not predefined by Excel, users create and name them.
Inserting or copying
Adding a new sheet by inserting a new sheet or copying an existing one between start and endpoint sheets, adds new sheet(s) into the reference automatically. For example, let’s assume that you have the “Jan” and “Dec” sheets in the beginning.
=SUM(Jan:Dec!C2) formula returns 12,000 from the two sheets “Jan” and “Dec”.
After formulas are created, you can create new sheet or copy an existing one, and name it “Feb”. The formula result will be updated automatically.
Deleting a sheet between start and end point sheets removes them from calculations. However, deleting start or end point sheets updates calculation results, as well as reference itself. The sheet in the cell reference shifts to adjacent inner sheet. For example; let say there are 3 sheets named “Jan”, “Feb” and “Dec” in respective order and a formula like this: =SUM(Jan:Dec!C2)
Deleting the sheet named “Jan”, will update the reference to Feb:Dec!C2, so the formula becomes: =SUM(Feb:Dec!C2)
Moving a sheet(s) between start and end point sheets causes same effect as inserting. Moved sheets will be automatically included in calculations. Moving a sheet(s) outside the reference range will remove the sheet(s) from those calculations.
However, moving either the beginning or the end sheets might update the reference. If the start sheet is moved into a position after the end sheet, it loses its place in the range and is removed from calculations.
Let’s take another look at the example containing 3 sheets named “Jan”, “Feb” and “Dec” in respective order and a formula, =SUM(Jan:Dec!C2).
Moving the “Jan” sheet after the “Dec”, reference will not contain “Jan” anymore. The formula becomes =SUM(Feb:Dec!C2).
Named ranges and 3D references
3D references can be used inside named ranges as well. Named ranges make your formulas less complicated, are easier to read, and help you build workbooks faster. To add a 3D reference as a named range:
- Go to the FORMULAS tab in the ribbon
- Click Define Name
- Type in a name into the Name box
- Enter the 3-D reference into the Refers to box
- Click OK to complete the process
Now, this named range can be used in calculations.
For more information on named ranges, please see: Excel Named Ranges