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 results from week 1 through week 5.
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 cells in columns B through H and we get an average.
But what if we wanted to get an average of all test scores and we didn’t have them all on one sheet? In that case, if all sheets you want to reference have the same exact 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 ending sheets. So, to get an average of the test scores in Week 1 through Week 5, I can start an AVERAGE function with an open parentheses, then type:
Then an exclamation mark, and then the address of the first test score in each sheet, 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 (Week 1) 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.
Then remove the 3D formula and look at how to enter the formula by pointing and clicking.
Start the formula normally. Then click the first sheet, Week 1. Now hold the Shift key held down and click the last sheet, Week 5. 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 last reference. If rename week 1, everything still works fine, but the reference is updated.
However, if I move week next to week 1, we are effectively removing weeks 2,3, and 4 from the the calculation, so we get the average of Week1 and Week5 only.
IF we move week 5 back to it’s original location, everything works the same as before.