Introduction to Dynamic Array Functions in Modern Excel

Excel Dynamic Arrays: The Future of Spreadsheet Intelligence

Excel’s dynamic array functions have revolutionized how professionals interact with data - bringing automation, flexibility, and scalability to everyday spreadsheet tasks. These functions empower users to build responsive models, streamline reporting, and eliminate manual formula maintenance. From finance and operations to HR and analytics, dynamic arrays are transforming workflows across industries. With tools like LAMBDA, FILTER, SORT, XLOOKUP and TEXTSPLIT,

Excel now rivals specialized platforms in speed and adaptability - making it a must-have skill for modern data professionals.

Master the Essentials of Dynamic Array Functions in Excel

This 1-day introductory course provides participants with the foundational skills to confidently use Excel’s dynamic array functions. Guided by an experienced trainer, you’ll learn how to extract, reshape, and analyze data using functions that respond instantly to changes. The course covers key categories including core dynamic arrays, modern lookups, array construction, reshaping, subsetting, and text splitting. By the end of the session, you’ll be equipped to build smarter spreadsheets, automate data flows, and unlock new possibilities in Excel-driven analysis.

Upon completion of the course, learners will be able to:

• Use core dynamic array functions like UNIQUE, SEQUENCE, FILTER, SORT, SORTBY, and RANDARRAY to extract and organize data dynamically.
• Apply modern lookup functions XLOOKUP and XMATCH to replace legacy lookup formulas with more powerful, flexible alternatives.
• Construct and reshape arrays using MAKEARRAY, HSTACK, VSTACK, TOCOL, TOROW, WRAPROWS, and WRAPCOLS for advanced layout control.
• Subset arrays with precision using TAKE, DROP, EXPAND, CHOOSECOLS, and CHOOSEROWS.
• Manipulate and split text data using TEXTSPLIT, TEXTBEFORE, and TEXTAFTER for cleaner, more efficient parsing.
• Create custom reusable functions using LAMBDA, enabling users to simplify complex formulas, encapsulate logic, and improve workbook maintainability without VBA

*Dynamic array functions are available in Microsoft 365 versions of Excel.

This is an intensive introductory course.

• Participants are required to have some basic ideas for using MS Excel.
• Learners are required to bring a Windows based laptop equipped with Microsoft 365 as Dynamic Array Functions are not available in Microsoft Excel 2016/ 2019.

"The trainer is knowledgeable and patient with the respective learners. Provides clear instructions."

"Progressive training that enables students with no prior experience to follow through the lesson seamlessly."

"The trainer has been very patience towards guiding those who have difficulties understanding certain parts of the topic."

"Engaging, always making sure everyone was on pace. He was also very patient in coaching those who were weaker."

Paul is a seasoned trainer with over 15 years of experience in financial analysis, data modeling, and data visualization. His expertise spans across advanced business intelligence platforms such as Power BI, Tableau, Qlik Sense, and Python for business analytics. With an exceptional track record, Paul has trained corporate professionals from diverse industries, helping them develop and implement data-driven strategies.

Paul holds a Bachelor of Business degree with distinction and double Master’s degrees in Applied Finance and Accounting. As a certified trainer and accomplished risk analyst, he designs and delivers impactful training programs that blend theoretical knowledge with practical applications. His course plans are widely adopted by leading learning hubs for both public and corporate training sessions, making him a trusted name in the data analytics domain.