How to Consolidate Data from Multiple Workbooks in a Single Worksheet

Get FREE Advanced Excel Exercises with Solutions!

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.

Consolidate Data for Similar Tables from Multiple Workbooks to single worksheet

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.

Consolidate Data for Similar Tables from Multiple Workbooks to single worksheet

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

Consolidate Data for Similar Tables from Multiple Workbooks to single worksheet

  • A window appears showing the file details.

  • Now, press the Combine & Load button.

Consolidate Data for Similar Tables from Multiple Workbooks to single worksheet

  • Select the Profit table and press OK.

Consolidate Data for Similar Tables from Multiple Workbooks to single worksheet

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

Consolidate Data for Similar Tables from Multiple Workbooks to single worksheet

  • 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


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.

Data from Multiple Workbooks with Same Worksheet Names to a single worksheet

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.

Consolidate Multiple Workbooks with Different Table and Sheet Names in an single Excel worksheet

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.

Consolidate Multiple Workbooks with Different Table and Sheet Names in an single Excel worksheet

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

Consolidate Multiple Workbooks with Different Table and Sheet Names in an single Excel worksheet

  • Only, two columns are shown here.

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

Consolidate Multiple Workbooks with Different Table and Sheet Names in an single Excel worksheet

  • 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.
=Excel.Workbook
  • After writing the formula a green check mark will show that indicates no errors are available to her.
  • Finally, press OK.

Consolidate Multiple Workbooks with Different Table and Sheet Names in an single Excel worksheet

  • We can see a new column added here.

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

Consolidate Multiple Workbooks with Different Table and Sheet Names in an single Excel worksheet

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

Consolidate Multiple Workbooks with Different Table and Sheet Names in an single Excel worksheet

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

Consolidate Multiple Workbooks with Different Table and Sheet Names in an single Excel worksheet

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

Consolidate Multiple Workbooks with Different Table and Sheet Names in an single Excel worksheet

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

Consolidate Multiple Workbooks with Different Table and Sheet Names in an single Excel worksheet

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

Consolidate Multiple Workbooks with Different Table and Sheet Names in an single Excel worksheet

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

Consolidate Multiple Workbooks with Different Table and Sheet Names in an single Excel worksheet

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


Alok Paul

Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo