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.

Table of Contents

Toggle

Place all the required file in a single folder

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

Open a workbook where files need to be combined

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

Link the folder to import data

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.

Click on Combine & Transform data

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.

Tips: One useful option to combine data is to combine in a pivot table to create auto summary while refresh data. It is available in the “Combine Load To…” option.

Select the sheet

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.

Power Query Editor

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.