How many times you wanted to find a quick way to combine multiple Excel files into one workbook without Copy-Pasting?

What if there was a way to combine data from multiple files and update the Excel workbook every time the source files changes, or a new Excel file is added to the folder? 

Let’s learn how to do it with Excel’s magic tool called “Power Query”! 🙂 

Combining data from multiple workbooks in the same folder with Power Query – Step by Step

Click here to download the files used in this tutorial and practice Power Query!

Let’s assume we are store owners, and we have sales data from 2016-2018 in 3 different files which are in the same folder:

Each file contains data regarding the Date, Item, Quantity and Price:

 

We can quickly combine the data from each of these files using Power Query!

First, let’s open a blank Excel workbook.

Then, go to “Data” tab – Get Data – From File – From Folder:

Now, select the folder which contains the Excel files you want to combine and click OK:

Now, we can see the files that we have in the folder. Click Transform Data (or Edit Data, in previous versions):

Now, we can see in the Power Query editor the Excel files. Next, we have to click the button circled in red:

Next, Power Query asks us to show us an example file to understand how to combine the files. 

Select Sheet1 and click OK:

Now, we can see a preview of the data from all the Excel files combined:

Just one more step… Click Close & Load and Select Close & Load, to load the data as an Excel table to your Excel workbook!

And now we can our data in the Excel Workbook:

We can see in the “Source.Name” column the name of the file from which we imported the data!

And here we can see the data from all the years 🙂

What happens if we update data in the folder?

Now, here’s the really cool thing about Power Query – If you will add a new sales file to the folder, let’s say – “Sales 2019” data, it will automatically be updated in the consolidated file! 

Here’s the additional sales data from 2019: 

All you have to do is to make sure to save the “Sales 2019” file in the same folder as the other files!

Now, go to “Data” tab and click “Refresh All”! 

And that’s it, our data is refreshed, and the new data is loaded:

Note that you can also edit the data in each of the sales files – add rows, change prices and dates – and once you save the edited file and click “Refresh All” – The updated data will be loaded to your Excel file!

Practice Power Query – Combine data from multiple excel files

Click here to download the Sales Data zip file

Here are some quick instructions:

• Save the “sales data” folder in your C drive (You can save it elsewhere as well, but the solution file refers to C drive):

• Open “Combined Sales Data – Exercise” which is an empty Excel workbook, and try to perform the steps described in this tutorial.
• To add the additional “Sales 2019” data, move the “Sales 2019” Excel file to the sales data folder saved on your computer, and click Data – Refresh All in Excel!
• If you want to see the final result, open “Combined Sales Data – Solved” workbook (Without the 2019 sales data…)