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.

Navigation to Power Query Excel Feature

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:

  • Using the power query feature is very user-friendly. There is no coding or complex formula involved in it.
  • Another advantage of using this feature is that, as you perform power query steps to combine excel files, it records and stores those steps. These recorded steps can be reused in the future just like we reuse the macro recordings.

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.

Sample Purchase Data November

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

  • Open a new excel workbooks and then navigate to ‘Data’ tab > ‘Get & Transform Data’ group > ‘Get Data’ option > ‘From File’ > ‘From Workbook’ as shown below:
Get Data from Workbook Navigation
  • In the ‘Import Data’ dialog box that appears, navigate to the location having the excel workbook. Select the workbook and click ‘Import’.
  • In the ‘Navigator’ dialog box, you can see the list of all the objects available in the selected excel file on the left pane. There are two objects in the October month’s purchase data excel file – ‘Sheet1‘ and ‘Table1‘. This is the reason why I mentioned above that the dataset in the individual excel workbooks should be a proper Excel table.
Preview of Table from Excel Power Query
  • Since we have to combine this imported data with the November month’s data, therefore, we shall store it temporarily as a ‘Connection’. To store data as connection, click on the downward-facing arrow next to the ‘Load’ button and select the option ‘Load To’.
Load To Option Navigator Window
  • In the ‘Import Data’ dialog box, select the option ‘Only Create Connection’ and OK.
Create Connection Only Power Query
  • As a result, excel would store the Excel table for October in the form of a connection.
October Data Stored As Connection

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.

Power Query Connections for Oct Nov

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

  • Simply right click on any of the connection and choose the option ‘Append’ as shown in the image below:
Right Click Connections to Append
  • In the ‘Append’ dialog box that appears, simply select the radio button ‘Two Tables’, choose the tables from the drop-down list, and click OK.
Append dialog box Power Query
  • As a result, the ‘Power Query Editor’ window would pop up on your screen. Here you can see the appended tables.
Appended Table Power Query Editor
  • Change the Query name like shown below:
Change Power Query Name Excel
  • Let us finally get this data to Excel worksheet. To get the appended table back to excel, simply go to the ‘Home’ tab > ‘Close & Load’ option.
Close and Load Power Query Excel

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

Combining Multiple Excel Files Using Power Query

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.

New Rows Added to Source Excel File

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

Right Click Refresh Appended Table Power Query

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

New Rows added to Appended Table

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

Leave a Comment