How to Combine multiple Excel Files in one Sheet in 5 Mins!
Today, my story will be a bit work centric where I had to come up with a solution for 38 excel files and I need to add them in one sheet. Generally if you have 1 MS excel file and many sheets within, it is easy. But if you have multiple excel files and you need to compile multiple excel file in one new sheet, it tough, right!
No. It is easy. Microsoft has something called Power Query for Data Geeks. Large amount of data can slow down your work. But Power Query has a different working method for compiling a large data source. Let’s not talk but jump into the topic.
1 Drop all your excel file in one folder. To make things easier, keep only excel files in the folder. I have 38 excel files here.
2 Make sure all files have the same number heading. Number of columns and their heading should be exact same for easing your work. Row number can vary.
3 Open a new sheet, name it what you want. Then Go to Data>Get Data>From File>From Folder.
Select your folder where you kept your excel files.
4 It should look like this after that. Select Combine>Combine and Load to. It will suggest you where you want to keep the data.
It should look like the following picture. Click the first sheet like in the red box. It will show you a preview. Click OK afterwards.
5 When you click OK. The command Pop up will come with the following picture. Select Table>New Worksheet>OK.
There you go. You just compiled 38 files or whatever your number is within just minutes. You can merge multiple excel files by using power query.
The Best Part . If you have similar more excel files and you need to add them in the existing power query. Do the following-
- Drop your file in the source folder. Make sure the column headers are same.
- Go to the sheet that has compiled data, left click and refresh the sheet.
Tada! Like this you can add as many file as you need in your power query data sheet. in seconds!
That’s all for today.
