Excel Unlocked

Merging or Combining Multiple Excel Files without VBA

A question that is being asked on various forums from excel users – “How to combine multiple excel files into one?”. Many excel experts suggest using the VBA Editor window to create a subprocedure for the same. However, why to go for a complex VBA coding, when you can use a standard excel feature to combine multiple excel files into one excel file. Excel Power Query Feature will help you in combining or merging all or multiple excel files.

Excel power query (also popularly known as ‘Get & Transform) is an amazing feature provided by Microsoft in its office suits to extract data from external sources into excel and play around with it. If you are an Excel 2016, 2019, or Office 365 user, then check the ‘Data’ tab > ‘Get & Transform Data’ group. Here, you can find all the Excel Power Query options.

But, if you are using Excel 2010 or Excel 2013 versions, then no need to worry. There is an excel power query add-in downloadable from the Microsoft Software Center. Check this link.

What are Advantages of Using Power Query?

Excel users prefer using the power query feature to get data from external sources because of the following advantages:

To learn more about basics on power query, check our blog on – Overview of Power Query in Excel

Sample Data for Combining Multiple Excel Files

Suppose you have different excel files having purchase data for different months. All the files have the same table headers. Now, you want to combine all these purchase data tables into one excel file, one below the other.

Make sure that the data in the individual excel workbooks are in the form of an Excel table. If not, then click anywhere on these individual datasets, and use the keyboard shortcut ‘Ctrl + T’. In the dialog box – ‘Create Table‘, simply click OK.

Steps by Step Guide for Combining or Merging Excel Files

To combine multiple excel files to one excel file, involves two activities:

  1. Get data from multiple excel files and store it as query connections
  2. Append these query connections (i.e. place the data one below the other)

Store Data From Multiple Files As Connections

Similarly, perform all the above steps to store the November month’s data as well. Finally, you have two connections ready with you to merge.

Append Connections in Power Query

Once the connections are ready, let us place the table data from these multiple excel files one below the other. It is known as “Appending data or connections in power query”.

Resultantly, you have your combined excel file ready with you. In total, 600 rows of data are now loaded into the excel file.

What If There is Some Change in Source Table?

The Excel power query feature is not used just to get data from sources. The steps stored as queries and connections are reusable steps. Meaning, when there is any change or new rows of data in the source excel files (October and November month’s), then you can get this updated data appended quickly by a simple activity of power query refresh.

Let us, for example, add some more rows of data to October month’s file.

To get this updated data to the appended table, simply right-click anywhere on the appended table and choose the option ‘Refresh’.

As a result, within few seconds, you have the new rows added to the appended table.

With this, we have completed this tutorial on combining or merging excel tables from multiple excel files into one.

Exit mobile version