Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to create a 3D formula reference

Tags 
Summary 
With 3D referencing you build a reference that refers to a range of worksheets. It's an elegant way way to analyze a large number of identical worksheets with simple formulas.
Video Transcript 

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:

Week1:Week5

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.

Author 
Dave Bruns

Related shortcuts