Formulas are the backbone of the Microsoft Excel. They allow you to do calculations, like adding totals, averaging numbers, or finding the highest value without a calculator. There is one thing that many Excel users don’t know: you can create 3D-Formulas with 3-D References. It is like a three-dimensional chart or image with more than one phase. A normal range is a group of cells from a single worksheet.
Imagine you run a small business and have separate Excel sheets for each year to track your annual sales: 2013, 2012, and 2011. Each sheet has the same layout, with the total sales amount in cell B2. Now, you want to determine the total sales for these three years without manually adding up each year’s sales.
This is where a 3D reference comes in handy. You can create a formula that looks at cell B2 in all three sheets at once.
What is a 3D Reference in Excel?
An Excel 3D reference is an awesome way to work with data across multiple worksheets. Imagine you have several worksheets, each representing a different month but having the same layout. A 3D reference lets you do calculations across these sheets easily.
For Example, in =SUM(Sheet1!A1:A10), “A1:A10” refers to a group of cells from Sheet1. But, a 3D reference is a range of cells (=SUM(‘2009:2013’!A1:A10)) in which you can refer to the same cells from multiple worksheets using a single reference. Refer to the same cell or range from multiple sheets.
Create a 3D Reference
The only thing you must do before using a 3D reference is to ensure that all the worksheets are in sequence. Let me show you an example.
Now, you have 5 worksheets in a workbook. To calculate the sum of the range C5:D6 from all 5 worksheets, you must use a formula like the one below.
=('2009'!A1:A10,'2010'!A1:A10,'2011'!A1:A10,'2012'!A1:A10,'2013'!A1:A10)But it will look like this if you want to create a 3D formula with a 3D reference.
=SUM('2009:2013'!A1:A10)- Enter Function: Enter the function you wish to use with your 3D reference. Excel offers numerous functions that support 3D referencing.
- Select the first worksheet: Click on the tab of the first worksheet you want to include in the 3D reference. This should be the initial sheet from which Excel will start calculating.
- Specify the cell or range of cells: On the first worksheet, click on the cell or drag to select the range of cells you want to include in the 3D reference. This cell or range should hold the data you want the function to calculate.
- Select the last worksheet: After specifying the cells on the first worksheet, hold down the Shift key and click on the last worksheet tab you want to include in the 3D reference.
- Close the parentheses: Once you’ve selected all the worksheets, close the parentheses and hit Enter. Excel will now calculate the result using the same cell or range of cells across the selected worksheets.
How 3D Reference Works in Excel
A 3D range formula always works in two different parts.
- Range if Worksheets – 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 continuous. In the above example, I have five worksheets from 2009 to 2013.
- Range of Cells – A range of cells. This is a normal range of cells that you want to refer to in all the worksheets.
Adding a New Worksheet
If you add a new worksheet within the span of sheets referenced by a 3D formula, Excel will automatically include that sheet in the 3D reference.
Suppose you insert a worksheet between 2009 to 2013. It will automatically include the value of the range A1:A10 from the new sheet in the function you are using.
Deleting a Worksheet
If you delete a sheet part of a 3D reference in a formula or a function, Excel will automatically adjust the reference to exclude the deleted sheet. For example, if your formula is =SUM(Sheet1:Sheet3!A1) and you delete 2010, it will update to =SUM(2013:2009!A1) without including sheet 2010.
After deleting a sheet, it’s a good idea to check your formulas to ensure they still work and show the correct result. As I said, Excel handles the deletion automatically, but it’s always good to double-check.
Moving a Sheet
Moving a worksheet to a position outside the referenced span of sheets will no longer be included in the calculation. In this example, the range of worksheets starts from “Sheet 2009” and ends at “Sheet 2013.” The point is that if you move any sheet out of this range, that sheet will be excluded from the formula calculation.
Use 3D-Range in Named Range
You can also use 3D references in named ranges can help use it in calculations across multiple worksheets. This allows you to refer to the entire workbook for a specific range of cells.
- First, go to the Formulas tab on the ribbon. Click on Name Manager. And then click on New to create a new named range. In the Name field, give your range a name.
- In the Refers to, enter your 3D reference. For example, if you want to refer to cell A1 from 2013 to 2010, you would enter: =2013:2010!$A$1
- Now, you can use your named range in any formula. For example, to sum the values in cell A1 across Sheet1 to Sheet3, you can use: =SUM(Sales).
Using 3d-Reference in a named range works like a powerful tool that allows you to create a multiple worksheet reference, and you don’t need to type it in the formula again and again. All you need to do is enter the name of the named range; that’s it.
Functions which Support 3D-Reference
In Excel, several functions support 3D references for formulas, allowing you to perform calculations across multiple worksheets. Here are some of the critical functions that support 3D references:
- SUM: Used to add up the same cell or range of cells across multiple worksheets.
- =SUM(January:March!A1)
- AVERAGE: Calculates the average of the same cell or range of cells over multiple worksheets.
- =AVERAGE(January:March!A1)
- MAX: Determines the highest value in the same cell or range of cells over multiple worksheets.
- =COUNT(January:March!A1)
- MIN: Identifies the lowest value in the same cell or range of cells over multiple worksheets.
- =MIN(January:March!A1)
- COUNT: This function counts the number of cells with numerical data in the same cell or range of cells across multiple worksheets.
- =COUNT(January:March!A1)
- PRODUCT: Multiplies all the numbers given in a set of cells.
- =PRODUCT(January:March!A1)
- COUNTA: Counts the number of cells that are not empty in a range.
- =COUNTA(January:March!A1)
- STDEV.P: Calculates the standard deviation based on the entire population given as arguments.
- =STDEVP(January:March!A1)
- VAR: Calculates the variance of values across multiple sheets.
- =VAR(January:March!A1)
- VARP: Calculates variance based on the entire population.
- =VARP(January:March!A1)
Benefits of Using 3D-Reference in Formulas
- Efficiency: It allows you to perform calculations on the same cell or range of cells across multiple worksheets simultaneously. This can save significant time, especially when dealing with large datasets.
- Quick Data for Analysis: You can easily perform calculations across multiple worksheets, such as sum, average, maximum, minimum, count, etc., making it easier to analyze large amounts of data.
Points to Take Care
- Same Data Layout: Make sure the layout (date position) is consistent across all the sheets you’re referencing. If cell A1 is a sales total in one sheet, it should be the same in all the others.
- Sheet Names: Avoid renaming sheets after creating a 3D reference. Changing sheet names can break the reference and lead to formula errors.
Can I use 3D-Reference in a Pivot Table Data Source?
Unfortunately, you cannot directly use 3D references as the source data for a Pivot Table in Excel. Pivot Tables require a contiguous range of data on a single worksheet.
Wrap Up
One of the most important benefits of 3D references is that they can shorten 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 tried 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.
Practice Workbook
Related Formulas
- Back to the List of Excel Formulas
Muito obrigado, excelente dica.
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.
I’m so glad you liked it. 🙂
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.
Thanks for your words. 🙂
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.
@disqus_mmptxJPJD0:disqus Awesome! Awesome! Awesome!
I always use it in my work
Great
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!
Hey Squalle, I’m so glad you linked it.
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/
Too Smart. Thanks for sharing
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.