Excel: Using Dynamic Array Functions [Updated: 7/2/2025]

Dynamic array functions in Excel allow users to perform calculations on multiple cells at once, saving time and increasing efficiency. This allows users to build more robust spreadsheets, in less time, with fewer errors. Follow along with Excel MVP Oz du Soleil as he walks you through Excel's calculation engine and demonstrates how to make the most of dynamic array functions. He walks through all the key functions—including UNIQUE, SORT, SORTBY, SEQUENCE, FILTER, and newer functions like TOCOL and CHOOSECOLS—showing practical, real-world uses for each one. Plus, learn how to use the spilled range operator (#) to reference a dynamic spill range. Oz concludes the course with a series of challenges so viewers can practice and master dynamic arrays.

Learning objectives

• Use dynamic arrays and Excel’s calculation engine to perform complex calculations and analysis.
• Describe the strengths, limitations, and behaviors of dynamic array functions and the new calculation engine.
• Filter and extract Excel data using FILTER, CHOOSEROS, CHOOSECOLS, TAKE, and DROP functions.
• Use Excel’s SORT and SORTBY functions to sort data based on single or multiple criteria.
• Create dynamic dropdown lists in Excel and use TEXTSPLIT, TEXTAFTER, and TEXTBEFORE to split text with precision.