FILTER, SORT and UNIQUE: dynamic arrays in Excel

In short: =FILTER(A2:C8,C2:C8>=1000) returns the matching rows, =SORT(A2:C8,3,-1) sorts by the third column, largest first, and =UNIQUE(A2:A8) drops duplicates. You type the formula in one cell and the result spills. These functions need Excel 2021 or later, or Microsoft 365.

Try it now

Column A lists cities, some of them several times. Build a list where each city appears once. Click the yellow cell and type the formula; the result fills the marked cells below it.

Task In E2, write a formula that lists every city from A2:A8 exactly once. The result spills down automatically.

	A	B	C	D	E
1	City	Customer	Revenue $		Cities
2	Berlin	Kaya Ltd	1200
3	Hamburg	Nordwerk	800
4	Berlin	Weber & Sons	450
5	Leipzig	Saxon Build	990
6	Hamburg	Harbor Logistics	300
7	Dresden	Elbe Valley Inc	1500
8	Berlin	Spree Media	700

What spilling means

A classic formula returns one value into one cell. Dynamic array functions take a range and hand back a range. You enter the formula in E2 and Excel fills as many cells down and to the right as the result needs. When the source data changes, the list grows or shrinks by itself. Only the first cell holds the formula; the others show it greyed out in the formula bar.

If anything blocks the way, even a single space, you get #SPILL!. Clear the cells below and to the right and the result appears. Spilling also does not work inside a formatted table (Ctrl+T), which returns #SPILL! as well.

The FILTER formula

Argument	Meaning	In the example
array	The range to return. The result has as many columns as this range.	A2:C8
include	A condition that is TRUE or FALSE for each row. It must have the same number of rows as the array.	C2:C8>=1000
if_empty	Optional: what to show when no row matches. Without it you get #CALC!.	"no matches"

With all three arguments: =FILTER(A2:C8,C2:C8>=1000,"no matches").

One more: all large orders

This time whole rows with three columns come back. Note that revenue of exactly 1,000 counts as well.

Task In E2, write a formula that returns every row from A2:C8 with revenue of at least 1000, including all three columns. The range E2:G8 is empty for the result.

	A	B	C	D	E	F	G
1	Customer	City	Revenue $		Customer	City	Revenue $
2	Kaya Ltd	Berlin	1450
3	Nordwerk	Hamburg	820
4	Weber & Sons	Frankfurt	1000
5	Saxon Build	Leipzig	640
6	Harbor Logistics	Bremen	2300
7	Elbe Valley Inc	Dresden	980
8	Spree Media	Berlin	1200

Several conditions: * for AND, + for OR

AND() and OR() do not work inside FILTER because they collapse everything to one value. Put each condition in brackets and combine them with arithmetic instead. TRUE counts as 1 and FALSE as 0.

• AND with a multiplication sign: =FILTER(A2:C8,(B2:B8="Berlin")*(C2:C8>=1000)) keeps Berlin customers with at least 1,000. The product is 1 only when both conditions are 1.
• OR with a plus sign: =FILTER(A2:C8,(B2:B8="Berlin")+(B2:B8="Hamburg")) keeps customers from either city. As soon as one condition is 1, the sum is above 0.

Do not skip the brackets. Without them Excel multiplies or adds first and compares afterwards, which gives nonsense.

SORT and SORTBY

=SORT(array,sort_index,sort_order) returns the range in sorted order. sort_index is the column inside the range to sort by, and sort_order is 1 for ascending (the default) or -1 for descending. So =SORT(A2:C8,3,-1) puts the highest revenue on top.

SORTBY sorts one range by another that does not have to appear in the result: =SORTBY(A2:A8,C2:C8,-1) lists only the customer names, ordered by revenue. Neither function touches the source data, unlike sorting from the ribbon.

UNIQUE and nesting functions

UNIQUE removes duplicates and keeps the order of first appearance. The real power comes from nesting:

• =SORT(UNIQUE(A2:A100)) gives an alphabetical list of all cities, handy as the base for a summary or a dropdown.
• =SORT(FILTER(A2:C8,C2:C8>=1000),3,-1) pulls the large orders and ranks them by revenue.
• =COUNTA(UNIQUE(A2:A100)) counts how many different cities there are.

Referring to a spill range: E2#

A spill range has no fixed size, so a normal reference like E2:E8 can miss rows or include blanks. Add a hash to the first cell instead: E2# always means the whole range spilling from E2. =COUNTA(E2#) counts the entries, and with the data from the first exercise =SUMIF(A2:A8,E2#,C2:C8) returns the revenue for every city in the list and spills itself.

Common errors

#SPILL!

Something is in the way. Click the error cell and Excel draws a dashed border around the range it needs. Clear it or move the formula.

#CALC! from FILTER

No row meets the condition, so the result is empty. Add a third argument such as "no matches".

#VALUE! from FILTER

The array and the condition cover different rows, for example A2:C8 with C2:C9. Make them line up.

#NAME?

Your Excel version does not know the function. This happens in Excel 2019 and earlier, including when someone sends you a file that uses these formulas.

FILTER, SORT and UNIQUE in German Excel

German Excel translates most names and uses semicolons between arguments:

English	German
=FILTER(A2:C8,C2:C8>=1000)	=FILTER(A2:C8;C2:C8>=1000)
SORT	SORTIEREN
SORTBY	SORTIERENNACH
UNIQUE	EINDEUTIG
#SPILL! / #CALC!	#ÜBERLAUF! / #KALK!

Which Excel versions have these functions?

FILTER, SORT, SORTBY and UNIQUE are available in Excel 2021, Excel 2024 and Microsoft 365, on Windows and Mac. Excel 2019 and 2016 do not have them. There you get similar results from the ribbon: Data > Sort and Data > Filter for AutoFilter, Data > Advanced (Advanced Filter) with "Unique records only" for a list without duplicates, or Data > Remove Duplicates. The catch is that none of these update automatically. See Sort and Filter for the details.

FAQ