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.
A simple an example:
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...
...I’m 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.
The Best Possible Way for Combining Excel Files by Merging data into ONE Workbook - POWER QUERY
If you ask me about one method, not two, which you need to combine data from multiple Excel files into a single one, I’d love to say it’s POWER QUERY.
But now, let’s learn this entire thing step by step...
Make sure to read this, don’t miss, please
This is Important.
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 worksheet name as a key to summarizing all that data.
One more thing:
As I said, you can use 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 same.
So now, while combining files using power query you can use worksheet name instead of 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.
Why Power Query is the Best Way to Merge Data into a Single File?
Yes, I’m a huge fan of Power Query and you’ll be if you get to know this:
It’s Real Time:
Once you create a query which combines data from multiple workbooks into one, you don’t need to do it again and again.
If you add new files to that your folder, you just need to refresh the table,
or, refresh the query,
or, use the refresh button.
And the power query will update your table and you’ll get all the data into the table from all the workbook you have in that folder.
If combine files manually or even by any other method then it takes time for you to copy-paste data into a single table.
But power query does it in the background and you don't need to spend a second extra.
Merge Data from Multiple Workbooks When you don’t have the Same Name for Worksheets and data in Tables
This is the hard truth…
…that in some situations, you won’t have the same name for worksheets and not all the data in tables all the time.
Now, what you should be doing in that case?
…in this case, you must know how you can combine data from all the files and I don’t want to miss to share with this thing with you.
...so without any further ado, let's get started.
- First of all, open the “From Folder” dialog box to locate the folder where you have all the files.
- Now in this dialog box, locate the folder and click OK.
- After that, click on the “Edit” to edit the table.
- At this point, you will have a table like below in your power query editor.
- Next, select the first two columns of the table and click on the “Remove Other Columns” from the right-click menu.
From here, we need to add a custom column to fetch data from the worksheets of the workbooks.
- For this, go to Add Column Tab and click on the “Custom Column” button. This will open the “Custom Column” dialog box.
- In the dialog box, enter =Excel.Workbook([Content]) and click OK.
…at this time you have a new column in the table but next, you need to extract data from it.
- Now, open the filter from that newly added custom column and click OK to expand all the data into the table.
- Here you have the newly expanded table with some new columns.
- Now from this new table, delete all the columns except third and fourth.
- So, open the filter for the column “Custom.Data” to expand it and click OK.
The moment you click OK, you’ll get all the data from all the files into a single table…
…you need to make some changes into it to make it PERFECT.
If you notice, all the heading of the column are into data itself...
...so you need to add the column headings.
- To do this, you need to double click on the header and add a name, or you can right click and select rename it.
The next you need to exclude the headings which you have in the data table.
- Now open any column’s filter option and unselect the heading name which you have in the column data and click OK after that.
Now our data is ready to load into the worksheet, so, go to the Home Tab and click on the close and load.
Congratulations! you have just combined data from the different workbooks (with different worksheets name and without any table).
This is also important:
At this point, you have merged the data into one table.
But there’s one thing you need to do…
…and that’s applying some formatting to it and making sure that it won’t go away when you update your data.
Here’s what you need to do…
- First of all, select the column where you have dates (as it is formatted as number right now) and format it as dates.
- After that, make all the columns wide as per the data you have in them.
- Here you can also format amount and price as “Currency”.
But the next thing is to make this formatting fix.
- For this, go to “Design Tab”, and open properties.
- Untick “Adjust Column” width and tick mark “Preserve Cell Formatting”.
- Yes, that’s it.
Now you have a query in your workbook which can combine data from multiple files...
...and merge it into a single workbook...
...even if the worksheet name is not the same or if you don’t have tables.
And yes, you have also made the formatting fix. ?
In the end,
As I said, POWER QUERY is real and if you frequently use to combine data from multiple files then you must use this method…
…as it’s a ONE-TIME setup.
The most important thing is you when you use power query you can even clean the data from those files as well.
Which method do you use to combine data from multiple files?
Make sure to share your views with me in the comment section, I'd love to hear from you. And please, don’t forget to share this post with your friends, I am sure they will appreciate it.
You must Read these Next
- Consolidate Data From Multiple Worksheets: This option can help you to combine data from multiple worksheets into a single one...
- Unpivot Data using Power Query: In this situation, you need to put some efforts and spend your precious time to make it re-usable...
- Create a Pivot from Multiple Files: In this post, I’d like to show you a 3 steps process to create a pivot table by using data from multiple...
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.