Excel Intermediate Lesson 4: Dynamic Arrays (FILTER, SORT, UNIQUE, SEQUENCE)

📎 This article includes 1 downloadable practice file ↓

📘 Excel Intermediate Course · Lesson 4 of 12

Since Excel 365 and 2021, a formula can return a whole list. Type it once and the results spill into the cells below. No more copying formulas down, no more helper columns.

The five functions

FILTER with several conditions

Multiply conditions for AND, add them for OR:

=FILTER(Sales!A2:L81, (Sales!H2:H81="Electronics") * (Sales!D2:D81="South"), "None")
=FILTER(Sales!A2:L81, (Sales!D2:D81="North") + (Sales!D2:D81="East"))

The last argument is what to show when nothing matches; without it you get #CALC!.

A live leaderboard in two formulas

E2: =UNIQUE(Sales!C2:C81)
F2: =SUMIFS(Sales!K2:K81, Sales!C2:C81, E2#)
G2: =SORTBY(E2#, F2#, -1)

E2# means “the whole spilled range starting at E2”. When a new rep appears in the data, every list grows by itself.

Common mistakes

• Referring to E2:E8 instead of E2#. The fixed range won’t grow when the list does.
• Spilling inside an Excel Table. Tables don’t allow spills; put dynamic formulas next to the table.
• Sharing with Excel 2016 users. They’ll see _xlfn.FILTER errors.

Practice

Download this lesson’s workbook below. Most answers are a single number built from a dynamic array, so the Check column can test them. Type your formula in the yellow column; the Check column turns green when the result matches, and the Answers sheet has a working formula for every task.