In this article, we have explained 6 Dynamic Array functions in Microsoft Excel. Dynamic Array functions are available with Office 365 Subscription in the Current Channel Office Insider Program.
Learn how to use XLOOKUP in Excel
Watch the step by step video tutorial:
Below are the 6 Dynamic Array Functions-
UNIQUE
Unique returns the unique values form given arrays. These values can be text, numbers, dates, times, etc.
Syntax
=UNIQUE (array, [by_col], [exactly_once])
Arguments
- array: Range or array from where to get the unique list.
- [by_col]: How to sort. By row = FALSE (default); by column = TRUE. This is an optional argument.
- [exactly_once]: TRUE = values that occur once, FALSE= all unique values (default). This is an optional argument.
SORT
SORT function is used to sorts the data of a range or array.
Syntax
=SORT (array, [sort_index], [sort_order], [by_col])
Arguments
- array: Range or array which you want to sort.
- [sort_index]: Column index to use for sorting. The default is 1. This is an optional argument.
- [sort_order]: 1 = Ascending, -1 = Descending. The default is ascending order. This is an optional argument.
- [by_col]: TRUE = sort by column. FALSE = sort by row. The default is FALSE. This is an optional argument.
SORTBY
SORTBY function used to sort the data of a range or array based on the single or multiple columns.
Syntax
=SORTBY (array, by_array1, [sort_order1], ...)
Arguments
- array: Range or array which you want to sort.
- by_array1: first range or array to sort by.
- [sort_order1]: First sort order for first range or array. 1 = ascending (default), -1 = descending. This is an optional argument.
- You can add multiple Range/Array and Sort order.
FILTER
FILTER function is used to filter the data on the base of the provided criteria.
Syntax
=FILTER (array, include, [if_empty])
Arguments
- array: Range or array to filter.
- include: Filter criteria as Boolean.
- [if_empty]: Value to return when no results are returned.
RANDARRAY
The RANDARRAY function returns random numbers. You can pass the argument for rows and columns, minimum and maximum values. You can define whether you want to get the whole numbers or decimal values.
Syntax
=RANDARRAY ([rows], [columns], [min], [max], [integer])
Arguments
All arguments are optional.
- [rows]: Number of rows to return. Default = 1.
- [columns]: Number of columns to return. Default = 1.
- [min]: Minimum value to return. Default = 0.
- [max]: Maximum value to return. Default = 1.
- [integer]: Return whole numbers. Boolean, TRUE or FALSE. Default = FALSE.
SEQUENCE
The SEQUENCE function returns the list of sequential numbers in an array, such as 1, 2, 3, 4.
Syntax
=SEQUENCE (rows, [columns], [start], [step])
Arguments
- rows: Number of rows to return.
- [columns]: Number of columns to return. This is an optional argument.
- [start]: Starting value (defaults to 1). This is an optional argument.
- [step]: Increment between each value (defaults to 1). This is an optional argument.
