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:

=SUM(Jan:Dec!C2)

This formula basically does the same thing as the two formulas below, but it’s far easier to write and read.

=SUM(Jan!C2,Feb!C2,Mar!C2,…,Nov!C2,Dec!C2)

=Jan!C2+Feb!C2+Mar!C2+…+Nov!C2+Dec!C2

Here is the list of formulas you can use with 3-D references:

Function	Description
SUM	Adds numbers.
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.
PRODUCT	Multiplies numbers.
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

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

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: