Powerful trick to combine all Excel files in a folder AUTOMATICALLY
Ғылым және технология
Need to automatically combine all files in a folder to One Excel workbook? You are going to love this completely automatic way. This simple but insanely time saving trick uses Power Query to combine files in a folder.
In this video ⏱👇
==============
0:00 - Introduction to the combine files problem
0:36 - A quick look at the folder & one of the files
1:10 - Combining the files with "Get Data → Folder" option in Excel
2:36 - Cleaning up the file data with Power Query rules
5:32 - Loading the data to Excel
5:55 - What happens when you add files to the folder
7:08 - How to learn Power Query
Sample files 📂
============
Here is a ZIP file that has individual monthly files & consolidated workbook (with Power Query connection). chandoo.org/wp/wp-content/upl...
For a similar example, but using PDFs, see this video: • How to Combine PDFs to...
Watch next 📽👉
==============
🔥 [RECOMMENDED] Detailed Power Query Tutorial (80 minutes) - • Power Query Tutorial -...
Power Query tips play list - • Power Query How-to & Tips
Combine sheets in an Excel file - • AWESOME Excel trick to...
Combine Data from PDFs - • How to Combine PDFs to...
Credits:
======
Big bang theory clip from • The Big Bang Theory: C...
Music from Epidemic Sound. Get high quality & fresh songs for your videos from here: www.epidemicsound.com/referra...
Have an awesome day 🌞🌼😀
Пікірлер: 343
Wow that's synchronistic. I was looking for exactly this yesterday evening for a project. Thank you Chandoo 😁😁
@chandoo_
2 жыл бұрын
What can I say.. I read your brain Brian 🤣
@briandelaney6354
2 жыл бұрын
@@chandoo_ 😄😄
Hi Chandoo. I had a side project at work lying around for literally 1 year. Your super concise video was a Eureka moment for ne. Happy I watched your PowerBI & PowerQuery vids and stumbled across this huge gem! Thank you, Chandoo!
You people are real heroes. Many institutions are charges thousands of rupees for this. And i don't think KZread pays similarly, but you choose this option. you literally own me
Thank you so much. Have to combine over half a year's worth of data in files for each month. All from a customer that sends in 1, 2 or sometimes even 3 files a day. Will take me hour to make the invoices tomorrow not days. Thank you so much!
Another great tip. Thank you Chandoo. This will actually help me combine my monthly close down data with less effort. This is really awesome! Never thought about it. More I say thank you less it will be for your effort in teaching Excel.
You have made it so easy to understand. Thanks a lot😊
Straight to point, and straight to point, then some more straight to point. This is what I always look for. Thanks.
@chandoo_
2 жыл бұрын
Thank you TP.
This video just saved my day from combining data from 53 excel folders data into 1 file. Thanks a lot _/\__/\_
WOW i was searching every where to have this, you just make easy, Thanks Chandoo
Thank you Chandoo, this video was very useful and saved me a ton of time. You are brilliant my friend!
I do this all the time. Huge time saver and makes me look good 😊 I always keep the source file name until I know for sure that I do not need it.
I'm following your daily dose from 2013 frequently happy to see u finally on you tube. 😀
The way you explain is excellent. You make tougher Task to Simple. Great. Appreciate.
Extremely helpful and informative video. Thank you Chandoo. Well Done!
Hi Chandoo. Another great tutorial! Love everything related to Power Query. Thanks also for providing the sample data to follow along.. very helpful! Thumbs up!!
@chandoo_
2 жыл бұрын
My pleasure Wayne.
really well explained, thanks so much and its so amazing to just put a file in and it updates!
Thank u it was gr8 to see u on KZread I was one of the oldest lover of your excel tricks specially graph presentation
you are awesome chandoo, i have find every thing for my project on your videos of power querry
Thanks Chandoo! this is really really helpful!!!
Great tip Sen Sei!
Amazing! I didn't know this was possible, thanks Chandoo
Thanks a lot It helped me a lot today for consolidating the data .
Thank you Chanoo I followed all the steps. It works provided after changing the name of the all the Sheets in a folder to same name [ Example: Data]]. I have the files but Sheet ina file different.
Thanku it helps a lot!. While others was using VBA Code for same process, but you taught us simple way to do this.
Excellent teacher. Thank you !
It was really helpful. Thank you Chandoo👏
I am in love with this tutorial¡
That is really awesome... You play with Excel as Sachin Tendulkar used to play with a cricket ball. Watching your videos is a pure pleasure. Thanks a lot.
Commeting before watching ☆ Looks promising
You are just amazing man
That’s awesome! Thank you 🤩
Great tip. I was blown away when I first saw this. I work in accounting, and getting people to stop having 40MB Excel workbooks because tab 1 is 800k lines of data instead storing data as period CSV files in a folder called data, is my calling. :)
@chandoo_
2 жыл бұрын
Wow.. 800k lines per tab... 😮 You are going to LOVE 💚 Power Query.
Great video! Thanks for posting!
Thank you so much its very helpful for it was saved my time during my heavy load work 👍👍👍
Thank you very much for the awesome video... This is what I wanted ☺️
Wow That's a nice knowledge. Thank you Chandoo sir 👍👍👍
Thanks for this. Well done.
Superb...Saved Time
Nice, thanks!
Thank you soo for your valuable tips..
Thank you Chandoo,
@chandoo, your tutorial was great. Especially, the sound effect at 3:55, it brought my attention back 😅
Sir, You are amazing and well explained information. Thank you
Thank you Chandoo!
APPLAUSE APPLAUSE SIR! Yet another great video using combine and queries!!👏👏🎉🎆🎇
@chandoo_
2 жыл бұрын
🙏 Thank you Saloria.
This guy is really good it's a pity I don't really use excel would have bought his course .
Appreciate…Thanks a lot for sharing this EXCEL treasure….Excel never stops revealing itself . Brilliant MS-Office designers and explorers-people like Chandoo. Luv this creative single platform , enough to make n assist various careers.
@chandoo_
2 жыл бұрын
Excel is AWESOME 😎
Awesome!
Thanks a lot, you are wonderful!!! Thank you Chandoo 😁😁
@chandoo_
2 жыл бұрын
So are you...
Informative video 👍👍
Thank you so much. This is an extraordinary.
Thankyou so much, this would save me 1 hour
@chandoo_
2 жыл бұрын
AWESOME 😎
Long wait is finally over. Thank you for your help. Request you to help with an idea to copy specific cells from multiple workbooks in a specific folder
@chandoo_
2 жыл бұрын
Thanks Viswa. Please refer to this advanced example for specific cell situation - chandoo.org/wp/combine-excel-files-using-power-query/
Sir, You are Amazing 😍
Thanks Chandoo
Mabrook!!! Thanks man!
@chandoo_
2 жыл бұрын
😀 You are welcome...
Another useful & awesome tip! I have a huge file with multiple data sheets I plan to consolidate with this method. Thanks Chandoo!
@chandoo_
2 жыл бұрын
Glad it was helpful!
This was amazing! Thank you!
@chandoo_
2 жыл бұрын
You're so welcome!
woww, this way is so perfect and magination
Thanks chandoo ji🙏
Super...thanks a lot chandoo!!
@chandoo_
2 жыл бұрын
You are welcome Mandar. Don't forget to share these amazing tricks with a colleague or friend. Light up their day.
You are the man. Thanks !!!!!
Excellent Chandoo.
@chandoo_
2 жыл бұрын
Thank you Rana.
Thanks, Chandoo - I want to give 100 marks to you for this fantastic data analysis video 📹
@chandoo_
2 жыл бұрын
Thanks Manzar 😀
Your teaching style it's very unique sir.... 🤩. Helpful tips
Amazing Explanation ... thanks Chandoo
@chandoo_
2 жыл бұрын
You're most welcome
very interesting, thanks Chandoo ... all ready to put into Access ;)
@chandoo_
2 жыл бұрын
Enjoy!
This is the best vid on the power query topic
@chandoo_
2 жыл бұрын
Thanks Tina... 😀
You Rock! Thanks
Excellent video sir! Chala bagaundhi.
@chandoo_
2 жыл бұрын
Thanks Mira garu...
Superb Chandoo.Very Useful
@chandoo_
2 жыл бұрын
Thank you so much 🙂
How did You learn so much like this brother. It's really interesting to learn Excel from your videos.❤️❤️❤️❤️🥳🥳
Thanks 🙏
You're a god
Wow, you are a magician 🙏
@chandoo_
2 жыл бұрын
🎩🐇💥
thanks a million
Nice explanation using the futures of powery query in excel.Thank you.😉💝💝💝
@chandoo_
2 жыл бұрын
You are welcome
Just like that 15 files into one. Thanks, great explanation.
@chandoo_
Жыл бұрын
You're welcome!
Best!
Great Video. I tried with my own data and it worked after my 2nd try. And I know I will be able to save 40 minutes at the financial month end
@chandoo_
2 жыл бұрын
That is AWESOME to hear Peter...
Thank you Chandoo Lots of love from seawoods darave which is located in Navi Mumbai ❤❤
Wow well explained sir thnk u
@chandoo_
2 жыл бұрын
You are welcome Reshma.
Thanks boss!
@chandoo_
2 жыл бұрын
Any time
This is awesome 👍. I wish you have published this video few weeks ago. I was modifying files one by one and then append queries 😅
@chandoo_
2 жыл бұрын
Better late than never. 😀
No doubt Power Query Amazing powerful Tool 🤩
Thank you for providing the zip file to practice .
@chandoo_
2 жыл бұрын
My pleasure.
thanks for sharing
@chandoo_
2 жыл бұрын
My pleasure
Excellent
@chandoo_
2 жыл бұрын
Thank you! Cheers!
wonderful boss
@chandoo_
2 жыл бұрын
Thanks for watching
Thanks
Great video. thanks for sharing sir.
@chandoo_
2 жыл бұрын
Thanks for watching!
Great intro! 😇👍
@chandoo_
2 жыл бұрын
Thanks Rishi. I love LEGO and couldn't resist adding them to my videos every now and then.
Thank you for the wonderful tutorial. Please let me know , how to display date of visit from each file, in the final output sheet!
Yours is the forth video I've tried. Three failed. You, however, said (approximately) "select only, do not open." And that made all the difference. (I was double clicking.) THANK YOU for paying attention to the little things... things that can totally frustrate.
Hi, Chandoo. Your video is very beautiful and helpful for me. If you make a video for remove duplicate dynamically with power quary it will help people like me. Your help is highly appreciated.
Hi chandoo sir,... Thank you so much 💗
@chandoo_
2 жыл бұрын
Most welcome
thanks sir ........ very useful tick ......... wow....😲😲😲
@chandoo_
2 жыл бұрын
Most welcome
thanks for you knowledge file sample is very very good for practice to learn thanks
@chandoo_
2 жыл бұрын
It's my pleasure
Great video. Only challenge I'm facing here is to keep the same format and some null data in between. Power query throwing 4000 errors when I'm loading data 😢 Need to recheck the data for formatting issues or null values.
Hi Chandoo and comment readers I'd like to suggest a a minor change that yields a neater approach. PowerQuery EXPECTS you may want to transform each file So, start by working on the Transform Sample File, use the built-in feature to remove top rows, promote headers etc PowerQuery will then automatically update the Transform File function Now you you don't have to faff around filtering out the blank rows and extra heading rows in your main query (Also, if the specification changes, you only need to review and update the Transform Sample File) And, thank you for taking the time to keep posting great Excel videos.
@mangeshbarve1751
2 жыл бұрын
This is the best way...
@chandoo_
2 жыл бұрын
Great tip on Transform Sample Alan. Donut for you 🍩 While I am aware of this, I didn't show it for a reason. > Transform sample & transform functions are somewhat tricky to explain. So I wanted to cover them in an advanced future video Here is one example of the AWESOME power of Transform functions - chandoo.org/wp/combine-excel-files-using-power-query/