Powerful trick to combine all Excel files in a folder AUTOMATICALLY

[@briandelaney6354]

Wow that's synchronistic. I was looking for exactly this yesterday evening for a project. Thank you Chandoo 😁😁

[@chandoo_]

What can I say.. I read your brain Brian 🤣

[@briandelaney6354]

@@chandoo_ 😄😄

[@sounduniverse9060]

You people are real heroes. Many institutions are charges thousands of rupees for this. And i don't think KZbin pays similarly, but you choose this option. you literally own me

[@livefognik]

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!

[@jamiereife5581]

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.

[@shristeekashyap1779]

You have made it so easy to understand. Thanks a lot😊

[@TP014563]

Straight to point, and straight to point, then some more straight to point. This is what I always look for. Thanks.

[@chandoo_]

Thank you TP.

[@shriidharkulkarni6648]

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.

[@saipranityellajosyula8008]

This video just saved my day from combining data from 53 excel folders data into 1 file. Thanks a lot _/\__/\_

[@RaviKumar-gz9my]

The way you explain is excellent. You make tougher Task to Simple. Great. Appreciate.

[@MuhammadAli-vx6qd]

Thank u it was gr8 to see u on KZbin I was one of the oldest lover of your excel tricks specially graph presentation

[@pulkithappy1897]

@chandoo, your tutorial was great. Especially, the sound effect at 3:55, it brought my attention back 😅

[@asthajain7093]

you are awesome chandoo, i have find every thing for my project on your videos of power querry

[@a.j.wilkes6352]

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_]

Wow.. 800k lines per tab... 😮 You are going to LOVE 💚 Power Query.

[@alanlamb6047]

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]

This is the best way...

[@chandoo_]

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/

[@RaviKumar-gz9my]

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.

[@raj2016]

I'm following your daily dose from 2013 frequently happy to see u finally on you tube. 😀

[@stevenperry7041]

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.

[@viswanathan19]

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_]

Thanks Viswa. Please refer to this advanced example for specific cell situation - chandoo.org/wp/combine-excel-files-using-power-query/

[@89minimalist]

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!

[@miguelgaitan1630]

Extremely helpful and informative video. Thank you Chandoo. Well Done!

[@abdulazizahmed7035]

WOW i was searching every where to have this, you just make easy, Thanks Chandoo

[@michaelhogan1887]

Thank you for the video, do you know how to this on a Mac? I only have the load option.

[@DarrenStarr]

Thank you Chandoo, this video was very useful and saved me a ton of time. You are brilliant my friend!

[@sarasuselvaraj8643]

Hi Chandoo. You made consolidation very easy and Awesome. I have 1500000+ rows of data in three excel with same format. Could you please help me out with getting all these data in single sheet for analysis & comparison study.

[@vijethamuppala5618]

Thanks a lot It helped me a lot today for consolidating the data .

[@PeterLawrence193]

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_]

That is AWESOME to hear Peter...

[@jamanemon2110]

It was really helpful. Thank you Chandoo👏

[@bertholletlubanda422]

Good day , i have really appreciated the tutorial can i ask the question , which version of excel is that ?

[@chandoo_]

I am using Excel 365. But you can apply this in some of the older versions too.

[@robinluvsrap]

Thanks Chandoo! this is really really helpful!!!

[@ibrahimmohammedanyarsyorda7447]

Which Excel version are you using please? Mine is 2013 and I don't see folder as part of the Get external data options. Please help.

[@andreas_Salve]

Wow That's a nice knowledge. Thank you Chandoo sir 👍👍👍

[@rautoramarautorama9324]

Gr8 video. What exactly do u mean when u say all files should be of same format. Is this file format or the format of the data inside the file.? Thanks

[@ricklinty]

Both

[@omkarshedge6065]

Thanku it helps a lot!. While others was using VBA Code for same process, but you taught us simple way to do this.

[@kritikagupta965]

Hi, thank you for the great video. Question - I am saving the power BI excel in the same folder as all the files. After that when I add a new file and try to refresh it is throwing an expression error. Can you please let me know how to fix it ?

[@SoNoFTheMoSt]

really well explained, thanks so much and its so amazing to just put a file in and it updates!

[@yogalekshumimarimuthu2173]

Thank you great video. I have one question, if we want combine files from different folder, can we do that? Say these file has same formats..

[@chandoo_]

You are welcome Yogalekshumi. The process is similar for files in different folders but you need to add some extra steps. It is tricky to explain in a comment, so I will add a video on this during upcoming weeks.

[@fg4513]

Not working fr me

[@baimeiguo6960]

