Dynamic Arrays in Excel: SORT, FILTER and UNIQUE in Plain English

Dynamic Arrays in Excel: SORT, FILTER and UNIQUE in Plain English featured illustration

📎 This article includes 1 downloadable practice file ↓

⏱ 2 min readUpdated 5 October 2026

In Excel 365 one formula can return many results that “spill” into neighbouring cells. It changes how you build reports: no more copying formulas down, no helper columns, results that grow and shrink with the data.

In this article
  1. Spilling and the # reference
  2. FILTER
  3. SORT and SORTBY
  4. UNIQUE
  5. SEQUENCE and LET
  6. #SPILL! errors
  7. Continue learning

Spilling and the # reference

Type =UNIQUE(B2:B200) in one cell: Excel fills as many cells below as needed and draws a blue border. Refer to the whole result with the spill operator: =COUNTA(D2#), =SORT(D2#).

Advertisement

FILTER

=FILTER(A2:F200, C2:C200="North")                                  ' one condition
=FILTER(A2:F200, (C2:C200="North") * (F2:F200>50000))              ' AND: multiply
=FILTER(A2:F200, (C2:C200="North") + (C2:C200="East"))             ' OR: add
=FILTER(A2:F200, C2:C200=H1, "No orders")                          ' message if none

SORT and SORTBY

=SORT(A2:F200, 6, -1)                          ' by column 6, largest first
=SORTBY(A2:A200, F2:F200, -1)                  ' sort names by another column
=TAKE(SORT(FILTER(A2:F200, C2:C200="North"), 6, -1), 5)   ' top 5 North orders

UNIQUE

=UNIQUE(C2:C200)                  ' distinct values
=COUNTA(UNIQUE(C2:C200))          ' how many distinct
=UNIQUE(C2:C200, , TRUE)          ' values that appear exactly once
=SORT(UNIQUE(FILTER(D2:D200, C2:C200="North")))   ' distinct North customers, A-Z

SEQUENCE and LET

=SEQUENCE(12, 1, DATE(2026,4,1), 0)          ' twelve copies of a date (use EDATE for months)
=EDATE(DATE(2026,4,1), SEQUENCE(12, 1, 0))   ' Apr 2026 … Mar 2027
=LET(n, FILTER(F2:F200, C2:C200="North"), SUM(n) / COUNT(n))

LET names intermediate results so long formulas stay readable and calculate once.

💡 Build a mini report from one cell: =VSTACK({“Region”,”Sales”}, HSTACK(UNIQUE(C2:C200), SUMIFS(F2:F200, C2:C200, UNIQUE(C2:C200)))).

#SPILL! errors

Cause Fix
Something in the spill range Clear the cells (Excel highlights the area)
Formula inside an Excel Table Tables can’t hold spills; put it next to the table
Whole-column references too big Use exact ranges or Table columns

Older Excel shows #NAME?: these functions need Excel 365 or 2021+.

⚡ Interactive Practice Lab & Formula Tester:

Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.

Continue learning

All Excel Formulas guides →
Dependent Dropdown Lists in Excel (Two Methods That Actually Work)Create a second dropdown that changes with the first — Category then Product, Country…Why 0.1 + 0.2 Is Not Exactly 0.3 (and Why Excel Sometimes Agrees)Computers store decimals in binary, so 0.1 + 0.2 becomes 0.30000000000000004. Why it happens,…Excel’s Strangest Behaviours Explained: 15 “Bugs” That Are Actually FeaturesWhy Excel turns 1-2 into a date, drops leading zeros, shows ####, changes long…Why Does Excel Have Exactly 1,048,576 Rows? The Logic Behind Software LimitsWhy Excel stops at 1,048,576 rows and column XFD, why old Excel had 65,536…

📎 Practice files for this article

  • 📗
    Dynamic arrays scenarios workbookFILTER, SORT, UNIQUE, SEQUENCE and LET with PASS/FAIL checks, a 'Try it' sheet with spilling formulas, and the two FILTER mistakes that break reports.
    ⬇ XLSX · 10 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong