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!

### 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.

## 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!*

Tools | Excel (Exam 2) Follow-along Guide | |

Lesson | AND vs. OR | |

Lesson | HLOOKUP | |

Lesson | IFERROR | |

Lesson | Nested IF vs. IFS | |

Lesson | Excel tables | |

Lesson | PivotTables | |

Lesson | COUNTIF, SUMIF, AVERAGEIF | |

Lesson | SUMPRODUCT | |

Lesson | 3D 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*

I’m a Miami University (OH) 2021 alumni who majored in Information Systems. At Miami, I tutored students in Python, SQL, JavaScript, and HTML for 2+ years. I’m a huge fantasy football fan, Marvel nerd, and love hanging out with my friends here in Chicago where I currently reside.