Excel: Using Dynamic Array Functions (Microsoft 365)
1h 30mAdvanced2021-05-25
Authors
Oz du Soleil
Excel MVP, Author, and Trainer
Course details
In September 2018, Microsoft released a new family of functions in Excel called dynamic arrays. With dynamic arrays, a user can write one formula and get many different results. 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 the new calculation engine and dynamic array functions. He walks through all seven functions—UNIQUE, SORT, SORTBY, SEQUENCE, FILTER, RANDARRAY, and SINGLE—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
1.Working with spills
2.Using the UNIQUE function
3.Sorting with dynamic arrays
4.Comparing SORTBY and SORT
5.Unstacking records with SEQUENCE
6.Creating intelligent filters
7.Randomizing data with RANDARRAY
8. Combining dynamic arrays in drop lists and more
Learning objectives
1.Working with spills
2.Using the UNIQUE function
3.Sorting with dynamic arrays
4.Comparing SORTBY and SORT
5.Unstacking records with SEQUENCE
6.Creating intelligent filters
7.Randomizing data with RANDARRAY
8. Combining dynamic arrays in drop lists and more
Skills covered
Office 365Microsoft 365SpreadsheetsMicrosoft ExcelBusiness Software and ToolsMicrosoftDeep Dive (X:Y)
Concepts
0. Introduction
- 01 - Using dynamic array functions
1. What to Know Before Using the New Functions
- 02 - Introducing spills, dynamic arrays, and the new calculation engine
- 03 - Warnings to heed
- 04 - Dynamic arrays and tables don't always play nice together
2. UNIQUE
- 05 - Unique vs. distinct
- 06 - Multiple criteria
- 07 - UNIQUE by column
- 08 - UNIQUE with two criteria
3. SORT
- 09 - Basic SORT and two criteria
- 10 - Horizontal SORT
4. SORTBY
- 11 - Basic SORTBY
- 12 - Horizontal SORTBY
- 13 - SORTBY vs. SORT
5. SEQUENCE
- 14 - Basic SEQUENCE
- 15 - Unstack records
6. FILTER
- 16 - FILTER logic - Plus
- 17 - FILTER logic - Asterisk
- 18 - FILTER logic - Equal
- 19 - FILTER logic - Minus
- 20 - FILTER Logic - Greater Than
- 21 - Crazy logic - Three criteria
7. RANDARRAY
- 22 - Randomize data
- 23 - Generate random data
8. SINGLE
- 24 - SINGLE - Implicit intersection
9. Putting It All Together
- 25 - The spilled range operator
- 26 - Dynamic drop-down list
10. Challenges and Solutions
- 27 - Challenge 1
- 28 - Solution 1
- 29 - Challenge 2
- 30 - Solution 2