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.

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

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

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

• How to Create a Linked Consolidation in Excel (2 Useful Methods)
• Build a Static Consolidation in Excel (2 Suitable Examples)
• How to Use Grouping and Consolidation Tools in Excel (5 Easy Examples)
• Consolidate Function for Text Data in Excel (with 3 Examples)
• How to Remove Consolidation in Excel (2 Handy Methods)
• Consolidate Data from Multiple Rows in Excel (4 Quick Methods)
• [Fixed]: Consolidation Reference Is Not Valid in Excel (with Quick Fix)