How to Consolidate Data from Multiple Workbooks in a Single Worksheet

Consolidating data from multiple is a complex process. MS Excel has no direct function or formula for this. There is one way that is Excel Power Query. This Power query can easily consolidate data from multiple workbooks into a single worksheet in Excel.

Download Practice Workbooks

Download this practice workbook to exercise while you are reading this article.

3 Examples to Consolidate Data from Multiple Workbooks in a Single Worksheet

Here, we will discuss 3 examples based on Excel Power Query to consolidate data from multiple workbooks into a single worksheet.

1. Consolidate Data of Similar Tables from Multiple Workbooks

In this section, we will consolidate data from tables with the same name from multiple workbooks. Follow the steps below.

📌 Steps:

• We will consolidate 4 Excel files.

• We have a table named Profit in our dataset.

Each of the 4 Excel files has tables of the same name and format.

• Now, we will apply the Power Query.
• Click on the Data tab.
• Choose From File of the Get Data option.
• Finally, choose From Folder option.

• Now, select the Data folder from the File Explorer.
• Finally, press the Open button.

• A window appears showing the file details.

• Now, press the Combine & Load button.

• Select the Profit table and press OK.

• We can see all data are combined from multiple workbooks into a single sheet.

If we want to add more files to the dataset, it’s a simple task. Just add a file in the same folder of the same format.

• We add a new file named Central.

• Now, go to any cell of the combined file. Click on the right button of the mouse.
• Choose the Refresh button option from the Context Menu.

• Have a look at the dataset.

New data is included in the combined file.

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

Similar Readings

• Consolidate Function for Text Data in Excel (with 3 Examples)
• Remove Consolidation in Excel (2 Handy Methods)
• How to Consolidate Data from Multiple Rows in Excel (4 Quick Methods)

2. Merge Data from Multiple Workbooks with Same Worksheet Names

In this section, we will consolidate data from multiple worksheets of the same sheet name.

📌 Steps:

• Here, we can see the Sheet Name at the bottom section.

All the workbook consists of the same name worksheet.

• Now, click on the Data tab.
• Now, follow Get Data >> From File >> From Folder.

• Select the desired folder from the File Explorer.
• Finally, press Open.

• Now, a window appears with the file name and details.
• Click on the Combine & Load option.

• Choose the Sheet name from the Combine file window.

If we have other sheets in the workbooks, those will also show here.

• Lastly, press OK.

Data from multiple workbooks with the same-named worksheets are combined here.

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

3. Consolidate Multiple Workbooks with Different Table and Sheet Names

In the last two examples, we saw that data was consolidated for multiple workbooks with the same table or sheet names. But here, we will see different worksheets and table names at the same time.

📌 Steps:

• Look at the table name and sheet name of the East file.

• Again, look at the West file.

Table and Sheet Names are different for both cases.

• Now, click on the Data tab.
• Go Get Data >> From File >> From Folder.

• Choose the desired file from the File Explorer.
• Then, press the Open button.

• The file with details is shown here.
• Click on the Transform Data button of the window.

• We enter the Power query mode.

We do not need to appear in all the columns here.

• Choose the Content and Name column and press the right button on the mouse.
• Choose Remove Other Columns option.

• Only, two columns are shown here.

• Now, press the Add Column tab.
• Then, select the Custom Column option.

• The Custom Column window will appear.
• Insert a name Import Data from the new column.
• We need to use a formula here. Out the exact formula here, then click on the Content option.

• After writing the formula a green check mark will show that indicates no errors are available to her.
• Finally, press OK.

• We can see a new column added here.

• Now, click on any cell of the Import Data details shown here.

• Now, expand the Import data column.
• Click on the right-upper side of the Import Data column.
• A menu shows to check options. We uncheck the Use original column name as the prefix.
• Finally, press the OK button.

• Data has expanded here.

• Here, both Sheets and Tables are shown. But we want only sheets and tables will be included in the Sheets.
• Click the arrow if the Kind column.
• Check only the Sheet option.
• Then, press OK.

• We do not need all the rows here.

• Choose the Name and Data columns and press the right button on the mouse.
• Click on the Remove Other Columns option.

• We can see the two columns.

• Now, click on the right-upper corner of the Data column.
• Similarly, uncheck the Use original column name as the prefix.
• Finally, press OK.

• We get all the data.

• Now, click on the Transform tab.
• Choose Use First Row as Headers option.

• The header has changed now.

• Now, we want to remove the errors. Press the right button of the mouse. Click on the Remove Errors from the Context Menu.

• Go to the Home tab.
• Click on the Close & Load option.

• We get our data exactly.

Conclusion

In this article, we described 3 examples in different situations. According to your need and situation, you can consolidate data from multiple workbooks into a single worksheet. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.