Dynamic array functions are only available in the Microsoft 365 version of Excel.  They are very different to many of the other functions as they allow you to work with multiple values at the same time in a formula.  The result is not normally one solitary cell but a range of cells called an array.

There are currently 6 main dynamic array functions which are FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE.  In this article FILTER, SORT and SORTBY will be looked at in detail which provide ways to filter and sort your data.

FILTER function

The FILTER function lets the data be filtered by a certain criteria.  If there is no result specific text can be shown.

Structure

=FILTER(array, include, if empty)

Array is the range or array to filter.  Do not include any headings or they will be filtered too.

Include specifies the data to be extracted

If empty is optional and is the result to be shown if no items are returned

Example

In this example we want to return all the employees in the credit department and put in the word “Empty” if there are no results.

The formula in cell J2 is:

=FILTER(A2:G28,C2:C28=”Credit”,”Empty”)

Image of Filter dynamic array function

SORT Function

The SORT function sorts data into a particular order based on a column and can be either ascending or descending.  The sort can even be by row or column.

Structure

=SORT(array, sort index, sort order, by column)

Array is the range or array to sort.  Do not include any headings or they will be sorted too.

Sort index is optional and is the column number to be sorted in the array.  If left out the first column will be sorted.  This refers to a row number instead if the final part of the function is set to TRUE

Sort order is the order the data will be sorted.  Use 1 for ascending and -1 for descending.  This is also optional and if left blank it will sort ascending

By column specifies whether the data is sorted by column or row.  If left blank or the word FALSE is used it will sort by row.  Inserting TRUE will sort by column

Example

In this example we want to sort the data by the department (column 3) into ascending order

The formula in cell J2 is:

=SORT(A2:G28,3,1)

Image of Sort dynamic array function

SORTBY function

This function sorts a range based on values in a corresponding range.  Where SORT can only sort by 1 column SORTBY can apply a multiple column sort if required.

Structure

=SORTBY(array, by array 1, sort order 1, by array 2, sort order 2………)

Array is the range or array to sort.  Do not include any headings or they will be sorted too.

By array 1 is the first column to be sorted by.  Unlike with the SORT function this is the range of cells to sort by

Sort order 1 is the order the data will be sorted.  Use 1 for ascending and -1 for descending.  This is optional and if left blank it will sort ascending

All other fields are optional and allow for 2nd, 3rd and subsequent level sorting if required

Example

In the following example we want to sort the data into ascending order based on the department and within this into ascending order based on position held.

The formula in cell J2 is:

=SORTBY(A2:G28,C2:C28,1,E2:E28,1)

Image if Sort by dynamic array function

Unlike traditional sorting or filtering using these dynamic array functions ensure the results will update if the original data changes.  Because the calculations are in different cells to the actual data this will remain unchanged.

TIP:  If the table to be used is formatted as a table then the array parts of the function will include any new data which is added at the bottom of the table.  This is really useful if the data is likely to grow or shrink.

 

 

Want to learn more about Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Excel Courses I run.

MEMBERSHIPS

FOLLOW LARA