Microsoft added six new dynamic array formulas to the latest
version of Excel. Let's take a look and see how they work.
Starting in January 2020 Excel for Office 365 includes Dynamic
Array Formulas. Six new functions are available - UNIQUE, FILTER,
SORT, SORTBY, SEQUENCE, and RANDARRAY. What makes these formulas special is
that they spill multiple results into neighboring cells. This is a radical
change for spreadsheets and why it's taken over two years for Microsoft to
release them in public versions of Excel. Let me show you each of the
six new functions. #1 UNIQUE. The UNIQUE function returns a list of
unique values from a list or a range. So in this example let's do the UNIQUE for
this column of names. You'll see that it returns seven names that are unique to
this list and it spilled the data over into multiple cells. You'll notice that
the formula is in this first cell and it's grayed out in the remaining cells.
That's how a spill function works. UNIQUE also allows you to pick more
than one column so let's say we want the unique values from this list of
two columns of data and it returned the unique values for both the name
and the region. The UNIQUE function has multiple parameters. First you establish
the array that you want to do unique values from. The second parameter is
a true/false whether you want rows or columns so if you do a horizontal
list you can use columns. It defaults to false for rows and the final parameter
is exactly once. If you want just the unique values that occur only one time
in the list you can set that to true and you'll see that these three names occur
only one time in this list. It ignores the ones that are multiple. And that's
the UNIQUE function. #2 FILTER. The FILTER function allows you to filter out
data in a table based on criteria that you define. For example, let's say we
want to filter this entire table where the region equals East and it spills the
data into multiple neighboring cells. We could choose other parameters like
where the units is greater than 15,000 and it filters out just that data. The
FILTER function includes one additional parameter called if_empty. If that value
is actually empty we can say something else like zero. So if we were to empty
one of these values it leaves that out of the filter. For a more detailed look
at the FILTER function take a look at my video called "Return Multiple
Results from an Excel Table With One Function". It covers the FILTER function
in detail. #3 SORT. The SORT function sorts contents of a list or an array.
You can do a simple sort by selecting a column and it puts them in order. You
can do a more complex sort by picking an entire array and it defaults to sorting
them in the same order by column 1 but it includes the other columns in
the data as well. There are additional parameters like the sort_index which
just represents which column you want to sort by. So if we wanted to sort this
list by the Region we can pick column 2 and now it's sorted on the Region.
The next parameter allows you to choose between ascending or descending order
and the last parameter is the column or row sort. If you have a horizontal list
that's when you want to use the column but it defaults to false for the row. #4
SORTBY. The SORTBY function works very similar to SORT but instead of sorting
it by a column number you specify a range to sort. So for example, let's do
a SORTBY of this entire range but we'll select the Region as the sort range and
it sorted that data by Region. There's additional parameters. We can pick the
sort order and we can specify a second array for sort order. So let's do it by
Region and then within that by Units, in ascending order and now it's sorted
by Region and by Units within that. One of the benefits of the SORTBY function
is the array range that you're selecting for sorting does not have to be inside
of the range you've selected. It could be a separate column somewhere else in
your spreadsheet. Other than that it is very similar to the SORT function. #5
SEQUENCE. The SEQUENCE function allows you to generate a list of sequential
numbers in an array. For example, let's do the sequence for 10 rows, 2 columns
wide. We'll start with 10 and we'll do it by 5's. It generated a sequential
list and as you can see it skips from one column to the next and then down the
rows. It's a simple function to allow you to create a sequential list and
like the other dynamic array formulas it spills across multiple columns and rows.
#6 RANDARRAY. The RANDARRAY function returns an array of random numbers. So
if we wanted to do a RANDARRAY 10 rows, 2 columns wide, between 10 and 100,
and let's pick Integer to start with. It gives us a series of numbers. One
thing to note about RANDARRAY - if you re-enter it, it randomizes those
numbers again so they're not fixed. You can also choose Decimal instead
of Integer and it returns a random array of decimal numbers. You can also combine
dynamic array formulas. For example, let's say we pick the UNIQUE names from
this list and we want to turn around and SORT that list alphabetically, or
how about we wanted to FILTER this list of names where the Units are greater
than 15,000 and then turn around and get the UNIQUE values from that list,
or how about we do a RANDARRAY 10 rows by 1 column, starting at 100, going to
500, and picking Integers. But then we want to take that list and SORT it. You
can get creative and combine these as long as you stay within the parameters
of each individual function. That's a look at the six functions available
for Dynamic Array Formulas. Remember these are available in the January 2020
release of Office 365 Excel. If you're on a semi-annual channel update schedule
they are available starting July 2020. Hey, if you want to see more videos like
this one please subscribe and if you've enjoyed this video be sure to click the
thumbs-up and leave a comment. I really do appreciate your support!