Consolidate data in multiple worksheets in Excel

To summarize data from separate worksheets, you can use the data consolidation tool in Excel. The worksheets can be in different workbooks or in the same work book. There are two types on consolidation possible with this. One is consolidation by position and the other is consolidation by category. This is a very useful tool in Excel which will help in data consolidation. We will see detailed illustration on how to do this in Excel 2007.

 

 

Consolidate by position.

This is possible when the data is properly arranged and the positions are similar in all the sheets which needs to be consolidated. See the below illustration

 

Company A

Company B

Company C

As you can see rows and columns for all the data are same. So we can easly do a data consolidation by position. Follow the below steps to learn how to do the data consolidation by position.

As we are doing the consolidation by position first we need to create the row and column labels in another sheet.

In the consolidation sheet select the place where we need to get the data consolidated. GO to data – Consolidate

This will pop up the consolidate window

In the reference select the range using the collapse button or if you have a named range you can directly enter the name and press add button to add the range to the “all references”. No need to check the boxes in “Use labels in” (We will use the same when we do the consolidation by category). You may check the box for ” create links to sorce data”. This will create a links in the consolidation sheet to the base data so that any change in the base data will reflect in the consolidation. The disadvantage for this is that in will create some unnecesssay rows below the data which might some times cause inconvenience.

 

Like wise add the all other ranges and press ok

This will give us the consolidated table

 

Consolidation by category

 

This is useful when the data is not properly arranged. But the row and column labels needs to be similar for all the tables

To explain this to the earlier data we will add company D

 

You can see that for the above data the labels are same but their positions are different. We require to do consolidation by category where in Excel consolidate the data based on the labels used for columns and rows. So be careful to use the same labels in all the data/tables.

Do the same steps as explained above for consolidation by position but use the full range including the column and row labels and tick the check box for use lables in Top row and left column

As the company D data contains an additional column for Total it also includes in the conolidated table which is the total for only the company D which you may delete as it might not show the correct picture.

Creating links to the base data

As mentioned earlier you can use the option to create links to the source data by checking the option for the same. This will create links in the consolidated table  which excel will automatically groups under each row

 

Using other functions for data Consolidation

The following functions are also available when using this tool . Use the drop down list to select the suitable function for your need.

Download the Excel files

 

Download the Excel files

Excel 2007 Version – Data consolidation in Excel

Excel 2003 Version - Data consolidation in Excel

 

Other Data Consolidation techniques in Excel explained

Also see other Data consolidation methods explained in Tips indeed

1. Data consolidation using Pivot tables
2. Data consolidation using Indirect function
3. Data consolidation using Sum Product, Sum if and Indirect function

Hope you enjoyed the tutorial .Please don’t forget to leave your comments/Queries

 

 

Tags: Data Consolidation