FILTER, SORT, UNIQUE and LET: Excel Dynamic Array Functions

Dynamic array functions are one of the biggest changes to Excel in decades. A single formula can return an entire table that grows or shrinks on its own when the data changes. They're available in Microsoft 365, Excel 2021 and later, Excel for the web and Google Sheets. In Excel 2019 or earlier you'll see a #NAME? error.

1. Understand spilling
2. UNIQUE and SORT
3. FILTER
4. Top 5 and summaries
5. LET

Spilling

Type =SEQUENCE(5) in an empty cell. Even though you typed in one cell, the numbers 1 through 5 appear going down. That's spilling: the formula takes up as many cells as it has results.

• Only the first cell holds the formula; the others show it grayed out in the formula bar.
• To refer to the whole result from another formula, use the first cell plus #: if the formula is in G2, the whole range is G2#.
• If anything is in the way, you get a #SPILL! error. Delete or move whatever is blocking it.

UNIQUE: lists without duplicates

=UNIQUE(Sales[Customer])

Returns each customer once. With a third argument of TRUE it returns only the values that appear exactly once: =UNIQUE(Sales[Customer],,TRUE), handy for spotting one-time buyers you might want to win back.

UNIQUE also works across several adjacent columns and returns each distinct combination. On the practice file, =UNIQUE(tblSales[[Region]:[Salesperson]]) returns 6 rows, one per region-salesperson pair, which instantly confirms that each rep covers a single region. If a rep had sold in two regions, you'd see an extra row for them.

SORT and SORTBY

=SORT(UNIQUE(Sales[Customer]))                   ← customers in alphabetical order
=SORT(Sales, 4, -1)                              ← the whole table sorted by column 4, largest first
=SORTBY(Sales[Salesperson], Sales[Amount], -1)   ← the salesperson on each order, from largest order to smallest

SORT's third argument is 1 (ascending, the default) or -1 (descending). SORTBY sorts by a column that doesn't have to appear in the result: the formula above returns one row per order (with repeated names), not a total per salesperson. For totals per person, see the summaries section below.

FILTER: a lookup that returns every match

=FILTER(array, include, [if_empty])

Unlike VLOOKUP, which returns only the first match, FILTER returns every row that meets the condition:

=FILTER(Sales, Sales[Region]="West", "No sales")

Several conditions

Combine conditions with arithmetic: multiplying means "AND," adding means "OR." Wrap each condition in parentheses.

West AND over $1,000:
=FILTER(Sales, (Sales[Region]="West") * (Sales[Amount]>1000))

West OR East:
=FILTER(Sales, (Sales[Region]="West") + (Sales[Region]="East"))

One month's sales (dates in H1 and H2):
=FILTER(Sales, (Sales[Date]>=H1) * (Sales[Date]<=H2))

A search box in your sheet

Combine FILTER with SEARCH to match partial text, like a web search. With the search term in B1:

=FILTER(Customers, ISNUMBER(SEARCH(B1, Customers[Name])), "No matches")

Type "coffee" in B1 and you'll see every customer whose name contains "coffee," regardless of capitalization.

An automatic top 5

=TAKE(SORT(Sales, 4, -1), 5)

TAKE (Microsoft 365 and Excel 2024) keeps the first rows of a result. If your version doesn't have it, use a PivotTable with the Top 10 filter (lesson 9), or let the sorted table spill and read the first five rows.

Other useful functions from the same family (SEQUENCE exists since Excel 2021; the other three need Microsoft 365 or Excel 2024):

Summaries that update themselves

Combine a spilled list with the functions from lesson 7. With the unique customers in G2#:

H2: =SUMIFS(Sales[Amount], Sales[Customer], G2#)

Because G2# returns several customers, the formula in H2 spills too: one total per customer, and if a new customer appears, the summary grows on its own. It's a PivotTable-style report that never needs refreshing. In Microsoft 365 you can go one step further with GROUPBY: =GROUPBY(tblSales[Salesperson], tblSales[Amount], SUM) returns every salesperson with their total, plus a grand total, in one formula.

Worked example: which orders carry the Portland roaster?

The roaster's sales manager (the Sales sheet in the practice file) suspects a handful of big orders drive the business. Three formulas prove it, with no manual filters or PivotTables:

Orders over $1,000:       =ROWS(FILTER(tblSales, tblSales[Amount]>1000))               → 22
Revenue from those:       =SUM(FILTER(tblSales[Amount], tblSales[Amount]>1000))      → $37,245
Which products are they?  =UNIQUE(FILTER(tblSales[Product], tblSales[Amount]>1000))  → French Press

Just 22 of 300 orders (7.3%) bring in $37,245 of $122,759.50 (30.3%), and every one of them is the same product:

Number of orders: the big ones are 7.3%.

Revenue: those same orders are 30.3% of sales.

To see the five largest, =TAKE(SORT(tblSales, 9, -1), 5) returns:

(The real result also includes Code, Category, Units and Price; five columns are shown here.) The takeaway for management: if French presses go out of stock, nearly a third of revenue goes with them. And because it's all formulas, when October's orders are added to tblSales the analysis updates itself.

Common dynamic array mistakes

LET: long formulas you can read

LET lets you name intermediate results inside a formula. It makes formulas easier to read, and faster, because each calculation runs only once:

=LET(
  westSales, FILTER(Sales[Amount], Sales[Region]="West"),
  total, SUM(westSales),
  orders, COUNT(westSales),
  total / orders
)

It returns the West's average order: with the practice table (use tblSales instead of Sales) that's $449.18. You can write a formula on several lines with Alt + Enter in the formula bar (Mac: Option + Return).

LET also tidies up more ambitious formulas, like a salesperson ranking with totals in a single cell:

=LET(
  s, UNIQUE(tblSales[Salesperson]),
  t, SUMIFS(tblSales[Amount], tblSales[Salesperson], s),
  SORTBY(HSTACK(s, t), t, -1)
)

It returns two columns, from Rachel ($25,029) down to Brandon ($13,238), and re-sorts itself when sales change. HSTACK places columns side by side (Microsoft 365 and Excel 2024).

A4: =SORT(FILTER(Sales, Sales[Region]=B1, "No sales"), 5, -1)
H4: =SORT(UNIQUE(FILTER(Sales[Salesperson], Sales[Region]=B1)))
I4: =SUMIFS(Sales[Amount], Sales[Salesperson], H4#, Sales[Region], B1)
D1: =LET(v, FILTER(Sales[Amount], Sales[Region]=B1), SUM(v)/COUNT(v))

Summary

• One formula can spill into many cells; G2# refers to the whole result.
• UNIQUE, SORT and FILTER replace a lot of manual work.
• Inside FILTER, * means AND and + means OR.
• LET names intermediate steps so long formulas stay readable.
• ROWS, SUM and UNIQUE wrapped around a FILTER answer business questions in one line.

Next lesson: Power Query in Excel: Import and Clean Data Automatically. You'll import and clean data so the report that takes you an hour today refreshes with one click.