Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

Excel 3D Reference for Cells

We all want to save our time.

And, by using 3D Reference in Excel you can save your a lot of time.

It’s is like three-dimensional chart or image which has more than one phase. A normal range is all about a group of cells from a single worksheet.

For Example, in =SUM(Sheet1!A1:A10), “A1:A10” is a group of cells which is referred from Sheet1.

But, a 3D reference is a range of cells in which you can refer to the same cells from multiple worksheets using a single reference.

In simple words, refer to same cell or range from multiple sheets.

Create a 3D Reference

Only one thing which you have to take care before using a 3D reference is all the worksheet should be in a sequence.

Let me show you an example.

Add Worksheets To Use Excel 3D Range

Now here you have 5 worksheets in a workbook.

So, if you want to calculate a sum of range C5:D6 from all the 5 worksheets you have to use a formula like below.

=SUM(‘2009‘!A1:A10,’2010‘!A1:A10,’2011‘!A1:A10,’2012‘!A1:A10,’2013‘!A1:A10)

But if you want to create a 3D formula with a 3D reference then it will be something like this.

=SUM(‘2009:2013’!A1:A10)

How 3D Reference Works in Excel

A 3D range formula always works in two different parts.

How Excel 3D Range Works

First, a range of worksheets:

Like a range of cells, you have to create a range of worksheets from which you want to refer cells.

The range of worksheets must be a continues range. In above example, I have 5 worksheets from 2009 to 2013.

Second, a range of cells

A normal range of cell from which you want to refer.

More Information

1. Adding a New Worksheet

If you insert a worksheet between 2009 to 2013.

It will automatically include the value of the range A1:A10 from new sheet in the function you are using.

more information about adding new worksheets in 3d range

2. Deleting a Worksheet:

If you delete a worksheet from 2009 to 2013. It will automatically exclude the value of cell C4 from the function you are using.

3. Sequence of Worksheets:

Here in this example, the range of worksheets starts from “Sheet 2009” and ends at “Sheet 2013”.

The point is, if you move any sheet out of this range, that sheet will exclude from the formula calculation.

Conclusion

One of the most important benefits of 3D-references is that it can shorten your complex formulas.

You don’t have to refer to all the worksheets separately in formulas. And, I hope this method will help you write better formulas.

😃

Now tell me one thing. Have you tired it before? Please share your views with me in the comment section, I'd love to hear from you.

And, please don't forget to share with your friends.

  • Hello, I read your new stuff named “How To Create 3D Reference – ExcelChamps.com” regularly.

    • Hello dji phantom 3,

      Thank you. Please subscribe to our newsletter to get all the awesome excel tips & tricks in your mailbox. Also, get your free excel shortcut copy.

  • Thanks for your marvelous posting! I definitely enjoyed
    reading it, you can be a great author. I will ensure that I bookmark your blog and definitely will
    come back later in life. I want to encourage you to ultimately
    continue your great work, have a nice weekend!

  • Pingback: Text-To-Speech in Excel Will Give Voice To Excel To Speak Text()

  • MF

    There is a very hidden magic for 3D formula writing.
    Take a look and hope you like it.
    https://wmfexcel.com/2015/07/11/sumc3-is-it-a-valid-formula-no-it-is-magical-indeed/

    • Puneet Gogia

      Too Smart. Thanks for sharing

  • Pingback: How To Quickly Create a Dynamic Drop Down List In Excel | Blog Excel Advise()

  • Squalle

    I actually stumbled upon this. While I was working in my spreadsheet, I tried it and it worked! I hadn’t known it was even possible. Very handy!

    • Puneet Gogia

      Hey Squalle, I’m so glad you linked it.

  • MTVN

    I always use it in my work

    • Puneet Gogia

      Great

  • Adrian Palmer

    I always put in a blank worksheet called start and finish (or whatever you prefer) so my formula looks like:
    =SUM(‘start:finish’!A1:A10).

    This has the advantage that if i want to insert the spreadsheet for 2008 or add 2014 sequentially, I don’t have to change the formulae. Similarly, If they are not named numerically, I can change the order to help people read it without fear of missing a sheet.

    • Puneet Gogia

      @disqus_mmptxJPJD0:disqus Awesome! Awesome! Awesome!

  • Emmanuel Martins Udu

    You’ve really been a blessing. Each time I get a mail from you, I’m one step better than the previous experience.
    I’m learning new things everyday.

    • Puneet Gogia

      Thanks for your words. 🙂

  • Neil Fitzjames

    Thanks Puneet, apart from ease of function the 3D format saves a lot of keying. I agree with your point of identical layout for each Worksheet.

    • Puneet Gogia

      I’m so glad you liked it. 🙂