How to create 3D references

Sometimes in Excel you may want to reference a large number of sheets that have the same structure. In this case, you can use a special trick called a "3D reference".

Here are the test scores we looked at earlier. The Summary sheet is pulling in the results from Week1 through Week5.

Suppose we want to average those test scores? In that case, since we have all the test scores on one sheet already, we can just use the AVERAGE function and refer to the cells in columns D through H to get an average.

But what if we wanted to get an average of all the test scores on the different sheets and we didn't have them already on the Summary sheet? In that case, if all the sheets you want to reference have the exact same structure, and appear next to one another in the workbook, you can use something called 3D referencing.

3D referencing works like a cell range—you use a colon between the starting and the ending sheets. So, to get an average of the test scores in Week1 through Week5, I can start the AVERAGE function with an open parentheses, and then type:

Week1:Week5

Then, an exclamation mark, and then the address of the first test score in each sheet, which is D6.

Once I enter the formula and copy it down, we get exactly the same averages we calculated earlier.

But in this case, the formula does not depend on the values in the Summary sheet. Instead, it's using the values on the first sheet (Week1) through the last sheet (Week5). In fact, we could just delete the values on the Summary sheet, and the average would keep working. I'll undo that, now.

Let's remove this formula and look at another way to enter it using the point-and-click method.

Start the formula normally. Then click the first sheet, Week1. Now hold the Shift key down and click the last sheet, Week5. Excel builds the 3D reference. To complete the formula, click D6 and press Enter.

Notice that Excel doesn't care about the names of the sheets when you build a 3D reference. It simply includes all sheets between the first and the last sheet. If we rename Week1, everything still works fine, but the reference is updated in the formula.

However, if  I move Week1 next to Week5, we're effectively removing weeks 2, 3, and 4 from the calculation, so we get the average of Week1 and Week5 only.

If I move Week1 back to its original location, everything works the same as before.