Excel Consolidation (Table of Contents)
Consolidate Data in Excel
Consolidate in Excel is used to combine the data of more than 2 workbooks which is available in the Data menu tab under the Data tools section with the name Consolidate. For this, we must have the same type of data in different workbooks. Although different data sets will also work, there will not be proper alignment in consolidated data. Choose any mathematical function which we want to execute at last. Then select all the data using references from all the workbooks and click on OK. This will combine the selected tables with the execution of the chosen mathematical function at the end.
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 than 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. First, here are the sample data files. Here, you can see the different worksheet names and the last consolidated file we have to work on.
This is our template in the “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, the 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 consolidated yearly sales product-wise.
Here we have data in four sheets; the 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 the “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 the 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 a “Consolidated file”.
Now, like before, we will go to the Data tab<Consolidation. Select “Sum” from the drop-down list.
We will now select the reference from our datasheets. We will start this with the “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 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 contain 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 the 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, check the “create links to source data”, which 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 a downloadable excel template. You can also go through our other related articles –
