Split Excel Data into Multiple Files by Column Values using Python (fast & easy)

𝗗𝗘𝗦𝗖𝗥𝗜𝗣𝗧𝗜𝗢𝗡
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
This tutorial will show you how to separate Excel Data into Workbooks by Column Values. Instead of VBA, we will be using Python and the Pandas Library. The Python code from this tutorial can be a huge time saver. You can apply the code from this tutorial also any other Excel – you just need to tweak it slightly.

Download all the files & source code from the tutorial:

𝗧𝗢𝗢𝗟𝗦 𝗔𝗡𝗗 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

𝗖𝗢𝗡𝗡𝗘𝗖𝗧 𝗪𝗜𝗧𝗛 𝗠𝗘
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

☕ 𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲?

[CodingIsFun]

*What kind of Excel Automation would you like to see next?* Let me know in the comments 😃

[daveg]

Thank you so much. You helped me automate 3 hours of weekly work for an FTE. Great video

[mayday_excelgroup]

Best thing I’ve ever seen so far about excel+python

[bak]

Hello, coding is fun. Sorry I don't know your good name .

You are welcome and thanks for your comments below. I actually love python coding. I came to learn it as I mentioned before for more than 1 year now . With your videos you are showing how much we can do with python. I prefer using python instead of VB since it is much easier and script oriented and it is easy for me to learn. Thank you again.

[marco_rock_s]

That was perfect, exactly what I was looking for, thanks!! Concise, yet complete. :)

[eryabolonha]

New subscriber
Thank you ✨
This would definitely save me a lot of time
I followed your tutorial which made me super happy 😊 but I need the master file format and data validation sheet 🙃

[marciorodriguesmota]

o video que eu estava procurando h'a muito tempo, thankss

[bak]

Nice coding. I just need to look into it more deeply to make sure I understand it well.
Thanks

[austenlowitz]

Great video! Thanks so much. Do you know how we can split the files while maintaining the excel formulas in the master file?

[veginetikalyanprasadchowda]

I need a solution in one column i have a lot of data and blank cells what requirements was all values from that sheet need to update in one file and blank value records to the 2nd CSV file is that possible

[beboldermedia]

Not sure if you will see this but wondering what to do when I see this error:

InvalidWorksheetName: Invalid Excel character '[]:*?/\'

When looking at my datafile I noticed some Customer_Name's have "/" in them. Any help?

[jhonnidarshan]

How do we get get new excel sheets by Columns that’s mean if it has 15columns and I need only 3 specific columns

[worldstreetfoodexplorer]

I am running into errors with Unique_value where it keeps saying “first arguement must be string or compli pattern.” Also when I do + ‘.xlsx’ it says something like cannot combine float and string. But the code works and I can see the files created… any explanation on that?

[mikebeaton]

Hey - I took your code and just changed the Column Name to work with my use case. After going through a few thousand seperations, I'm getting this error -- TypeError: first argument must be string or compiled pattern -- any thoughts?

[Viralvlogvideos]

Pandas is failing to process 100 million records around 18.5 gb csv file. Any idea how to split into smaller records, I also tried polars it is generated only one file of 100k

[wnibwi]

Hi! Thanks for the video as it help me a lot. Can I know how to save the final multiple file into csv?

[trendingnow]

By using your code, "can only .str accessor with string valuse!" This error will shown?

[varatharajinpayanangal]

How do I format the output excel sheet? Like column width, auto adjust size of column to content

[kevinthomas]

Great videos! Thank you so much! Quick question: What if the column values I am splitting by are numeric instead of strings? I keep getting an error with 'str.contains', but when I use a different column that are string values everything works fine.

[xHeroGirlx]

Nice! Thank you! How can I split one excel file with multiple sheets into multiple files with multiple sheets based on one row/value in each sheets?