Unpivot Data in Excel 365 Using Dynamic Array Formulas (No Power Query!)

Learn how to unpivot data in Excel 365 using dynamic array formulas — no Power Query required! In this tutorial, we’ll take a football dataset where each month is in a separate column (wide format) and reshape it into a tidy table with Team, Month, and Goals in three columns (long format). This makes analysis, filtering, PivotTables, and charts much easier. Perfect if you’re exploring Excel for data analysis, advanced formulas, or alternatives to Power Query. What you’ll learn: ✔️ How to use the MAKEARRAY function to generate rows and columns dynamically ✔️ How MOD and INT can cycle through teams and months correctly ✔️ How INDEX pulls the right team names, headers, and values ✔️ Why dynamic arrays update instantly when your dataset changes ✔️ How this approach compares to Power Query unpivoting ⏱️ Timestamps 00:00 - Intro 00:25 - The dummy data 00:46 - MAKEARRAY function 02:52 - ROWS function 03:38 - COLUMNS function 05:25 - LAMBDA function in MAKEARRAY 07:57 - Team column unpivot INDEX MOD ROWS 09:27 - Months column unpivot INDEX INT ROWS 10:31 - Values column unpivot INDEX MOD INT ROWS 13:33 - Final thoughts 💬 What’s your favourite unpivot method in Excel? Leave a comment below! 📼 Related videos: Unpivot Python - TBC Unpivot Power Query - TBC Unpivot VBA - TBC Dynamic arrays - https://youtu.be/e4VO9lIxQaE?si=T6QIghbwAqv6w1dl 🙌 Support the Channel: 👍 Like the video 💬 Leave a comment 🔁 Share with a friend 💸 https://buymeacoffee.com/benjones - to support the channel 📁File Coming Soon 🔔 SUBSCRIBE FOR WEEKLY TUTORIALS Subscribe to BenJonesExplains Click here to subscribe and master Excel, one trick at a time! https://www.youtube.com/@BenJonesExplains?sub_confirmation=1 @BenJonesExplains 🔍 Tags / Hashtags: #Excel365 #ExcelTutorial #DynamicArrays #Unpivot #DataAnalysis