Data Consolidation using 3-D referencing

June 23rd, 2011 by Tips Master | Posted under Excel Functions.

3D referencing in excelData consolidation can be very easily done using Excel 3-D referencing. 3-D referencing as the name suggests has 3 dimensions which are Rows, columns and worksheets. Not all Excel functions supports 3-D referencing. We will see in this post how to create 3-D referencing and which are the excel functions that support 3-D referencing

 

 

Create 3-D referencing

This is really helpful when you have data in different sheets which are similar in structure. Similar in structure means column and row labels for all the sheets should be same.

We will see the below illustration to understand this better


There are 3 company data needs to be consolidated. This is consolidated using the SUM function

Data consolidation using 3-D referencing

To enter the 3-D reference do the following steps
1. Enter the forrmula in the cell and open the brackets “=Sum(
2. Select the starting sheet (here A)
3. Now press Shift key and select the last sheet. Now the formula will be “=sum(‘A:C’!
4. Now holding the shift key select the cell or range you need to add. The formula now will be “=SUM(‘A:C’!B5
5. Close the brackets and press enter to add the 3-d referencing

 

Adding or deleting sheets in 3D reference


If you need to add any sheets just add the new sheet between the starting and ending range. The function will automatically add the new sheet as we have given the range of sheets instead of individual sheet name.

To delete the sheet simply delete the sheet so the function will recalculate based on the remaining sheets. You can even remove the starting and ending sheets and Excel will recalculates the formula with the remaining sheets

 

Functions available for 3-D referencing in Excel
1. SUM                                                                      10.PRODUCT

2. AVERAGE                                                               11.STDEV

3. AVERAGEA                                                            12.STDEVA

4. COUNT                                                                 13.STDEVP

5. COUNTA                                                               14.STDEVPA

6. MAX                                                                      15.VAR

7. MAXA                                                                    16.VARA

8. MIN                                                                      17.VARP

9. MINA                                                                    18.VARPA

Other than this list see the post on data consolidation using SUMPRODUCT, SUMIF and INDIRECT function used to create 3-D referencing

 

Create name for 3-D referencing

Go to Name manger. In the reference , type the formula in the same way we done earlier. Give a suitable name and press ok

Using 3-D referencing in names

You can use it instead of the formulas.

 

Download the files

Download the 2007 version – Data consolidation using 3D references

Download the 2003 version -Data consolidation using 3D references

Please let me know if you have any queries/comments

 

Tags:

Comments

4 Responses to “Data Consolidation using 3-D referencing”
  1. gobida majhi says:

    how to use 3d in excel sheet

  2. reet k says:

    many thanks. It helped me to see that columns and row labels should be the same..this point was not clear to me until i read it here.

  3. Mohan J says:

    Fantastic superb notes. thanks a lot to Tips Master

Do you have any comments on Data Consolidation using 3-D referencing ?