Dynamic Array

The definition of a "dynamic array function" is a function that can populate more than one cell.
Exactly how many cells the function populates, can vary, depending on the data passed in. Hence the word "dynamic".
Before Excel 2021, populating more than one cell using one function, was only possible with Ctrl + Shift + Enter.
In Excel 2021 you can populate more than one cell using Enter (you don't have to use Ctrl + Shift + Enter) any more.


New Dynamic Array Functions

In Office 365 these new functions can populate more than one cell, by just using Enter.

FILTER(2021) Returns an array of data in a range that satisfies multiple conditions.
RANDARRAY(2021) Returns an array of random numbers between 0 and 1.
SEQUENCE(2021) Returns an array of sequential numbers.
SORT(2021) Returns an array of data in a range that has been sorted.
SORTBY(2021) Returns an array of data in a range that has been sorted based on the values in a corresponding range.
UNIQUE(2021) Returns an array of unique values in a list, table or cell range.
FILTER
(2021) Returns an array of data in a range that satisfies multiple conditions.
RANDARRAY
(2021) Returns an array of random numbers between 0 and 1.
SEQUENCE
(2021) Returns an array of sequential numbers.
SORT
(2021) Returns an array of data in a range that has been sorted.
SORTBY
(2021) Returns an array of data in a range that has been sorted based on the values in a corresponding range.
UNIQUE
(2021) Returns an array of unique values in a list, table or cell range.

*The SINGLE function was deprecated and should not be used.


Existing Functions Have Also Been Upgraded

It has always been possible to use Ctrl + Shift + Enter with certain functions to populate more than one cell.
In Excel 2021 a lot of the existing functions have also been upgraded so they can ALSO populate more than one cell, by just using Enter.
Before Excel 2021, the curly brackets were necessary.
In Excel 2021, the curly brackets have become optional.

SQRTReturns the positive square root of a number.
SQRT
Returns the positive square root of a number.

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext