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

Get FREE Advanced Excel Exercises with Solutions!

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.

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 the 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 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


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.

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, click on the Open button.

Browse the Folder to Consolidate Workbooks

  • As a result, a Power Query 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


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.


<< Go Back To Merge Excel File | Merge in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo