If you ask people who work with data, you will get to know that combining Excel files or merging workbooks is a part of their daily work.
Agree ?
The very first thing you need to do is to combine those files in one single workbook and only then you can create your report further.
The point is: You have to have a method which you can use for merging these files. Say “YES” in the comment section if you want to know the best method for this.
Today in this post, we are going to share with you the best way to merge data from multiple Excel files into a SINGLE workbook.
But, here’s the kicker.
This post will teach you something you need to learn to use in the real world data problem so make sure to read the entire post.
Power Query is the best possible way when it comes to combining excel files by Merging data !
Power Query is the best way to merge or combine data from multiple Excel files in a single file. You need to store all the files in a single folder and then use that folder to load data from those files into the power query editor. It also allows you to transform that data along with combining.
It works something like this:
- Saving All the Files into a Single Folder
- Combining them using Power Query
- Merging Data into a Single Table
Note: For combining data from different Excel files, your data should be structured in the same way. That means the number of columns and their order should be the same.
Sample Data file to work with
You can download the sample file to proceed along with this tutorial.
Merging files using Power Query
To merge files, you can use the following steps:
- First of all, extract all the files from the sample folder and save that folder at the desktop (or wherever you want to save it).
- Now, the next thing is to open a new Excel workbook and open “POWER Query”.
- For this, go to Data Tab ➜ Get & Transform Data ➜ Get Data ➜ From File ➜ From Folder.
- Here you need to locate the folder where you have files.
- In the end, click OK, and once you click OK, you’ll get a window listing all the file from the folder, just like below.
- Now, you need to combine data from these files and for this click on “Combine & Edit”.
- From here, the next thing is to select the table in which you have data in all the workbooks and yes, you’ll get a preview of this at the side of the window.
- Once you select the table, click OK. At this point, you have merged data from all the files into your power query editor and, if you look closely you can see a new column with the name of the workbooks from which data is extracted.
So, right-click on the column header and select “Replace Values”.
Here in the “Value to Replace” enter the text “.xlsx” and leave “Replace With” blank (here idea is to remove the file extension from the name of the workbook).
After that, double click on the header and select “Rename” to enter a name for the column i.e. Zone
At this point, your merged data is ready and all you need is to load it into your new workbook. So, go to the Home Tab and click on the “Close & Load”.
Now you have your combined data (from all the workbooks) into a single workbook.
This is the moment of JOY, write “Joy” in the comment section if you love to use “Power Query for combining data from multiple files”.Important Points to remember
- In the above steps, we have used the table name to combine data from all the files and add all of it into a single workbook. But not all time you will have the same table name in all the Excel files and at that point, you can use the worksheet name as a key to summarizing all that data.
- As I said, you can use a worksheet name to combine data with the power query but there are few more things which I want to share with you and you need to take care of those. Power Query is case sensitive, so when combining files make sure to have the name of worksheets in all the workbooks in the same letters.
- The next thing is, to have the same name for the column headers, but here the kicker: The order of the columns doesn’t matter. If column1 in the north.xlsx is column2 in the west.xlsx, Power Query will match it, but you have to have column names the same.
- So now, while combining files using power query you can use the worksheet name instead of the table name, and here you have “SalesData” as the worksheet name in all the files.
- You select it and click on the “Combine & Edit” and follow all the steps which I have mentioned in the above method.
