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.
How to Combine Multiple Excel Files into One by using Power Query | Quick way to Combine Multiple Workbooks in 6 Steps in Excel
We have yearly sales file/ workbook which we want to combine in a single workbook.
Table of Contents
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.
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:
