AdvancedLesson 21 of 305 min read

Dynamic arrays: FILTER, SORT, UNIQUE, SEQUENCE and LET

Use formulas that return many results at once, learn how spilling works, and keep your files compatible with older versions.

In this lesson you will

  • Explain spilling
  • Use FILTER, SORT, UNIQUE and SEQUENCE
  • Name steps with LET
  • Plan for compatibility

About 16 minutes

One formula, many results

Alicia used to copy a formula down a hundred rows to pull out every North sale. Her colleague types one formula in one cell and the whole list appears below it, growing and shrinking when the data changes. This is the biggest change in Excel for years: dynamic arrays. A formula can return several values at once, and they ‘spill’ into the cells below and beside it. This lesson explains how that works, which functions use it and how to stay compatible with people who do not have it yet.

Good to know

Dynamic array functions need Microsoft 365 or Excel 2021 and later, and most also work in Google Sheets. Older versions do not have them. The spreadsheet program used to prepare this course could not calculate them, so the formulas below are shown as patterns, and what they return is worked out separately from the same sample data.

Spilling

Type a formula that returns many values in one cell, press Enter, and the results fill the cells below and to the right. That block is the spill range, outlined in blue when you click inside it. You edit only the first cell; the others are part of the same formula. If something blocks the way, such as text in a cell the results need, you get a #SPILL! error. Clear the obstacle and it fills in. To refer to a whole spill range from another formula, use the first cell followed by a hash, as in =SUM(H2#).

The core functions

Function What it returns
FILTER(range, test) Only the rows that meet a condition
SORT(range, column, order) The rows in a new order
SORTBY(range, by, order) Rows sorted by another range
UNIQUE(range) The list without repeats
SEQUENCE(rows, cols) A grid of numbers: 1, 2, 3 …
XLOOKUP(find, in, out) A lookup that can return many columns

What they return for our sample data

The program below applies the same ideas to the twelve sample sales in plain Python and prints what the matching formulas would return. This is a model of the result, not Excel running the formulas, and it is shown for that reason. The output is real:

import datetime as dt
rows = [(dt.date(2026,1,5),"North","Pen",10,20),(dt.date(2026,1,12),"South","Notebook",5,60),(dt.date(2026,1,20),"North","Notebook",8,60),
        (dt.date(2026,2,3),"East","Pen",20,20),(dt.date(2026,2,10),"East","Notebook",3,60),(dt.date(2026,2,14),"South","Pen",15,20),
        (dt.date(2026,2,25),"West","Notebook",6,60),(dt.date(2026,3,2),"North","Pen",12,20),(dt.date(2026,3,15),"East","Notebook",9,60),
        (dt.date(2026,3,22),"South","Notebook",4,60),(dt.date(2026,3,30),"West","Pen",25,20),(dt.date(2026,3,31),"North","Notebook",7,60)]
rev = [(d, r, p, u * pr) for d, r, p, u, pr in rows]

# =FILTER(rows, region="North")
north = [x for x in rev if x[1] == "North"]
print("FILTER North:", [(x[0].isoformat(), x[2], x[3]) for x in north])
# =UNIQUE(region column)
seen = []
for x in rev:
    if x[1] not in seen: seen.append(x[1])
print("UNIQUE regions:", seen)
# =TAKE(SORTBY(revenue, revenue, -1), 3)  (largest three revenues)
print("SORT top 3 revenue:", sorted((x[3] for x in rev), reverse=True)[:3])
# =SEQUENCE(4,1,1,1)
print("SEQUENCE(4):", list(range(1, 5)))
FILTER North: [('2026-01-05', 'Pen', 200), ('2026-01-20', 'Notebook', 480), ('2026-03-02', 'Pen', 240), ('2026-03-31', 'Notebook', 420)]
UNIQUE regions: ['North', 'South', 'East', 'West']
SORT top 3 revenue: [540, 500, 480]
SEQUENCE(4): [1, 2, 3, 4]

