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

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.

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

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 the From Folder option.

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

• A window appears showing the file details.

Click on the image for better quality

• Now, press the Combine & Load button.

Click on the image for better quality

• Select the Profit table and press OK.

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

Click on the image for better quality

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

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

Click on the image for better quality

New data is included in the combined file.

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.

Click on the image for better quality

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

Click on the image for better quality

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

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 in the window.

Click on the image for better quality

• We enter the Power query mode.

Click on the image for better quality

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

Click on the image for better quality

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

• We do not need all the rows here.

Click on the image for better quality

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

Click on the image for better quality

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

Download Practice Workbooks

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

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 give your suggestions in the comment box.