How to Sort and Filter using Dynamic Array Functions in Microsoft Excel

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”)

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)

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 2^nd, 3^rd 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)

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.

Sort and Filter Dynamic Arrays FAQs

What are dynamic array functions in Excel?

Dynamic array functions allow a formula to return more than one result. Instead of displaying the answer in a single cell, the results can spill into nearby cells automatically.

Which version of Excel includes dynamic array functions?

Dynamic array functions are available in Excel for Microsoft 365. They may not be available in older standalone versions of Excel.

Why should headings not be included in the array?

If headings are included, Excel treats them as part of the data. This can cause headings to appear in the results or be sorted with the rest of the information.

What does it mean when results spill?

Spilling means Excel places the results of a formula across several cells. If another value blocks the spill range, Excel will show an error until the space is cleared.

What is the difference between SORT and SORTBY?

SORT sorts a range using a column number within the selected data. SORTBY sorts one range based on values in another range, which makes it useful for sorting by more than one column.