In Excel, the first line would be =FILTER(A2:F13,B2:B13=“North”), which spills four rows with all columns. The unique list would be =UNIQUE(B2:B13), giving North, South, East, West in order of first appearance. A top-three list is =TAKE(SORT(F2:F13,,–1),3) in the newest versions, or =LARGE(F2:F13,{1;2;3}) in older ones.

Old ways that still work everywhere

To count the distinct regions without UNIQUE, a classic formula is =SUMPRODUCT(1/COUNTIF(B2:B13,B2:B13)). It was really calculated on the twelve regions and returned 4, which matches the four regions found above. If you share files widely, such formulas are safer than dynamic arrays.

LET: name things inside a formula

LET gives names to parts of a calculation so long formulas become readable and faster. For example =LET(rev, D2*E2, IF(rev>=500, rev*0.9, rev)) calculates the revenue once, calls it rev, and reuses it. It is a pattern for modern Excel and was not run here. If you share with older Excel, put the sub-step in a helper column instead, which is just as clear.

Using spilled results

  • Drop-down lists that grow: point the validation list at a UNIQUE spill, written as =H2#.
  • Dynamic reports: a FILTER driven by a drop-down that holds the region name.
  • Charts: a chart can use a spill range, so it grows with the data.
  • Combined: SORT(FILTER(…)) gives a sorted, filtered list in one go.

Compatibility and safety

  • Save as .xlsx: older Excel shows the cached values but cannot recalculate dynamic formulas.
  • An @ sign may appear in formulas opened from old files. It means ‘only one value here’.
  • Check that the spill area has room, and do not put other data in its path.
  • Keep a plain version of important reports for people who cannot use the new functions.

Practice

Make a list of at least twenty sales or expenses. If you have Excel 365 or Google Sheets, use FILTER to show one category, UNIQUE to list the categories, SORT to rank the amounts and SEQUENCE to number the rows. If you do not, rebuild the unique count with SUMPRODUCT and the top three with LARGE, and compare with the Python model idea by checking the answers by hand.

Practice answer

The count of unique categories from UNIQUE (using ROWS) should equal the SUMPRODUCT result. If they differ, a category has a different spelling or an extra space. Clean it with TRIM first (lesson 14).

Common mistake

Using a dynamic array in a workbook that colleagues open in an older version. They will see errors or frozen values. Ask what version people use, or provide a plain alternative.

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

What is a spill range?

  1. A chart area
  2. The cells a multi-value formula fills
  3. A sheet name
  4. A print area

You edit only the first cell.

What does #SPILL! mean?

  1. The file is corrupt
  2. A date is wrong
  3. A sheet is hidden
  4. Something blocks the results

Clear the cells in the way.

Which function removes repeats from a list?

  1. UNIQUE
  2. SORT
  3. FILTER
  4. SEQUENCE

UNIQUE lists each value once.

What does LET do?

  1. Locks a sheet
  2. Lists errors
  3. Names parts of a calculation
  4. Imports data

It makes long formulas readable.

Which formula counts distinct values in any Excel version?

  1. UNIQUE only
  2. FILTER only
  3. SEQUENCE
  4. SUMPRODUCT(1/COUNTIF(range,range))

It works everywhere.

Summary

  • Dynamic array formulas return many results that spill into cells.
  • FILTER, SORT, UNIQUE and SEQUENCE are the core functions.
  • LET names parts of a formula.
  • Provide older-version alternatives when you share files.

Next lesson

Next: advanced lookups and array logic that works in every version.

Questions

Which Excel versions support dynamic arrays?

Microsoft 365 and Excel 2021 and later. Check your version under File, Account.

Why does my formula show #SPILL!?

Cells in the spill area are not empty, or the area is merged or inside a table. Clear them.

Do these work in Google Sheets?

FILTER, SORT, UNIQUE and SEQUENCE work in Google Sheets. LET and some newer functions may differ, so check its function list.

Progress is saved in this browser.