Abstract
Transcript
In this video, we'll quickly review new Dynamic Array functions in Excel.
With the introduction of dynamic array formulas, Excel includes 6 brand new functions that directly leverage dynamic array behavior.
Each of these functions is covered in more detail later in the course, so this is just a brief demonstration.
UNIQUE
The UNIQUE function is designed to extract unique values from a set of data.
This range contains a list of colors, some of which appear more than once.
With UNIQUE, I can quickly build a list unique values.
If the source data changes, the list automatically updates.
SORT
As the name implies, the SORT function sorts data. For example, I can use SORT to sort this data by score in descending order.
If I change a score, the sorted list automatically updates.
SORTBY
The SORTBY function makes it easy to sort by more than one column.
In this case, the goal is to sort the data by group, then by score in descending order.
With SORTBY, I supply a range for each column along with a sort direction.
SEQUENCE
The SEQUENCE function is designed to generate numeric sequences.
For example, I can use SEQUENCE to generate 15 numbers incremented by 1, or incremented by 7.
You can also use SEQUENCE with dates and times. For example, here I can create a list of sequential times between 8:00 AM and 6:00 PM, each 1 hour apart.
RANDARRAY
The RANDARRAY function can be used to generate an array of random numbers. For example, I can generate 10 random decimal values.
Or a grid with 100 random integers between 1 and 100.
The RANDARRAY function can be used inside other formulas to perform random sorts, and random item selection.
FILTER
The FILTER function lets you extract data based on matching criteria. For example, I can easily extract just the members of the red group.
If the source data changes, the result automatically updates.
Filter can be used with more complex criteria, and we’ll look at several examples later on in the course.
XLOOKUP and XMATCH
Finally, I want to mention two other new functions: XLOOKUP and XMATCH. Technically, these aren’t dynamic array functions per se, but they do leverage the dynamic array formula engine, and we’ll look at these as well.
