Excel Dynamic Arrays: FILTER, SORT, UNIQUE, LET

A dynamic array formula is a formula that returns several values at once and spills them into the cells below and to the right of where you typed it. You enter one formula in one cell, and Excel fills as many cells as the answer needs. FILTER, SORT, UNIQUE and SEQUENCE are the best known; LET lets you name pieces of a formula. Dynamic arrays are available in Microsoft 365 and Excel 2021 or later. In Excel 2019 and earlier these functions do not exist.

They replace many jobs that used to need helper columns, pivot tables or Ctrl + Shift + Enter formulas. This guide shows what spilling means, then each function on one small sales table, how to combine conditions with AND and OR, and the errors you will meet (#SPILL! and #CALC!). Every result was produced by running the formula in Microsoft Excel.

What spilling means

Type =A1:A3*2 in C1. Older Excel would multiply only A1 by 2 and you would have to copy the formula down. Modern Excel multiplies the whole range and shows all three results, each in its own cell. Only C1 holds a formula; the cells below display parts of its result. To refer to the whole result, add # after the anchor cell: C1#.

C1 holds =A1:A3*2, E1 holds =SUM(C1#), E2 holds =ROWS(C1#)

Only the anchor cell C1 contains a formula; C2 and C3 show its spilled results

If a cell in the way of the spill is not empty, Excel refuses to overwrite it and shows #SPILL!. Here the same formula sits above a cell that contains an x:

Clear the blocking cell and the formula works at once. Other causes of #SPILL! are a merged cell in the way, an Excel Table (spills are not allowed inside tables), or a result too big for the sheet.

FILTER: pick the rows you want

FILTER(array, include, [if_empty]) returns the rows of array for which include is TRUE. The include argument is a test that produces one TRUE or FALSE per row, such as B2:B11="North". The sales table is in A1:E11 (the same one as in the SUMIFS guide), and the formulas below are typed into G2 with the column names copied into G1:K1:

=FILTER(A2:E11, B2:B11="North")

The result is five columns wide, because the array we passed was five columns wide, and as tall as the number of matches.

When nothing matches, FILTER returns the error #CALC!. Give it a third argument for that case, or wrap it in IFERROR. The other ways to use it inside a formula, for example to add up the filtered amounts, are shown here:

FILTER with AND and OR

Inside FILTER, each condition gives a column of TRUE and FALSE (which count as 1 and 0). Multiply two conditions and the row must satisfy both: AND. Add two conditions and a row passes when at least one is true: OR. Brackets decide the grouping, so write the rule in words first. “North and (Pen or Bag)” keeps the region fixed and lets the product vary:

=FILTER(A2:E11, (B2:B11="North") * ((C2:C11="Pen") + (C2:C11="Bag")))

Move the brackets and the rule changes. ((B2:B11="North")*(C2:C11="Pen")) + (C2:C11="Bag") means “(North and Pen) or any Bag”, which returns five rows instead of three, because every Bag row now passes whatever its region. Similarly (B2:B11="North")+(C2:C11="Bag"), plain OR, returns six rows. Both counts are in the table above (rows 1 to 3).

SORT and SORTBY

SORT(array, [sort_index], [sort_order], [by_col]) sorts a block of rows by one of its columns. The order is 1 for ascending and -1 for descending. SORTBY(array, by_array, order, ...) sorts by a column that need not be part of the result, with as many keys as you like. Sorting the sales by amount, biggest first:

=SORT(A2:E11, 4, -1)

Give two keys as array constants to sort by region and then by amount, descending, in the same call. Ties keep their original order:

=SORT(B2:D11, {1,3}, {1,-1})

SORTBY can return one column ordered by another. Column G lists the regions ordered by amount, and column H the amounts ordered by region and then amount, without showing the sort column at all:

UNIQUE: distinct values and a live summary

UNIQUE(array, [by_col], [exactly_once]) returns the distinct rows of a range. Use it to list the regions without typing them. Because UNIQUE spills, another formula can refer to its result with #, which gives a summary table that grows and shrinks with your data, without a pivot table:

G1: =UNIQUE(B2:B11)
H1: =SUMIFS(D2:D11, B2:B11, G1#)
I1: =COUNTIFS(B2:B11, G1#)

Add a new region to the data and a new row appears in the summary. A few more counting patterns:

SEQUENCE, TAKE and friends

SEQUENCE(rows, [columns], [start], [step]) creates a series of numbers, which is handy for row numbers, calendars and test data. Because it is an array, it can be used in arithmetic: SEQUENCE(5)^2 gives the squares. Here A1 holds SEQUENCE(2,3), E1 holds a weekly series of dates and G1 the squares:

Microsoft 365 also has functions that cut and join arrays: TAKE and DROP keep or remove rows from the top or bottom, and VSTACK and HSTACK stack arrays vertically or horizontally:

LET: name the parts of a formula

LET(name1, value1, [name2, value2, ...], calculation) gives names to intermediate results inside one formula. It makes long formulas readable, and Excel computes each named part only once. The names exist only inside the LET:

Array arithmetic and its traps

In modern Excel a comparison on a range produces an array of TRUE and FALSE, and arithmetic on that array works on all the values at once. SUM((range>2)*range) is therefore a one-cell conditional total. In Excel 2019 and earlier the same formula needs Ctrl + Shift + Enter, or SUMPRODUCT, which handles arrays without it. A1:A5 hold 1, 3, 5, 7 and 9:

The two traps to remember are the double count when you add overlapping conditions, and the fact that OR and AND reduce an array to a single answer, so they cannot be used to test each row. Use + and * for that.

Errors and common mistakes

Mistake 1: typing over a spill range

The spilled cells below the anchor are not editable individually. Type into one of them and you get #SPILL! in the anchor. Clear the cell and the spill returns.

Mistake 2: expecting it to work in Excel 2019 or earlier

Older versions do not have FILTER, SORT, UNIQUE, SEQUENCE or LET. A workbook that uses them shows #NAME? when opened there. Share results as values, or use SUMPRODUCT, INDEX and helper columns for older users.

Mistake 3: mismatched sizes in FILTER

The include range must have exactly as many rows as the array. One row short gives #VALUE!, as in the table above.

Mistake 4: forgetting the if_empty argument

An empty result gives #CALC!, which then spreads into every formula that uses it. Add the third argument, or IFERROR.

Mistake 5: OR() and AND() inside FILTER

They return a single TRUE or FALSE for the whole array, so the filter keeps everything or nothing. Use * and +.

Cheat sheet

Try it yourself

Use the sales table in A1:E11 and work out each answer first.

1. List all amounts of 500 or more, sorted from biggest to smallest, in one formula.

=SORT(FILTER(D2:D11, D2:D11>=500), , -1)

2. How many different products are in the table, and what are they?

3. Write the FILTER include test for “North and (Pen or Bag)”, then for “(North and Pen) or Bag”. Do they return the same number of rows?

4. Make a formula that shows the squares of 1 to 5 using only one cell.

Frequently asked questions

What is a dynamic array in Excel?

A formula that returns more than one value and spills them into neighbouring cells automatically. Only the first cell holds the formula. Dynamic arrays exist in Microsoft 365 and Excel 2021 or later.

What does #SPILL! mean in Excel?

Excel cannot show the whole result because something is in the way: a non-empty cell, a merged cell, an Excel Table, or a result that is too big. Clear the obstruction and the formula works.

What does the # sign mean in a formula like A1#?

It is the spill range operator: it refers to the whole block that the formula in A1 spilled into, however big it currently is.

How do I filter with two conditions in Excel?

Use FILTER with the conditions multiplied for AND: =FILTER(A2:E11, (B2:B11="North") * (D2:D11>=500)). Add them for OR.

Why does FILTER return #CALC!?

No row matched, so the result is an empty array. Give FILTER a third argument, for example "none", or wrap it in IFERROR.

How do I remove duplicates with a formula?

=UNIQUE(range) returns the distinct values. Add SORT around it for an ordered list.

Do FILTER, SORT and UNIQUE work in Excel 2019?

No. They need Microsoft 365 or Excel 2021 or later. In Excel 2019 use SUMPRODUCT, INDEX, helper columns or the Advanced Filter.

What is the LET function for?

LET names values inside a formula so you can reuse them, which makes long formulas shorter, easier to read and often faster.