One of the best excel cell reference attributes is a 3D reference excel or dimensional reference.
A 3D reference in excel mentions the same cell or maybe a wide range of cells on many worksheets. It is an easy method to find out the data across various worksheets along the same structure. It is considered a good alternative option to the Excel Consolidate feature. We are going to discuss it in detail below, stay with us till the end to have complete details. So, let’s get started!
GET MORE INTERESTING ARTICLES BY CLICK HERE.
What is a 3D reference in Excel?
An excel 3D reference let you cite the same cell or a wide range of cells in various worksheets. In other words, it refers to the wide range of worksheet names, not only the wide range of cells.
The salient point is that all of the referenced sheets need to be on the same pattern and the same kind of data.
3D reference Formula in excel:
A 3D formula is a formula that cites the same cell or maybe a wide range of cells on several worksheets.
It can be used to add up the numbers in cell on 4 variant worksheets.
When using this 3-D formula in Excel, all worksheets between the First sheet and the Last sheet are involved in calculations.
Point to be Noted: All Excel functions do not support the 3D references.
How to build a 3-D reference in Excel?
To build a 3D reference, follow the steps that are, mention below.
- First of all, you have to click on the cell where you want to enter your 3D formula.
- Secondly, you have to type the equal sign (=), and then enter the function’s name, along with the type of an opening parenthesis, e.g., =SUM
- Thirdly, you have to click on the tab of the first worksheet that you want to add to your 3D reference.
- Fourthly, you have to hold on to the Shift key, clicking the tab of the last worksheet which was included in your 3D reference.
- Fifthly, you have to opt for the cell or variant range of cells that you want to figure out.
- Sixthly, you have to type the remaining formula as usual.
- Lastly, you just have to press the Enter key to accomplish your Excel 3-D formula.
How to add a new sheet in an Excel 3D formula?
3D references in Excel are expansile. It means that you can produce a 3-D reference at some point, then you can insert a new worksheet, and proceed it into the range that your 3-D formula consults to. Take a look at the example below to understand it fully.
Suppose that it’s the start of the year and you have facts for the first few months only. However, so you have to add a new sheet each month and you have to calculate all the sheets as they are added.
To do this, produce an empty sheet, named Dec, and make it the last sheet in your 3D reference: =SUM (Jan: Dec! B2:B5)
Whenever a new sheet is placed in a workbook, simply proceed with anywhere between Jan and Dec:
That’s all! Because your SUM formula has a 3-D reference, it will now sum- up the given range of cells (B2:B5) in all the worksheets within the designated range of worksheet names (Jan: Dec!). You just have to remember that all of the sheets should be added in an Excel 3D reference in the same data layout and the same data kind.
How to create a 3D reference in excel with names?
To make it easier for you to utilize 3D formulas in Excel, you can produce a defined name for your 3D reference.
- On the Formulas tab, you have to go to the Defined Names group and click Define Name.
- Now, in the New Name dialog, type in your desired and easy-to-remember name in the Name box, the upper limit for characters is 255.
- Delete the data of the Refers to box, and then you have to enter a 3D reference by following the given information.
- Firstly, Type = (equal sign).
- Secondly, repress the Shift, click on the tab of the first sheet where you desire to reference, and then click your last sheet.
- Thirdly, Select the cell or range of cells that have to be referenced. You can also reference the whole column by just clicking the column letter on your sheet.
In this example, let’s produce an Excel 3D reference for the whole column B in sheets Jan via Apr.
- Click the OK button to save the newly produced 3D reference name and shut the dialog. And it’s done!
And now, to sum up, the numbers in column B in all the worksheets from Jan via Apr, you can go with this simple formula:
=SUM (my reference)
Excel Functions carrying 3-D references:
Below is the list of Excel functions that permits using 3-D references:
- SUM – Adds up numerical values.
- AVERAGE – Figure out the arithmetic mean of numbers.
- AVERAGE – Figure-out the arithmetic mean of values, including numbers, text, and logical.
- COUNT – Calculate cells with numbers.
- COUNTA – Calculate non-empty cells.
- MAX – Returns the largest value.
- MAXA – Returns the largest value, including text and logical.
- MIN – Discover the smallest value.
- MINA – Discover the smallest value, including text and logical.
- PRODUCT – Multiplies numbers.
- STDEV, STDEVA, STDEVP, STDEVPA – Count a sample divergence of a specific set of values.
- VAR, VARA, VARP, VARPA – Returns a sample dissimilarity of a defined set of values.
Final Thought:
We have shared all details related to the 3D reference excel and its formula. You can follow up all the steps mentioned above, to insert 3D formula in a sheet and the method to create a name in a 3D excel sheet. We hope it will be informational content for you, if yes, let us know in the comment box below. We would love to hear from you.