Dates and location
Pricing
Hours
Dates and location
Pricing
Hours
Description
This advanced course explores the Dynamic Array functionality introduced in Microsoft 365, including the new formula behavior known as spilling and related concepts such as the Spill Range Operator. These enhancements make formulas easier to create, maintain, and apply, while reducing the complexity often associated with traditional spreadsheet solutions. Participants will also learn how to identify and troubleshoot common issues, including the SPILL error that may arise when working with dynamic arrays.
Participants may follow along with the course using the supplied practice data and will learn a wide range of Dynamic Array functions that automatically adjust their output as underlying data changes. Beginning with foundational functions such as SEQUENCE and UNIQUE, the course progresses to more advanced capabilities designed for proficient Excel users. Topics include dynamic filtering using the FILTER function, data extraction and manipulation techniques, and the creation of analytical reports using functions such as GROUPBY and PIVOTBY, which can often provide an alternative to traditional PivotTables.
The course also covers advanced functions such as LET and LAMBDA, enabling users to build powerful, reusable, and highly customized spreadsheet solutions. Through practical demonstrations and hands-on exercises, participants have an opportunity to develop skills needed to take advantage of modern Excel's dynamic formula capabilities. A practice data file containing the same datasets used throughout the demonstrations is included to support learning and experimentation.
Unlock learning anytime, anywhere with the Blackboard Learn app
Seamlessly access your on demand courses, assignments, and get PD hours on the go. Learn how to download now and elevate your education experience with the convenience of mobile learning.
Key Takeaways
Upon completion of this course, you will be able to:
- Explain and apply modern Excel formula concepts, including dynamic array spilling, the Spill Range Operator, and techniques for identifying and resolving spill errors.
- Use advanced formula-building techniques, including Named Ranges, array constants, and formulas that accept and process multiple inputs.
- Leverage Dynamic Array functions such as SEQUENCE, UNIQUE, SORT, GROUPBY, PIVOTBY, FILTER, LAMBDA, and LET to create more efficient, flexible, and scalable spreadsheet solutions.
- Combine and nest functions effectively by integrating Dynamic Array functions with legacy Excel functions and using multiple Dynamic Arrays within a single solution.
- Apply formula best practices, tips, and productivity techniques to simplify complex formulas, improve workbook maintainability, and enhance analytical workflows.
Who Will Benefit
This course is designed for anyone who regularly works with spreadsheets that involve complex formula creation. It is particularly beneficial for users with existing formula experience who perform analytical tasks or develop templates for others. Participants who manage large datasets, filter and analyze data, generate reports, and frequently build lengthy or complicated formulas will find this course especially valuable.
How to Access the Course
To access your course, visit the CPA Ontario Blackboard site and sign in using the same username and password used for the Registration Portal. You can also access your course through the Blackboard Learn app (iOS or Android).
Important: Course access begins on the date of purchase and remains available for the Access Time specified for the course under Dates and Location. Please review the access time before purchasing. Note that it may take up to 15 minutes after registration for the course to appear in Blackboard.
Registration, cancellation, withdrawal, and other CPA Ontario PD policies can be found here.
Speaker(s)
Paul Mascarenhas is a specialist in Strategic Planning, Business Analysis, and Information Technology Skills training and facilitation. He holds a Bachelor of Science degree and a Master's Diploma in Business Administration. Through his company, Avancer Learning Inc., Paul has delivered hands-on technology and business skills workshops to leading Canadian corporations, professional associations, and departments within both the Federal and Provincial Governments.