3D Formulas: The smart way to do cross-sheet formulas
Office Mastery
S H A R E
Assign a 'primary' menu

3D Formulas: How To Create Cross-Sheet Formulas

3D formulas in Excel

BY JASON MORRELL

In this short post, I show you a smart way to shorten a formula that collects data from a number of worksheets or workbooks.

Here's what is covered:

3D Formulas: How to create cross-sheet formulas

FEATURE DOWNLOAD

Grab the free 3D Formulas cheat sheet

1. Why are 3D Formulas useful?

3D formulas are simpler than regular formulas, take less time to create than regular formulas and are less prone to errors. What’s not to like?

For example, let’s say you need to create a summary sheet which will calculate the total monthly figures for the six months between January and June.

3D Formulas - Empty Summary Sheet

Figure 01: Empty Summary Sheet

Here is a snapshot of the 6 monthly worksheets, name Jan, Feb, Mar, Apr, May and Jun.

3D Formulas - Jan to Jun Sales

Figure 02: Jan to Jun Sales

You’ll notice that each sheet has exactly the same format and it’s showing the total sales for 4 Australian states.

2. Most people don’t use 3D formulas

What most people would do, and it’s actually the longest and the slowest way of doing things, is to start a formula in cell B2 like:

=SUM(

then go to each month’s worksheet in turn and click cell B2 and separating each cell reference with a comma. Add the closing bracket at the end and you finish with this formula:

=SUM(Jan!B2, Feb!B2, Mar!B2, Apr!B2, May!B2, Jun!B2)

It works. But imagine you had 20 sheets to process, rather than just 6! You would spend half the day collecting cell references and you would probably make a mistake or two along the way!

Here’s a smarter way to get the job done.

3. How to create 3D formulas in Excel

Start the formula in the same way.

=SUM(

then …

1.  Click the Jan sheet to select it.

2.  Hold down the SHIFT key.

3.  Click the Jun sheet. Every sheet between Jan and Jun is now selected.

4.  Click cell B2.

5.  Type a closing bracket.

6.  Don’t press Enter just yet. There is one final step you must do.

At this point your formula will look like this:

=SUM(Jan:Jun!$B$2)

The cell reference is absolute (which means ‘fixed’). That’s what the dollar signs in the formula do.

To enable us to AutoFill the formula to the rest of the table, we must first make the cell reference ‘relative’ (‘unfixed’) by removing the dollar signs. One way is to manually delete the dollar signs but you can also tap the F4 key 3 times until the dollar signs are gone.

The formula is now:

=SUM(Jan:Jun!B2)

This means that when we AutoFill down, the cell beneath will say

=SUM(Jan:Jun!B3)

and the cell beneath that

=SUM(Jan:Jun!B4)

and so on.

Now that the formula in cell B2 is relative, you can AutoFill down to copy the formula.

3D Formulas - Just prior to AutoFill

Figure 03: Just prior to AutoFill

Most people stop there. But one quick thing I want to mention here is that AutoFill copies any manual formatting as well as the formulas, so at some later stage you may have to come back to fix the formatting.

Good news! It doesn’t have to be that way.

As soon as you do the AutoFill, click the AutoFill Options smart icon (which appears on the bottom right corner of the selected cells) and choose Fill Without Formatting.

3D Formulas - Don't forget the AutoFill Options

Figure 04: Don't forget the AutoFill Options

When you choose Fill Without Formatting it's like telling Excel “just copy my formulas down but don’t touch my formatting”.

 

TIP: If you have used the ‘Format As Table’ feature, you won’t have any issues with using AutoFill because Excel takes care of all the formatting for you.

  

So there you have it.

3D formulas.

Easy to set up and super-efficient.

4. Watch the video (over the shoulder demo)

Click to watch video

5. What next?

ACTION ITEM: Before we close out this post I want to give you one little action item! Comment below with ONE thing you learned from this post and how you're going to put it into ACTION. Share your successes and drop any questions you have below.

Enjoy the rest of your day!

Jason Morrell blog signature
Jason Morrell, Office Mastery

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

>