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