Dynamic Array
Excel Tips and Tricks

6 Powerful Dynamic Array Function in Excel

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.
UNIQUE function
UNIQUE function

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.
SORT Function
SORT Function

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.
SORTBY Function
SORTBY Function

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.
FILTER Function
FILTER Function

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.
RANDARRAY function
RANDARRAY function

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.
SEQUENCE function
SEQUENCE function

Click here to download the practice file.

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com