Automate Excel Workbook Splitting with Power Automate & Office Scripts Tutorial

Stop manually splitting massive Excel workbooks—let Power Automate and Excel Office Scripts do the heavy lifting for you! In this step-by-step tutorial, discover how to instantly split Excel data into multiple worksheets or even separate workbooks, all based on a key column. Perfect for anyone looking to streamline workflow automation, boost productivity, and harness the power of low-code tools in the Power Platform. You'll learn how to use cloud flows and Office Scripts to automate what used to take hours, whether you're wrangling a few hundred rows or over 100,000. We’ll walk through real-world datasets, share practical scripts, and cover common pitfalls (like Excel’s quirky sheet name rules) so you can confidently tackle your own business process automation. Plus, see how to bulk load any data—be it from SharePoint, Dataverse, or APIs—into Excel with a single script. What You’ll Learn: ✔️ Build a Power Automate flow that splits Excel workbooks into multiple worksheets or files by any key column ✔️ Automate repetitive data-splitting tasks for large datasets—no more manual sorting! ✔️ Use Office Scripts to bulk load data from any source (SharePoint, Dataverse, APIs) into Excel ✔️ Avoid common Excel automation pitfalls and maximize your digital transformation with low-code solutions Timestamps: 00:00 – Instantly Split Excel Workbooks with Power Automate & Office Scripts 00:43 – Exploring Sample Excel Workbooks and Datasets 01:07 – Running Office Script to Split Data by Key Column 03:56 – Reviewing Results: Multiple Worksheets Created 07:17 – Building a Cloud Flow for Individual Workbook Creation 14:57 – Validating Output: Unique Excel Workbooks Generated 17:15 – Deep Dive: Understanding the Office Script Code 21:05 – Bulk Loading Data into Excel from Any Source 24:40 – Final Tips, Summary, and Next Steps #PowerAutomate #workflowautomation #LowCode #PowerPlatform #Excel Ready to supercharge your Excel workflows? Try these scripts, check out the blog for downloads, and let me know in the comments if you have questions or want to see more Power Apps and automation tips! Don’t forget to subscribe for the latest in digital transformation and citizen development. Additional Links: 🔗 https://www.youtube.com/playlist?list=PLzq6d1ITy6c2_qM_ocYDtEaENrqi92YmM&list=PLzq6d1ITy6c138K_CM7hs9T1zuvvZufX_ More Office Script demo videos 📝 https://www.damobird365.com/split-a-workbook-into-multiple-worksheets Blog post with sample code to split Excel workbooks 🔗 https://youtu.be/RB_ySjhm9Sg?list=PLzq6d1ITy6c138K_CM7hs9T1zuvvZufX_ Video on creating an Excel file from Compose 🫠 About me: 👋 Hi, I'm Damien Bird — Power Platform Cloud Solution Architect at Microsoft ☁️ 🧠 Former Forum Super User (2021/22) & Business Applications MVP (2022) 🎤 Speaker at conferences across Europe, the US, and local UK user groups 💡 Passionate about sharing knowledge, solving problems, and empowering the community with the Power Platform! 📚 Explore more: 🔗 Blog: https://DamoBird365.com 🎥 Video & Quiz CPD: https://DamoBird365.com/YouTube 💖 Support the Channel: https://youtube.com/@DamoBird365/membership ☕ Buy Me a Coffee: https://BuyMeACoffee.com/DamoBird365 💼 LinkedIn: https://LinkedIn.com/In/DamoBird365 🐦 Twitter: https://x.com/DamoBird365 🌐 Bluesky: https://bsky.app/profile/DamoBird365.bsky.social 📜 Feedback or Request: https://forms.office.com/e/WAV1iF2Wis