Very nice, thanks! If you have more than one tab in one excel workbook, example: 4 file, each file has 3 tabs, how do you handle those case?

[@chandoo_]

You can combine the idea in this video with the one here - kzbin.info/www/bejne/oZDYmKaef5mqf9k All the best.

[@wayneedmondson1065]

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_]

My pleasure Wayne.

[@kartheekdk2400]

Chandoo I have one doubt If I save n close this consolidated sheet after loading the data After 1 month again if I want to add November data if I place November data in the same folder will it get updated?

[@honnurswamy6292]

Your teaching style it's very unique sir.... 🤩. Helpful tips

[@RaviKumar-gr5lo]

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.

[@satishojhaMrblank]

Thank you very much Chandoo.

[@dynamictechnocrat]

This guy is really good it's a pity I don't really use excel would have bought his course .

[@jibinreny6030]

Thankyou so much, this would save me 1 hour

[@chandoo_]

AWESOME 😎

[@GravelCycling2023]

Hi chandoo, can be schedule automatic refresh on some date & time in Power query, so that whenever a new file is added, our file gets updated

[@maziz6237]

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_]

Excel is AWESOME 😎

[@minhajshareef1180]

Sir, You are amazing and well explained information. Thank you

[@hirengson]

I was doing word column combinations in my excel. And rows are continuing loding till now. So can I save it.. for stop loading rows and catch up already loaded rows. ?????

[@8uhr744]

Hey there, nice Tutorial on how to use PowerQuery to combine and tranform multiple excl files. Im working on Automating this procedure with vba, and i've got a problem there. I want the end user to be able to select a folder with a folderpicker, in wich all the files are, he wants to get combined and transformed. Im using a variable "folderPath" therefore but when I place my variable instead of an absolute path I get error messages. i would be very grateful, if you could help me with that, and I also think it would be very powerful for many other situations. Thank you for your tutorials!

[@Falkland82]

If want to combine data from daily excel spreadsheet is this process valid? I have file with 245 spreadsheet all the same. Can i combine them for analisys?

[@premmish]

Thanks for the video Chandoo. I have been trying to do the same through Excel Power Query, I am getting strange and data is not processed. However, the same data is easily processes through Power BI. Please help me why I am getting such error in Excel 2016. Thanks

[@shakeel778]

Thank you so much its very helpful for it was saved my time during my heavy load work 👍👍👍

[@Soulenergy31]

Great tip Sen Sei!

[@Kiara0101]

I am in love with this tutorial¡

[@ruleem2785]

This was amazing Sir, but i have excel sheet in different folders and i need to extract those excel data into one excel so how to go with that

[@CaribbeanQueen07]

Excellent teacher. Thank you !

[@NoelFerguson]

Thanks for this. Well done.

[@chetrana8314]

Excellent Chandoo.

[@chandoo_]

Thank you Rana.

[@awesome_uk]

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.

[@bittujha2890]

Hi Chandoo - Many thanks for posting this video. Many I request you to pos something to how to get rid of helper queries while combining multiple xlsx file? As there are several video that talks about combining csv file and show the steps get rid of helper queries but not with xlsx file

[@vishwambharsholapur6109]

Hi Chandoo, Thanks for the video and help. I just have a problem. All my excel files are not cleaned (# of columns are different) but reading in the comments I guess it doesn't matter. When I hit 'Combine & Transform', only a single workbook data is loaded and all the other excel workbook data is shown as error. Can you resolve this?

[@RenitaDsilva-b9e]

What if the content within the existing file is updated with new data. Does the combined excel sheet update accordingly as well? Also can you make a video using power automate to create a scheduled flow to merge the excel files within one drive and save into a combined excel sheet

[@chandoo_]

Yes, the combined Excel will update (you just have to refresh). I will make a video on Power automate approach later.

[@sujitdhas174]

Thank you Chandoo Lots of love from seawoods darave which is located in Navi Mumbai ❤❤

[@baimeiguo6960]

Thanks a lot, you are wonderful!!! Thank you Chandoo 😁😁

[@chandoo_]

So are you...

[@LearnAccessByCrystal]

very interesting, thanks Chandoo ... all ready to put into Access ;)

[@chandoo_]

Enjoy!

[@Seftehandle]

This is the best vid on the power query topic

[@chandoo_]

Thanks Tina... 😀

[@praveenr4110]

This is great, thanks for explaining! Is there a solution where number of rows exceeds 1M (which is max excel can support). Pls suggest

[@ahmedlotfy8347]

Can we able to combines different workbooks with different sheet names I mean first sheet in the workbook with second sheet in another workbook with not the same tab or sheet name is it possible And thanks in advance

