How to Combine Multiple Excel Files into One by using Power Query | Quick way to Combine Multiple Workbooks in 6 Steps in Excel

Combine multiple excel files into one excel file may be a challenge for many excel users. Most of the times it is being done manually by using copy paste method, one after another. The challenge in the copy paste method is time. It is consuming too much time as one by one workbooks needs to open, copy the data, and paste below other. Now excel power query has powerful option to combine multiple workbooks into a single workbook. It is also a quick process. Let’s investigate the simple steps so that you can apply in your day-to-day job. In this post, you’ll get to know that how to combine multiple excel files into one.


We have yearly sales file/ workbook which we want to combine in a single workbook.

As a first step, place all the sales files in a single folder. Here folder name called “Combine”, where four files are present (2011 – 2014).

Create a file outside the combine folder. Kept file name as “Combined sheet”.

In the combined sheet, follow the steps below to link “Combined” folder and import & combine four sheets.

Data Tab -> Get Data (Get & Transform Data) -> From File -> From Folder -> Select the folder from pop-up window.

Import data from folder

You’ll be getting three options if click on combine drop down, “Combine & Transform”, “Combine & Load” and “Combine and Load to”. Better to use the first option that is Combine & transform so that if you want to do any operations, it can be performed. In case of Combine & load, it is directly combined and load to the excel sheet.

Combine data option

Different option to combine data

Next step is to select the required sheet or visualize the sample data and move to power query editor. In this case we have only one sheet, data.

Select data sheet

Power query editor gives you the option to do any data operation like filter any rows or column, remove error from data and many more. Here we are not doing any sort of operation and will load as it is combined data.

Power query editor

Save the changes in power query editor

Hope this will help to save your time and improve automation capabilities in your day to day job.

All the best.

Please follow and like us:

Leave a Comment