Excel: Using Dynamic Array Functions

Course details

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.

Concepts

0. Introduction

01 - Using dynamic array functions

1. What to Know Before Using These Functions

02 - Warnings to heed
03 - Dynamic arrays and tables don't always play nice together

2. UNIQUE

04 - Unique vs. distinct
05 - UNIQUE with multiple criteria
06 - UNIQUE made practical

3. SORT and SORTBY

07 - SORT function
08 - SORT horizontal
09 - Horizontal SORTBY
10 - SORTBY cities
11 - PIVOTBY
12 - GROUPBY

4. SEQUENCE

13 - Basic SEQUENCE
14 - Using SEQUENCE to unstack records
15 - SEQUENCE flip a dataset

5. FILTER

16 - Introducing FILTER
17 - FILTER logic - OR and AND criteria
18 - FILTER logic - Equal
19 - FILTER logic - Minus
20 - Extreme FILTER logic - Three or more criteria

6. RANDARRAY

21 - Randomize data
22 - Generate random data
23 - Midterm introduction
24 - Midterm challenge
25 - Midterm solution

7. Manipulating Strings of Text

26 - Part 2 introduction
27 - Split strings of data with TEXTSPLIT
28 - TEXTSPLIT into rows
29 - Isolate data with TEXTBEFORE
30 - Isolate data with TEXTAFTER

8. Dynamics on Rows and Columns

31 - Convert arrays into single rows or columns with TOCOL
32 - Use WRAPROWS and WRAPCOLS to convert data into an array
33 - Return specific rows or columns with CHOOSECOLS
34 - Extract specific rows or columns of data with TAKE
35 - Eliminate certain rows or columns of data with DROP
36 - Combine datasets with HSTACK and VSTACK
37 - WRAP

9. Challenges and Solutions

38 - Introduction to the final challenges
39 - Challenge 1
40 - Solution 1
41 - Final challenge
42 - Final solution

Conclusion

43 - Next steps