This tutorial is a detailed step-by-step guide to data consolidation in Excel. The Data Consolidation tool combines or consolidates data in different worksheets or workbooks in Excel.
Also read: How to Pull Geographical Data (County/City) in Excel?
What is Data Consolidation in Excel?
Data consolidation in Excel enables a user to consolidate data from a separate worksheet to a master worksheet or, from a different workbook to a master workbook.
Essentially, it grabs data from a series of sheets or even workbooks and brings it together in a single worksheet in Excel.
However, this feature can sometimes play off tricks if you aren’t quite aware of its usage. Well, do not be overwhelmed. It shouldn’t be misunderstood to be a faulty feature.
To avoid some of the common errors that occur during the data consolidation process, we discussed resolving those, towards the end of the guide.
Steps to Consolidate Data in Excel
Let’s get started with this detailed step-by-step guide to consolidating data from one or more sheets or files in Excel. Consolidated data can be combined by adding, multiplying, averaging, counting, etc. in a single sheet.
1. Consolidate data from one or more sheets
To consolidate data from multiple sheets in Excel, do as follows next.
First, we will prepare datasets in three different worksheets in a file like this.
- Create a new sheet in the same workbook in which you want to consolidate the data from the three worksheets. Here CONSOLIDATE.
- Go to the Data tab on the ribbon.
- Under the Data Tools section, select Consolidate.
- A window named Consolidate opens.
- Choose a function you wish to apply to the consolidated data. For instance, choosing Sum would sum all values in the three datasets in the new sheet.
- Once you have chosen a function, click in the Reference dialogue box.
- Navigate to the first sheet from where you want to start consolidating data. Here 2017.
- Select the data range in that sheet, and do not forget to select the headers too (top and/or left).
- Select the entire table like this.
- Click Add after each selection, and it gets added to All references.
- Repeat the steps for the remaining two tables in separate sheets.
- Click Delete if you want to delete a selection from being consolidated.
- In the Use Labels in section, you have a few options to add or not add your headers.
- Check Top Row if you want to add the top row of each table in the consolidated data.
- Check Left Column if you wish to add the left columns of each table in the consolidated data.
Pro Tip: To name your selections or references before going ahead with the consolidation process, place your cursor on the Reference field in the consolidation window, press F3 or Fn+F3 and select a reference under All References.
Let us move on to consolidating the selected datasets.
- Now that we created source or reference data in three different sheets, we will consolidate data in a whole new sheet.
- To go about it, create a new sheet in the workbook.
- Select a cell anywhere in the sheet and the Consolidate window, hit OK.
You will now see the consolidated data showing up in the sheet. Here, we have opted to sum all the values. If you opt to count or multiply the values, you will see different results.
Here’s how the consolidated data looks with a few stylings.
Resolving error: Source reference overlaps destination area
At this point, make sure the cell ranges of each table are different. The ranges must not overlap each other, which is what results in this error.
If the cell range of a table in the first sheet is A1:E6, make sure the tables in the other sheets have been placed on different ranges and they do not overlap as well.
Resolving error: Cannot create links to consolidation sheet
You must have faced this error when you tried to consolidate data and checked the Create link to source option in the dialogue box. However, this option must not be selected before consolidating data. This is when you see this error.
It gets a bit tricky here when you want to enable an auto-update feature for the consolidated data. Follow the steps below to avoid this error.
To auto-update the consolidated data table on changes, select the consolidated data table, click Consolidate in the Data tab. In the Consolidate window, under the Use Labels in section, check the Create link to source data option.
Consolidate data from multiple workbooks to a new workbook
To consolidate data from multiple workbooks to a new workbook, follow the steps below.
Before we start, make sure all workbooks that you wish to consolidate are open.
- Create and open a new workbook as your master workbook. This will be where you will consolidate all your data. Here, consolidate.xlsx.
- Let’s rename the worksheet to Consolidate and select a cell in this sheet.
- Click on the Data tab and click the Consolidate button to open the Consolidate dialogue box.
Repeat the step of selecting the tables from every workbook in the same way as we did in the first example.
The only step that is different here is that we’re selecting references from different workbooks using the Browse button, beside the Reference field in the Consolidate dialogue box.
Conclusion
This tutorial walks you through a detailed step-by-step guide on Data Consolidation in Excel. This piece explains two ways to consolidate data i.e., consolidating data from multiple sheets to a new sheet. Second, consolidating data from multiple workbooks to a new workbook.
Reference: Happy.