3D References - Formulas that Reference Multiple Sheets at Once in Excel

(make sure to download our sample workbook so you can follow along)

Sections:

Example 3D Reference

How it Works

How to Create a 3D Reference

Adding or Removing Sheets from the Workbook

Functions You can Use with a 3D Reference

Working with Sheets with Variable Names

Notes

Example 3D Reference

I have a worksheet that consolidates sales data between stores and I want to have that data nicely presented on the Master worksheet like this:

To SUM the sales from the three worksheets Store 1, Store 2, Store 3, we use this formula in cell B4:

How it Works

The formula in the example above is saying that you need to sum all of the data that is located in cell B1 in all of the sheets from Store 1 to Store 3.

Any sheet that is located between Store 1 and Store 3 will be included in this calculation, including the Store 1 and Store 3 sheets.

The syntax for this 3D Reference is like this:

=FUNCTION('Starting Sheet:Ending Sheet'!Cell or Range Reference)

The important thing about this type of reference is that ALL data must be located in the same place on all sheets that you want to include in the 3D Reference.

Every one of our store sheets looks like this:

How to Create a 3D Reference

There are a couple steps involved in creating the 3D Reference.

1. Type the function that you want to use along with the opening parenthesis:
   
2. Click the tab for the first sheet that you want to include in the 3D Reference:
   
3. Hold the Shift key and then select the last sheet that you want to be included in the reference:
   
   Notice that in the Formula Bar, above columns C and D in this case, you can see the formula being created.
4. On the currently visible sheet, select the cell or range of cells that you want to be included in the calculation:
   
5. Add any other arguments that might be needed, depending on the function you are using.  Then, hit the Enter key.  That's it.
   
   Once you hit the Enter key, it will complete the creation of the formula and take you back to the worksheet where you input the formula.

If you want, you can also copy this formula down just like you would any regular formula and it works the same way.

Now, we have 5 3D References that gather and sum the sales data for the first 5 weeks for three stores.

Adding or Removing Sheets from the Workbook

If you add or move any worksheets between Store 1 and Store 3 they will also be included in the calculation.

Similarly, if you delete sheets or simply move them outside of the bounds of Store 1 and Store 3 they will no longer be included in the calculation.

Functions You can Use with a 3D Reference

• SUM
• AVERAGE
• AVERAGEA
• COUNT
• COUNTA
• MAX
• MAXA
• MIN
• MINA
• PRODUCT
• STDEV
• STDEVA
• STDEVP
• STDEVPA
• VAR
• VARA
• VARP
• VARPA

Working with Sheets with Variable Names

If you have a series of worksheet tabs, the names of which or order of which you will not always know, you can still use 3D References to perform calculations on them.

The trick is to create a starting and ending sheet and use these in the 3D Reference.  Then, put all of the sheets that you want to be in the calculation between these two sheets.  Make sure to leave the starting and ending sheets blank and it won't disrupt any of your calculations.

Notes

3D References are a handy little thing to use in Excel but they can be a little confusing at first.  The main thing is that you must ensure that the worksheets included in the reference have the exact same layout and that all of the data in them is in the same place.  This is easy to do when you export data from another program but it is not so easy to do if you are combining worksheets that other users have created (people tend to mess things up no matter how hard you try to prevent it).

To be honest, I don't use 3D References that much.  The problem is that a lot of people won't understand what's going on if they need to debug or work on a worksheet that you have created using them and, if a user doesn't know that these references exist in a workbook or even what they are, they may change the order of the worksheets and mess-up the calculations.  That said, I don't use these references when I am sending Excel workbooks out to other people.

Also, don't forget to download the accompanying spreadsheet so you can see examples of 3D References in Excel.