Excel: Using Dynamic Array Functions (Office 365/Microsoft 365)

Introduction

• Using dynamic array functions

1. What to Know Before Using the New Functions

• Introducing spills, dynamic arrays, and the new calculation engine
• Warnings to heed
• Dynamic arrays and tables don't always play nice together

2. UNIQUE

• Unique vs. distinct
• Multiple criteria
• UNIQUE by column
• UNIQUE with two criteria

3. SORT

• Basic SORT and two criteria
• Horizontal SORT

4. SORTBY

• Basic SORTBY
• Horizontal SORTBY
• SORTBY vs. SORT

5. SEQUENCE

• Basic SEQUENCE
• Unstack records

6. FILTER

• FILTER logic: Plus
• FILTER logic: Asterisk
• FILTER logic: Equal
• FILTER logic: Minus
• FILTER Logic: Greater Than
• Crazy logic: Three criteria

7. RANDARRAY

• Randomize data
• Generate random data

8. SINGLE

• SINGLE: Implicit intersection

9. Putting It All Together

• The spilled range operator
• Dynamic drop-down list

10. Challenges and Solutions

• Challenge 1
• Solution 1
• Challenge 2
• Solution 2