How to Consolidate Data in Excel from Multiple Workbooks (2 Methods)

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.


Practice Workbooks

You can download our practice workbook from here for free!


What Is Consolidation in Excel?

Excel consolidation 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.


2 Ways to Consolidate Data in Excel from Multiple Workbooks

Suppose, you have a dataset for income, expense, and savings for January and February individually 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.

Keep the Multiple Workbooks Data Organized

  • 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.

Use Consolidate Tool to Consolidate Multiple Workbooks in Excel

  • 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 group are ticked. Finally, click on the Add button.

Add Workbook to Consolidate Data from multiple Workbooks

  • 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.

Add Another Workbook to Consolidate Data from Multiple Workbooks

  • Last but not the least, after adding your desired files and data range, click on the OK button.

Confirm the Consolidation

  • 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.

Consolidated Workbook from Multiple Workbooks

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.

Whole Summary of Consolidated Workbook

Read More: How to Consolidate Two Sheets into One in Excel (3 Useful Methods)


2. Use 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.

Open Multiple Workbooks to Consolidate

  • At this time, the Browse window will open. Here, select the directory where you have put your workbooks previously. Subsequently,m click on the Open button.

Browse the Folder to Consolidate Workbooks

  • As a result, a PowerQuery window will open now. Following, click on the Combine button below.

Combine the Selected Multiple Workbooks

  • Now, choose the Combine & Load option from the dropdown listed options.

Load the Selected Workbooks to Consolidate

  • 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.

Confirm the Consolidation

Thus, you can combine your multiple workbooks into a single workbook. And, the resulting outcome would look like this after some custom formatting.

Consolidated Multiple Workbooks by Power Query

Read More: How to Automate Consolidation in Excel (with Easy Steps)


Things to Remember

  • Using 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 rather just copy and paste the values from the workbooks into the consolidated workbook.

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.

And, visit ExcelDemy to know more about Excel features and so on. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo