If you want to merge multiple Excel files into a single workbook, you can do it with Power Query.

This article will show you 2 techniques to do the job.

Step 1: Import from Folder

Power Query is a wonderful tool with many options to import and manipulate data.

1. Open the option Get Data > From Files > From Folder.

2. Then, select the folder name where are your Excel files.

3. And the next dialog box shows you all the files in this folder and sub-folders.

4. Click on the button Transform Data to continue the process of importation

Step 2: Select your files

For the moment, we have selected all the files in the selected folder and subfolders. Fortunately for us in this example, all the files have xlsx extensions. But, you can use the filter to select only the xlsx files.

1. Right-click on the extension value (in a cell) with the information xlsx
2. Then, the option Text Filters
3. Finally, select the option Equals

Only the multiple Excel files are kept, ready to be import.

Step 3: Keep the column Content

Once you have filtered the files you want to combine, you must keep the column Content.

1. Right-Click on the header of the column Content
2. Select Remove Other Columns

And you have this

Step 4a: Easiest technique

Now, you can directly combine your file just by clicking on the arrow on the header of the column

Then, you select the Sheet name or the Table name to import. All the files will follow this rule.

And just like that, all your files are combined 😀👍

With this technique, Power Query generates 4 queries to merge many Excel files.

Step 4b: Write an M function

This second technique needs to use one M function because this option doesn't exist in the ribbon

1. Go to the ribbon Add Column
2. Then Custom column

And next, you insert the M function Excel.Workbook with the column Content as argument.

=Excel.Workbook([Content])

This function converts the contents of your file into a Table.

Step 6: Select the Data column

Behind a Table, there is more than one column to expand. With this technique, you expand the column Data

And now you have this

The column Content must be removed

Step 7: Expanded the column Data

In the final step, you just have to click on the icon Expand to visualize the content of all your Excel files.

This technique is 4 times faster than the first technique