3D Cell References | CSE 148 – Excel Exam 2

Still feeling nervous for your exam? Check out the Excel Cram Kit (Exam 2) for practice modules that emulate the difficulty that you'll face on your exam. Each module includes a thorough step-by-step explanation where I walk you through the entire problem solving process!

3D Cell References explained

"NOOOOOO!" you scream, as you sit up in bed abruptly.

You wake up from your terrifying dream, dripping in sweat. To ensure you made it back to the real world, you glance at the table across from you. "Thank goodness" you say to yourself, as you notice the 3 empty beer cans and 2 crushed seltzers sitting on your desk. "I'm still in college".

In this terrible dream, you were a year into working your full-time job in the real world. You woke up, went to work, and sat in your cubicle all day. The worst part of the dream came at the end, when you went home to...

...budget. In this nightmare, you sat on your laptop and made a month-by-month financial review in Excel, with each month having its own sheet.

How terrifying!

As you reviewed your expenses from month-to-month, you realized that you should make a sheet that summarized expenses from each month's sheet into one "year-in-review" sheet. To do so, you used a 3D cell reference.

3D cell references enable you to reference the same individual cell or range from multiple different worksheets.

Coding a 3D Cell Reference

The first step to 3D referencing is to take note of all the worksheets that you'll be referencing, not cells.

In our case, we'll be referencing the August, September, October, November, and December sheets.

Next, let's move to our Year in Review sheet and pick the first cell we'll 3D reference. We'll start with our "Rent" category.

Without 3D referencing, if we wanted to compute the sum of all rent expenses for August through December, it'd look like this:

=August!B1 + September!B1 + October!B1 + November!B1 + December!B1

But this gets annoying, and imagine if you had more months. Ugh. Even more typing.

With a 3D cell reference, we can SUM these values across all sheets with ease!

=SUM(August:December!B1)

You can also do this like so (while holding shift after clicking on the August worksheet):

What this does is calculate the sum of all the "B1" cells within the boundaries of the August and December sheets.

One very important thing to note though, is that the structure of each of those sheets from August to December must be the same.

But... why?

Say in September, we accidentally placed our "Grocery" charge in cell B1.

Our 3D cell reference is not smart enough to notice that September's B1 is not rent, but grocery, and shouldn't be included in the SUM calculation. It would just add September's grocery charge to the sum instead of September's rent charge. This would cause our Year-in-Review sheet to have an incorrect total rent charge.

When using 3D cell references, you must ensure the structure of each of the sheets is the same.

To complete the rest of our Year-in-Review sheet, all we need to do is copy down our formula to the other categories like so:

How does this work? Because the 3D cell reference changes to B2, B3, B4, etc. as you copy it down each cell. It's still a relative cell reference!

To convert this relative cell reference to an absolute cell reference, we only need to add the "$" in front of the row/column value of B2. If you wanted to use an absolute cell reference instead, you could do it like so.

=SUM(August:December!$B$1)

Now when we copy it down to the other categories, each one has the sum of all rent charges. This is because the 3D cell reference stays at "B2" for each one!

Note: there's no reason you'd want your rent charges to be copied into your grocery, entertainment, etc. The point of this divergence was to show you that 3D cell references work with absolute cell references as well as relative cell references.

Using a 3D cell reference with a range

Say we want to compute the total charges incurred over the year in our Year in Review sheet.

To do so, we can 3D reference a range instead of a single cell, like so:

=SUM(August:December!B1:B5)

This sums the range of "B1:B5" in each of the sheets between August and December, resulting in a total charge of $11,677.00.

Note: Yes, you could've just summed all the values in the Year in Review sheet. This example was to show the functionality of working with ranges in 3D cell references.

Practice problem

Let's say that you and your best friend got into weight lifting, and decided to have a little competition.

Over the course of your first year lifting, you placed your personal best in each lift within "3D.1". Your friend did the same in "3D.2".

Within "3D.3", determine the maximum weight lifted in each exercise between you and your friend with a 3D cell reference.

=MAX(3D.1:3D.2!B3)

This formula is then copied down through the table.

Note: the formula for maximum is not MAXIMUM, rather MAX!

Excel (Exam 2) Follow-along Guide

It's no secret you retain info better when you write it down. That's why I created the Excel (Exam 2) Follow-along Guide for you!

As you come upon key concepts highlighted in yellow, like this sentence here, you can fill-in-the-blanks on your Follow-along Guide so that you remember all the important stuff for later!

You can obtain the Excel (Exam 2) Follow-along Guide for FREE by entering your email below!

Congratulations! You've gone through all the core concepts that will be on your second Excel exam! Now what?

Practice, practice, practice.

I cannot emphasize enough how much more important it is to practice Excel rather than study it. Without practice, it's difficult to truly understand what you're coding when it comes time for your exam.

In the Excel Cram Kit (Exam 2), I've compiled practice questions that emulate the difficulty that you'll face on your exam. Each question includes a thorough step-by-step explanation where I walk you through the entire problem solving process. That way, you never feel lost, and are prepared to solve any exam problems thrown your way! Click below to get started!

Follow-along Guide

Fill-in-the-blanks for highlighted, key points in the concepts below!

Free Resources

Each exam concept broken down in simple, real-world examples!

ToolsExcel (Exam 2) Follow-along Guide
LessonAND vs. OR
LessonHLOOKUP
LessonIFERROR
LessonNested IF vs. IFS
LessonExcel tables
LessonPivotTables
LessonCOUNTIF, SUMIF, AVERAGEIF
LessonSUMPRODUCT
Lesson3D Cell References

Practice Problems

Step-by-step walkthrough for each of the questions you need to be ready for!

"I got this review for my 2nd CSE exam after not doing so well on the first one, and it was so helpful! Will definitely be using again in the future."

Erin Graham
Oct. 26, 2021

Rating: 5 out of 5.

Leave a Comment