Dynamic Array Formulas in Excel

Last Updated : 23 Jul, 2022

Dynamic arrays are resizable arrays that calculate automatically and return value into multiple cells based on a formula entered in a single cell. The new array (multiple cells) that we get is known as spilling and the new array has been placed in neighboring cells. It is not necessary to use Ctrl + Shift + Enter to enter an array formula. Dynamic array formulas are only available in Excel 365 and Excel 2021.

Dynamic Array Formulas

Below mentioned array formulas are the dynamic array formulas, let's learn in detail how these formulas are applied in excel on a certain dataset.

UNIQUE
SORT
SORT BY
SEQUENCE
RANDARRAY
FILTER
LOOKUP

UNIQUE Formula

Unique formulas return a list of unique values in a list. Unique means that element that appears only once in an array. Values can be text, number, etc. A unique function is not case-sensitive means 'Apple' and 'apple' are the same. Syntax,

=UNIQUE(A3:A17)

Applying-UNIQUE-formula — fig 1. Print only Unique Values

SORT Formula

Sort formula returns a list in increasing order or decreasing order. Values can be text, number, etc. Syntax,

=SORT(A4: A10) increasing order.

Sorting-in-increasing-order — fig 2. Sort the values

SORTBY Formula

SORTBY formula sorts the contents of a range based on values from another array. Values can be text, number, etc. Syntax,

=SORTBY(A4:A10,B4:B10,-1)

Here, -1 is used for only decreasing order.

SEQUENCE Formula

The sequence formula allows you to generate a list of sequential numbers in an array. List can be 1D, 2D determined by rows and columns arguments. Syntax,

=SEQUENCE (5,6)

It will generate five rows and 6 columns, so the value will be from1 to 30.

Generating-sequence — fig 4. Arrange sequence in row and column

RANDARRAY Formula

RANDARRAY formula generates an array of random numbers. Generated values can be either decimals or whole numbers. The size of the array is specified by rows and columns arguments. Syntax,

=RANDARRAY(1,2)

Generating-array-of-random-numbers — fig 5. Generate random value

FILTER Formula

The filter formula filters a range of data based on given criteria. It returned an array of filtered values. In this formula we print only those values which are greater than 100. Syntax,

=FILTER(A4:A12,A4:A12>100)

Values-greater-than-100-printed — fig 6. Using filter formula

LOOKUP Formula

The Excel LOOKUP function returns a value from a range (one row or one column) or an array. If you want to search any number from an array then we use this function. Syntax,