Excel Consolidation (Table of Contents)
Consolidate Data in Excel
Consolidation in general means collection or gathering of some common things in one place. It could be any sort of data or anything like information or number. Consolidation in Excel refers to a function that combines information or data from different workbooks/worksheets into one worksheet/workbook. In other words, this function takes data from different worksheets to combine it into a single worksheet which can be easily updated.
For example, if you have four worksheets. Each sheet has expenses for a quarter. You might need a single sheet with the expenses from all four quarters. This is when the consolidation function comes into play. When the data in worksheets are in the same position, which means under the same labels and in the same order. We use “Consolidation by Position”. And when the data in worksheets are not under the same labels and not in the same order. Then, we use “Consolidation by Category”. It is just like creating a Pivot table.
How to Consolidate Data in Multiple Worksheets?
Let’s understand how to consolidate data in multiple worksheets with a few examples.
Example #1 – Consolidate Data in the Same Workbook
Let’s say we have a worksheet in which we have sales data with four worksheets marked as per their region as North, South, East, and West. Now we would like to consolidate the data into one place rather in a sheet within the same workbook. There is a fifth sheet named consolidated file.
This example will show the consolidated sales for all the regions. Here are the sample data files. Here you can see the different worksheet names and the last consolidated file on which we have to work.
This is our template in “consolidated file” sheet and now we will start consolidating data from the worksheets.
We will now click on cell B3.
Where we want the “Consolidate” function to insert the data from other sheets. As we can see above, cell B3 is selected and now we will move up to the Data tab in Excel Ribbon and will go to Consolidate.
Once we click on Consolidate, below window will appear:
The first thing we look at is the “Function” dropdown which shows many arithmetic functions like sum, count, max, average, etc. Since we want a sum of sales, we will select “Sum” in the dropdown. Now, we will go to the reference tab to add a reference to our data from different worksheets. We will then go to our first sheet that contains the sales data for North. We only have to select sales data and not heading and rows. This is shown below.
The reference is shown in the “Reference” box like this.
Click “Add” and the reference will be added in the “All Reference “box.
In the same manner, we now will add a reference from all other sheets like North, South, East, and West. Once we have finished adding the references click “OK”.
Now we have consolidated data for sales for the executives month-wise, at a national level.
Example #2 – Consolidate Yearly Sales Product Wise
In this, we have quarter wise sales for products A, B, C and D and we want a consolidated yearly sales product-wise.
Here we have data in four sheets, first sheet with sales data for Q1, next has data for Q2 followed by data for Q3 and Q4. We will now go to the reference tab to add references to our data from different worksheets. We will go to our first sheet that contains the sales data for Q1. We will select the data as shown below.
Now, we will go to the Data tab and click Consolidate and the below window will appear.
We will click “Add” and the reference will be added in the “All reference “box.
We will click “Add” and the reference will be added in the “All reference “box.
We will click “Add” and the reference will be added in the “All reference “box.
Below is our template for the consolidated datasheet. We will now select cell B2 to get the total sales data from other sheets.
We will select “Sum “ function from the drop-down. Likewise, we will add references from sheets Q2, Q3, and Q4. It will appear like this. All the references from all the sheets are now added. Click “OK”
We now have our consolidated yearly sales data with the sum totals for each product.
In case, if we want to insert the consolidated table in a blank worksheet instead of making a template table. We can do that as well. Taking our previous sample data, we will do the consolidation below.
We don’t have a template for consolidation table and we want to get consolidated data in a blank worksheet with row and column labels. We will add a new worksheet, in our case it is “Consolidated file”.
Now like before we will go to Data tab<Consolidation. Select “Sum” from the drop-down list.
We will now select the reference from our datasheets. We will start this with “North” sheet and will then proceed with “South”, “East” and “West” sheets. We will select the data as shown below which includes row and column labels.
We will then add the reference in the “All references” box:
Now add all the references in the same way from all the datasheets.
We will now check both the “Top Row” and “Left Column” and then press OK.
We now see a consolidated sales data with row and column labels.
If you want to update the consolidated data when the individual sheet gets updated, click on the box “Create Links to create data”. If you want to update data manually then don’t check the box and click OK.
The cells in the consolidated table now contains the sum formula as shown below. This makes it dynamic in nature.
This is a dynamic consolidation table.
Example #3 – Consolidating Data from Different Workbooks
If we have data in different workbooks and we want to consolidate it into a single workbook. We can do this as well. This gives us great flexibility and ease.
We have quarter wise sales data of various products say A, B, C and D in different workbooks as shown below.
Once we have all the workbooks open, we will go to a new workbook and click on cell B3. Add the references from all the worksheets below.
We have consolidated data from all the workbooks into a new workbook.
Moreover, any changes in the data in any workbooks will also get updated in the new consolidated data workbook.
So we have learned how to use consolidation function in Excel with the help of examples. It is a very useful function in merging or collecting data into one sheet from different worksheets or workbooks.
Things to Remember About Consolidation in Excel
- Be careful in selecting reference data when you are checking the boxes for “Top Row” and “Left Column”. You will then have to select the complete data including the row and column labels.
- When you are consolidating data of dynamic nature from different worksheets and workbooks. Do check the “create links to source data” and this will automatically update the changes in the data if done.
Recommended Articles
This is a guide to Consolidation in Excel. Here we discuss How to Consolidate Data in Multiple worksheets along with practical examples and downloadable excel template. You can also go through our other related articles –
