Sometimes, we have datasets containing the same data headings but different values according to their different categories in multiple workbooks. For data analysis and quick summarization, we need to combine those workbooks. In this article, I will show you how to consolidate data in Excel from multiple workbooks at large using 2 effective methods.
What Is Consolidation in Excel?
Consolidation in Excel means the combining of pieces of information from multiple datasets/worksheets/workbooks into one. It also enables the readers to have a quick understanding of multiple workbook datasets.
How to Consolidate Data in Excel from Multiple Workbooks: 2 Ways
Suppose you individually have a dataset for income, expense, and savings for January and February from 3 consecutive years. Say, you have the data in two different workbooks named January.xlsx and February.xlsx. Now, you want to consolidate these two workbooks into a single workbook. You can accomplish this in two effective ways that are described below. 👇
1. Use Excel Consolidate Feature to Consolidate Data from Multiple Workbooks
You can combine data from multiple workbooks using the Consolidate tool. Follow the steps below to do this.
📌 Steps:
- First and foremost, open all the workbooks that you want to consolidate. Remember, you need to make sure that the data you want to consolidate are organized in the same way.
- Now, open the file where you want to consolidate the other files. Following, click on the B5 cell. Subsequently, go to the Data tab >> Data Tools group >> Consolidate tool.
- At this time, the Consolidate dialogue box will appear. Here, choose the function as Sum. Subsequently, in the Reference text box, refer to the B5:E8 cell from the Consolidating January Data worksheet. Make sure, all the options of the Use labels in the group are ticked. Finally, click on the Add button.
- Now, your first worksheet is added to the Consolidation dialogue box. Following, add the B5:E8 cell from the Consolidating February Data in the same way.
- Last but not least, after adding your desired files and data range, click on the OK button.
- Thus, you can see the two workbooks are consolidated in your desired workbook. For example, it would look like this. Besides, they are automatically grouped by your workbooks. As a result, there are level tabs and show/hide toggle buttons to show or hide the groups.
Moreover, you can expand the groups by clicking the toggle buttons. Consequently, you can see the two workbooks are grouped and consolidated in an organized way. For instance, the outcome should look like this.
Read More: How to Combine Multiple Workbooks to One Workbook in Excel
2. Use the Power Query Tool to Combine Data in Excel from Multiple Workbooks
You can also use Excel Power Query to consolidate multiple workbooks. Go through the steps below to do this.
📌 Steps:
- Initially, keep the workbooks that you want to combine in a separate directory.
- Next, open the file where you want to consolidate the desired workbooks. Now, select any cell. Subsequently, go to the Data tab >> Get Data tool >> From File option >> From Folder option.
- At this time, the Browse window will open. Here, select the directory where you have put your workbooks previously. Subsequently, click on the Open button.
- As a result, a Power Query window will open now. Following, click on the Combine button below.
- Now, choose the Combine & Load option from the dropdown listed options.
- At this time, the Combine Files window will open. Select the Consolidate Data in Excel table from the left side portion. Finally, click on the OK button.
Thus, you can combine your multiple workbooks into a single workbook. And, the resulting outcome would look like this after some custom formatting.
Read More:Â How to Merge Excel Files Based on Column
Things to Remember
- Using the Consolidate tool will enable you to use functions between the workbooks’ datasets. Besides, it will group the dataset according to the workbooks. But PowerQuery will not give you this advantage. It will instead just copy and paste the values from the workbooks into the consolidated workbook.
Download Practice Workbooks
You can download our practice workbook from here for free!
Conclusion
To sum up, I have shown you 2 suitable ways to consolidate data in Excel from multiple workbooks. Read the full article and apply the method that suits you the best. Besides, you can practice from our workbook here. I hope you find this article helpful and informative. If you have any further queries or recommendations, feel free to comment.
Related Articles
- How to Merge Excel Files into One Using CMD
- How to Consolidate Data from Multiple Workbooks in a Single Worksheet
- How to Compare and Merge Excel Workbooks
- How to Merge Excel File into Word Document
- How to Merge Multiple Excel Files into One Sheet by VBA
- Combine Multiple Excel Files into One Workbook with Separate Sheets
