Dynamic Arrays: FILTER, SORT and UNIQUE

In Microsoft 365 and Excel 2021 or later, a formula can return many values. The results spill into the neighbouring cells, and the whole block updates when the source data changes.

UNIQUE

=UNIQUE(Sales[Region])

This returns each region once. Type it in one cell and the list fills the cells below.

SORT

=SORT(UNIQUE(Sales[Region]))

SORT takes the array, and optionally the column to sort by, the order (1 ascending, -1 descending) and whether to sort by column.

To sort a whole table by Revenue, largest first, where Revenue is the ninth column:

=SORT(Sales,9,-1)

SORTBY sorts one array by the values of another:

=SORTBY(Sales[Product],Sales[Revenue],-1)

FILTER

=FILTER(array, include, [if_empty])

All rows for the North region:

=FILTER(Sales,Sales[Region]="North","No rows")

Multiply conditions to require all of them, and add them to require any of them:

=FILTER(Sales,(Sales[Region]="North")*(Sales[Revenue]>=700))
=FILTER(Sales,(Sales[Region]="North")+(Sales[Region]="East"))

A report that builds itself

In N2, list the regions:

=SORT(UNIQUE(Sales[Region]))

In O2, total each one by referring to the whole spilled list with the # operator:

=SUMIFS(Sales[Revenue],Sales[Region],N2#)

N2# means “the entire range that spilled from N2”. When a new region appears in the data, both columns grow by a row with no editing.

SEQUENCE

=SEQUENCE(12)
=EDATE(DATE(2025,1,1),SEQUENCE(12,1,0))

The first returns the numbers 1 to 12. The second returns the first day of each month of 2025, which is a quick way to build a calendar column.

The #SPILL! error

#SPILL! means something is in the way of the results. Clear the cells below and to the right of the formula.

Dynamic array formulas cannot be placed inside an Excel Table. Put them in ordinary cells beside it.

Practice

Produce a list of the products sold in the South region, each shown once, in alphabetical order.