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?
A simple an example: Let’s say you want to create a sales report and you have data of four different zones in four different files.
Now:
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
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 Quer
- Merging Data into a Single Table
Make sure to download these sample file from here to follow along and check out this tutorial to learn power query.
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.
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 Point
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.
One more thing:
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.
Why Power Query is the Best Way to Merge Data into a Single File?
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?
Well…
…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.
Make sure to download these sample files to follow along and save it on your desktop.
...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.
I hope this tutorial will help you to Get Better at Excel. But now, you need to tell me one thing.
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
- Create a Pivot from Multiple Files
- Perform VLOOKUP in Power Query in Excel
- Unpivot Data in Excel using Power Query
- Power Query: Concatenate Values (Text and Numbers)
- Power Query: Date Difference
- Power Query: Functions and Formulas
- Power Query: IF Statement
- Remove Duplicates using Power Query
- Power Query: IF Text Contains
- Power Query: Get Today’s Date
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 find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.
Thank you this was helpful. However, I need to combine passsword protected excel files into single file. Any tips/suggestions are appreciated.
It was very helpful. I want to merge data from 37 different excels and with the help of your information it happens in less than 5 minutes. Thanks a lot.
Joy
Really appreciated. Thank you.
Thank you so much Puneet. It is very helpful. I will be looking at your other tutorials too. Thanks again
That was great – thanks a million!
Thanks so much for this, Puneet!
My version of Excel is limited on my work laptop but I have Power BI Desktop. Will the Power Query functionality inside Power BI essentially work the same way?
Yes 🙂
Lots of files downloaded from processing database. unfortunately all sheet / tab names are unique. So i would have to go into each sheet to modify sheet name in order to successfully use this method. Think i’ll leave them as csv and just use python… Thanks anyway. always good to know.
THANK YOU SO MUCH
Very helpful. Thank you so much for this!
Thank you!
“As you 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”
How can I refresh the combined data when I have more files or want to delete 1 old file. eg.: when I add 1 more file “EAST2” and delete file West, and I don’t want to do all steps again to merge these new group of files (East2, East, South, North). Are there any ways to refresh combined data when source files update like in PBI
“when I have more files or want to delete 1 old file” that won’t create a problem but the data needs to match the steps you create in the query. For Example: when the format of the data changes in a new file that breaks the query steps in some way. But that can also be tackled down, you just need to create a dynamic query. good day.
Thanks alot for this team
Hi, I used the method mentioned on WIN 10 machine and found the the content in the file is lost with error while opening “Problems During Load”, please advice.
Can I have that screenshot on puneet-at-excelchamps-dot-com / puneet-at-gogia-dot-me
(I received an DataFormat.Error) External table is not in the expected format.
Joy
Hello!
Thank you for the article. I’ve tried to merge data from multiple csv files where you don’t have the same name for the sheets and results an error.
“DataFormat.Error: External table is not in the expected format.
Details:
Clienti_20220401.csv”
Do you have a solution also in this case?
JOY
Wow, Fantastic …. Joyyyyyyyyyyyyyyyyyy 🙂
Absolute Joy! I did a big fist pump when the data successfully loaded. Thank you so much!!!
Super I want study its
“JOY”
Yes, it brought “joy” to my heart.
Thanks so much – Well done!!
Great , Awsome, Thanks and always i support
Hi Mr Puneet,
How “get data>from file>from folder” using MS excel 2013? There are no icon
Thank you
In 2013, you need to install it. https://www.microsoft.com/en-us/download/details.aspx?id=39379
This is fantastic – is there a way of keeping the data in the new file despite the source data being deleted?
You need to use a macro for that.
Great info, easy to follow. Merged 25 sheets into one!
Fantastic article. I need to club 4 files in 1 file as 4 separate sheets. This needs to be done for 2000 such files which should become 500 files with 4 tabs each. Is there a quick way to achieve this rather than doing manually for each set of 4 files ?
Dear Puneet,
Thank you for sharing the tutorial.
It works well for me, except at one point for which I need your help.
I have data and in column I have floating type data. Example: 0.5, 2.5, 4.5
However, when data is consolidated from multiple documents it rounds off floating numbers to lower value.
Example: 0.5 changes to 0, 2.5 charges to 2.
Thus, please help to resolve the issue.
Regards
Taruchit Goyal
what data type it’s changing to? check from: transform -> any column -> data type
Very nice info. I love it and every step is very easy to follow through. Thank you very much, you make my day brighter.
Perfect
easy and perfect . Thanks
Thanks Puneet, it’s really amazing and very helpful item.
Excellent and pretty easy
Thank you so much for this awesome tutorial! Is there a way to merge files if they have a different number of columns though? I have some files that have 2 extra columns at the beginning. I have a TON of files to combine so I want to avoid opening each file to delete those 2 columns (or opening the other files to add 2 blank columns) if at all possible.
Yes, there should be a better way to do for the situation you are telling, let me come back to you.
Thank you!
Very informative and helpful blog, I love it!
Thank you soo much Puneet!
You’re Welcome.
Thanks for sharing this useful and informative blog. it’s really useful.
Brilliant, as always.Puneet
Power Query is unfortunately unavailable to previous Excel
For compatibility issues with installed macros i’m using Excel 2007.
I can recommend “ASAP UTILITIES” which has, among many other things, a comprehensive IMPORT utilities which helps resolve these kind of tasks…
Dear Puneet,
Thank you very much
Dear Levanon,
Thank you and appreciating very much for your “ASAP UTILITIES” Tip.
I am using MS Office 2007 for my personal use.
Dear Levanon,
Thanks for your very valuable tip as I’m using Office 2007 and your suggestion is very welcome.
Best regards,
Nishantha