English | MP4 | AVC 1280×720 | AAC 48KHz 2ch | 1h 26m | 192 MB
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.
Topics include:
- Working with spills
- Using the UNIQUE function
- Sorting with dynamic arrays
- Comparing SORTBY and SORT
- Unstacking records with SEQUENCE
- Creating intelligent filters
- Randomizing data with RANDARRAY
- Combining dynamic arrays in drop lists and more
Table of Contents
Introduction
1 Using dynamic array functions
What to Know Before Using the New Functions
2 Introducing spills dynamic arrays and the new calculation engine
3 Warnings to heed
UNIQUE
4 Unique vs. distinct
5 Multiple criteria
6 UNIQUE by column
7 UNIQUE with two criteria
SORT
8 Basic SORT and two criteria
9 Horizontal SORT
SORTBY
10 Basic SORTBY
11 SORTBY multiple criteria
12 SORTBY vs. SORT
SEQUENCE
13 Basic SEQUENCE
14 Unstack records
FILTER
15 FILTER logic – Plus
16 FILTER logic – Asterisk
17 FILTER logic – Equal
18 FILTER logic – Minus
19 FILTER logic. Yes small
20 Crazy logic – Three criteria
RANDARRAY
21 Randomize data
22 Generate random data
SINGLE
23 SINGLE – Implicit intersection
Putting It All Together
24 The spilled range operator
25 Dynamic drop-down list
Challenges and Solutions
26 Challenge 1
27 Solution 1
28 Challenge 2
29 Solution 2
Resolve the captcha to access the links!