[@chandoo_]

You can. I cover some of the advanced concepts like this in my Power Query mini-course. Feel free to get it from here - chandoo.org/wp/power-query-power-course/

[@_Manimal_]

Is this way better to save memory in excel ???

[@roes5654]

Commeting before watching ☆ Looks promising

[@sujaynazare489]

This was amazing! Thank you!

[@chandoo_]

You're so welcome!

[@alwulhan]

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_]

Better late than never. 😀

[@ultra332211]

Thanks, Chandoo - I want to give 100 marks to you for this fantastic data analysis video 📹

[@chandoo_]

Thanks Manzar 😀

[@TimEllisWW]

Just like that 15 files into one. Thanks, great explanation.

[@chandoo_]

You're welcome!

[@sekharispassion]

Sir, I have combined 11 xl files following your video. (combine and load option) The queries window which displays on the right of the file was showing xyz rows loaded. Xyz errors. What does the errors refer to? Found the reason for the errors while combining. Used the first option of combine and transform to clear errors. Thank you for the video.

[@shoaibrehman9988]

Hi Sir, Can we find Max value by using xlookup if multiple values available in range from a particular lookup item.

[@anandtaori6569]

Superb...Saved Time

[@hariniharnath9932]

Superb Chandoo.Very Useful

[@chandoo_]

Thank you so much 🙂

[@atulvishwakarma2986]

I need to combine sheets from files that exist in different folders, all named in same pattern. I hv done this by 1. first combining required sheets into one work book and then 2. combining sheets into one. How do I skip step 1? That is combining sheets from files that exist in different folders? Can I pick sheets in Workbooks from folders, just as you picked workbooks/files from folders?

[@surendrasharma2042]

Hi, Can we extract data from gmail by automation in excel?

[@HusseinKorish]

Amazing Explanation ... thanks Chandoo

[@chandoo_]

You're most welcome

[@poojashah5929]

Hi Can we upload data beyond 10 lakh rows as I have 2019, 2020, 2021 data and all this 3 files individually contains row UpTo 700000 So how do I combine it together. Please can you show us Best Regards Pooja

[@anubtiw]

Did you find out how to do this?

[@venkatyalamati3285]

Thank you very much for the awesome video... This is what I wanted ☺️

[@stuartmccloud307]

Amazing! I didn't know this was possible, thanks Chandoo

[@mjjvmjjv8277]

Hi Chandoo please explain Based on One unique code how to merge different format of excel workbooks to 1 on vertical basis i.e. next to last column not to row

[@sivannatarajan7]

Wow, you are a magician 🙏

[@chandoo_]

🎩🐇💥

[@GoldenSlumber474]

Another useful & awesome tip! I have a huge file with multiple data sheets I plan to consolidate with this method. Thanks Chandoo!

[@chandoo_]

Glad it was helpful!

[@rosywild4774]

You are just amazing man

[@saloriasaxon7323]

APPLAUSE APPLAUSE SIR! Yet another great video using combine and queries!!👏👏🎉🎆🎇

[@chandoo_]

🙏 Thank you Saloria.

[@balamira297]

Excellent video sir! Chala bagaundhi.

[@chandoo_]

Thanks Mira garu...

[@rameshharidhasa7107]

Nice explanation using the futures of powery query in excel.Thank you.😉💝💝💝

[@chandoo_]

You are welcome

[@fashihahahmad2346]

What happen if we combine live data? Example, we want to capture the student's height in 4 clases. Notice that there's new student register during the day which in class 1. Can the teacher update/add new line for the new student? And can the master data capture the new line item?

[@ramses2]

Thank you Chandoo,

[@mandara8315]

Super...thanks a lot chandoo!!

[@chandoo_]

You are welcome Mandar. Don't forget to share these amazing tricks with a colleague or friend. Light up their day.

[@sushant19901]

Sir very nice But what if there is data inside the excel work book saved in the folder how to get those details gere

[@sunnypandit8033]

Sir, You are Amazing 😍

[@rdnyman]

Great video! Thanks for posting!

[@hazemali382]

No doubt Power Query Amazing powerful Tool 🤩

[@christoslefkimiotis9889]

when you selected to delete the null, did it delete the null rows (the whole row) or the null cells? (and maybe destroy your data?). Nice video

[@chandoo_]

The entire row is excluded from the final data.

[@christoslefkimiotis9889]

@@chandoo_ the whole row that is null, not the line that has one null cell, correct?

[@Gauravjin]

Xlookup can return one value or one row but how can you return all the unique rows.