Here, we will learn how to use the Excel dynamic array functions: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE.
FILTER function
The Excel FILTER function extracts matching records from the given array or range based on one or more conditions. For example, to extract values in the horizontal array {"a","b","c","d","a"} that contain "a", use FILTER like this: =FILTER({"a","b","c","d","a"},{"a","b","c","d","a"}="a"), that returns "a" across two columns. The function can be found in Excel 365 - Excel 2021. FILTER takes the arguments like this: (array, include, [if_empty]), of which array and include are required. Array accepts the array or range from which we want to extract matching records based on one or more conditions. Include accepts the condition that gets converted to a Boolean array (TRUE and FALSE values) while testing. If_empty accepts a custom value to use when no record meet the condition. Omitting the argument will force the function to return the CALC! error. In the example shown, the FILTER formula in E4 extracts all the matching records from the given range A4:C11 that contain the group "A" in the range C4:C11.
RANDARRAY function
The Excel RANDARRAY function generates an array of random numbers between the given two values. For example, to generate an array of random integers, 8 rows by 3 columns, between 1 and 9, use RANDARRAY like this: =RANDARRAY(8,3,1,9,TRUE). To generate random decimals, replace TRUE with FALSE. The function can be found in Excel 365 - Excel 2021. RANDARRAY takes the arguments like this: ([rows],[columns],[min],[max],[integer]), of which none is required. Therefore, you can use the function simply as: =RANDARRAY(), that returns a decimal value between 0 and 1. Rows accepts the number of rows that need to be filled. Columns accepts the number of columns that need to be filled. Min accepts the smallest random number. Max accepts the largest random number. Integer controls the kind of values to return. It is a Boolean argument that accepts either TRUE to return integer numbers or FALSE to return decimal values. Since all the arguments are optional, rows, columns and max default to 1, min defaults to zero and integer defaults to FALSE.
SEQUENCE function
The Excel SEQUENCE function generates an array of sequential numbers. For example, to generate an array of sequential numbers, 8 rows by 3 columns, starting at zero and incremented by 2, use SEQUENCE like this: =SEQUENCE(8,3,0,2). To generate a descending sequence, supply a negative value in the fourth argument, like to generate descending numbers between 10 and zero in 1 column, use SEQUENCE like this: =SEQUENCE(11,1,10,-1). The function can be found in Excel 365 - Excel 2021. SEQUENCE takes the arguments like this: (rows, [columns], [start], [step]), of which rows is required. However, at least one numeric value for rows or columns is required. Therefore, you can use the function simply as: =SEQUENCE(1) or =SEQUENCE(,1), that return the value 1. Rows accepts the number of rows that need to be filled. Columns accepts the number of columns that need to be filled. Start accepts the starting number in the sequence. Step controls the increment between each value. All the arguments default to 1.
SORT function
The Excel SORT function sorts the given array or range by the given column number in ascending or descending order. For example, to sort the values in the vertical array {"b";"d";"c";"a"} in ascending order, use SORT like this: =SORT({"b";"d";"c";"a"},1,1), that returns {"a";"b";"c";"d"}. The function can be found in Excel 365 - Excel 2021. SORT takes the arguments like this: (array, [sort_index], [sort_order], [by_col]), of which array is required. Array accepts the array or range to be sorted. Sort_index accepts an integer indicating the column or row number to sort by. Sort_order either accepts 1 that sorts in the ascending order or -1 that sorts in descending order. By_col accepts a Boolean value (TRUE/FALSE) that controls the direction of sorting. FALSE will sort by row and TRUE will sort by column. You need to set this argument to TRUE when your data is organized horizontally. By default, SORT will sort the vertical data in ascending order by the first column.
SORTBY function
The Excel SORTBY function sorts the given array or range by the another given array or range in ascending or descending order. For example, to sort the vertical array {"b";"d";"c";"a"} using values in the vertical array {4;3;1;2} in ascending order, use SORTBY like this: =SORTBY({"b";"d";"c";"a"},{4;3;1;2},1), that returns {"c";"a";"d";"b"}. The function can be found in Excel 365 - Excel 2021. SORTBY takes the arguments like this: (array, by_array1, [sort_order1], [by_array2, sort_order2],…), of which array and by_array1 are required. Array accepts the array or range to be sorted. By_array1 accepts the array or range to be sorted by. Sort_order1 either accepts 1 that sorts in the ascending order or -1 that sorts in descending order. By default, SORTBY will sort in the ascending order. Thereafter, the function accepts the subsequent optional by_array and sort_order arguments in pairs.
UNIQUE function
The Excel UNIQUE function returns unique values in the given array or range. For example, to extract unique values from the vertical array {"b";"a";"b";"a";"a"}, use UNIQUE like this: =UNIQUE({"b";"a";"b";"a";"a"}), that returns {"b";"a"}. The function can be found in Excel 365 - Excel 2021. UNIQUE takes the arguments like this: (array, [by_col], [exactly_once]), of which array is required. Array accepts the array or range from which to extract unique values. By_col accepts a Boolean value (TRUE/FALSE) that controls the direction of extracting. FALSE will extract by row and TRUE will extract by column. You need to set this argument to TRUE when your data is organized horizontally. Exactly_once also accepts a Boolean value (TRUE/FALSE) that controls whether to extract unique values or those that appear only once. FALSE extracts all unique values and TRUE extracts unique values that appear only once. By default, UNIQUE extracts all unique values from the vertical data.
You have successfully learnt how to use the Excel dynamic array functions: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE.
