Various ways of data consolidation in Excel

For the last few posts, I have been writing about data consolidation – Various ways possible of consolidating in Excel. I was getting a lot of queries on the same so thought of putting all those in a single place. This is the consolidation of all the previous post on the topic. You will also find sample worksheets with illustrations under each posts.  Don’t forget to do your home works with those .

 

 

 

1. Data consolidation using Indirect function

The first one was using Indirect function to get the data from different sheet. This is very useful when we need to ge the data   from different sheets to a single place. This does not perform any calculations.

Data consolidation using Indirect Function in Excel

 

2. Data consolidation using Sumproduct, Sumif and Indirect function

The second one was a little more complex and uses Sum product, Sumif and indirect function to sum up data from different sheets. This is real cool tip and this one gave me lot of visitors and comments. This comes at good help when the workbook has 50 or 100 worksheets. Dont miss this

Data consolidation using Sum product, Sumif and indirect function

 

3. Using Data - consolidate tool in Excel

The third one uses data consolidation wizard in Excel to do the data consolidation task. This is also a good tool from excel but not used by many. This can be used instead of the second option but will be good only for limited worksheets or ranges

Data consolidation in Excel

 

4. Consolidating multiple ranges using Pivot tables

Instead of the above two options if you want to use the pivot tables then this one explains on using data consolidation of multiple ranges using pivot tables

Consolidate multiple ranges using Pivot tables

 

5. Data consolidation using 3-D referencing

3D is now the talk in the town. Have you ever used 3D referencing in excel. This  last post is pretty straight forward and explains about using 3D referencing in Excel to consolidate data.

Data consolidation using 3D ranges

 

This is not a complete list . Data consolidation is also possible using VBA which i will be covering in my later posts.  Hope you like the posts.  I love the second option (consolidating data using Sumif, Sumproduct and indirect function). Which one of the above did you like the most . Give me a true feed back.

 

 

 

 

Tags: Data Consolidation