Data consolidation in Excel made easy using Pivot tables

Consolidating multiple ranges using Pivot table is a very useful function in Excel which will help to consolidating values from different sheets. This is an alternative to consolidating data using Sum product explained in the previous posts. Data consolidation is possible in Excel using data Consolidation function, consolidating multiple ranges using Pivot tables and using 3D functions.

 

 

 

 

Adding the Pivot table Wizard to Quick Access tool Bar for Excel 2007

In the excel version 2003 this is available in the menu – Data – Pivot Tables. In Excel 2007, if you require this option you will need to add this to the quick Access Tool Bar

Press the office button to get the Excel options. In the Excel options go to Customize menu and add the “pivot table and pivot chart Wizard” to the Quick access tool bar.

 

To Consolidate –  Financial Performance summary

Quarter Performance Summary for company ABC

The below 3 tables show the peformance summary of 3 companies, A,B and C. The holding company(parent company)  for the 3 is ABC. we will see how it is possible to quicly consolidate the data in seconds

 

 

 

Consolidating Performance Summary

Create a new tab for consolidating the data. To select the Pivot table Wizard press the short cut key Alt+D+P or select from Quick Access Tool bar.

Select the radio button “Multiple consolidation ranges”

Press the next button. Here you will find the option to create the page field. Either the Excel will create a single page field (which i recommend) or there is an option for the users to create page field. We will select the option ” Create a single page field for me” and press Next

This will land you in the option to select the ranges.

Either You can use the collapse button to select the range one by one and add or type the names if you have defined any.

For ease of use i have used Excel tables. (TableA, TableB, TableC). There is a helpful tip when you convert the range to table. Dont check the option “my table has headers” when you create the table. This is because when you select the option “my Table has headers” the headers will not get selected in the Pivots. (Very helpful tip which i found very recently… )

 

The table might look a little bit odd, but it has the benefits..

Coming back to the Adding ranges.., Type the name of the tables and press add to add the 3 tables.

Press finish to complete the Pivot

Hurrayyyyyyyy , See how quickly we completed consolidating the three companies.

 

Advantageous and Disadvantageous  of tables and pivot

Downs – This will work like the normal pivot table except one disadvantage that Page field will not be available for you to analyze data. Page field will be automatically added for Tables. You will not be able to change that. But you will able to select all the tables or, one table at a time using the page field.

Ups – Because we used the tables, the base data will be very dynamic. You can add data at rows or columns of data and what you need to do is to just refresh the pivot to add the new row or column. Also there is no requrement for the data to be in same format for all the tables. Pivot will take care of this provided the row and column headings are same for all the tables (not the table headers, headers used by the pivot, here descitpion and Quarters).

 

Download the file

Use the link to download the 2007 version – Consolidating Multiple ranges in Pivot tables

Use the link to download the 2003 version – Consolidating Multiple ranges in Pivot tables

 

I love to hear your comments. Don’t feel lazy about giving your comments

 

Tags: Pivot table