Chapter 14 · advanced · 6 min
Dynamic arrays: FILTER, SORT, and spill
Every formula so far has lived in one cell and returned one value. FILTER and SORT are different — they return an entire range, and Excel automatically "spills" that range across as many cells as it needs, starting from wherever the formula itself lives. Nest them and you can filter and sort in a single formula, with zero helper columns.
The grid
A staff table in A1:C6 — Name, Department, Salary — five employees across Engineering, Sales, and Marketing.
FILTER: one formula, one cell, many rows out
FILTER(range, condition) returns every row of range where condition is TRUE, spilling downward from the cell the formula is typed into. No copy-down, no helper column of TRUE/FALSE flags.
=FILTER(A2:A6,B2:B6="Engineering")Three people are in Engineering: Aiman, Chandran, Elaine. How many cells will this spill into?
Scroll to see all 11 columns →
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Name | Department | Salary | ||||||||
| 2 | Aiman | Engineering | 5200 | =FILTER(A2:A6,B2:B6="Engineering") | |||||||
| 3 | Bee Choo | Sales | 4100 | ||||||||
| 4 | Chandran | Engineering | 6100 | ||||||||
| 5 | Dev | Marketing | 4800 | ||||||||
| 6 | Elaine | Engineering | 5700 |
SORT: reorder a whole range in one formula
SORT(range, sort_index, order) returns range reordered — sort_index picks which column to sort by (1 for the first column of the range), order is 1 for ascending or -1 for descending.
=SORT(C2:C6,1,-1)The five salaries are 5200, 4100, 6100, 4800, 5700. What's the first value this spills?
Scroll to see all 11 columns →
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Name | Department | Salary | ||||||||
| 2 | Aiman | Engineering | 5200 | =SORT(C2:C6,1,-1) | |||||||
| 3 | Bee Choo | Sales | 4100 | ||||||||
| 4 | Chandran | Engineering | 6100 | ||||||||
| 5 | Dev | Marketing | 4800 | ||||||||
| 6 | Elaine | Engineering | 5700 |
Nesting FILTER inside SORT
FILTER's output is itself a range, so it can be SORT's first argument — filter to Engineering, then sort what's left by salary, all in one formula, no intermediate cells:
=SORT(FILTER(A2:C6,B2:B6="Engineering"),3,-1)sort_index 3 refers to the third column of the filtered result (Salary), not the third column of the original table — the two ranges have the same column order here, but if FILTER had dropped a column, the index would shift too.
Engineering staff, highest salary first
In I2, filter the staff table to Engineering and sort the result by salary descending, in one formula.
Scroll to see all 11 columns →
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Name | Department | Salary | ||||||||
| 2 | Aiman | Engineering | 5200 | ||||||||
| 3 | Bee Choo | Sales | 4100 | ||||||||
| 4 | Chandran | Engineering | 6100 | ||||||||
| 5 | Dev | Marketing | 4800 | ||||||||
| 6 | Elaine | Engineering | 5700 |
The trap: #SPILL! when the path is blocked
A spilling formula needs every cell in its output range to be empty. Type anything — even a single space — into a cell that a FILTER or SORT formula would spill into, and the formula returns #SPILL! instead of its result, even though the formula itself is completely correct. The fix is never to change the formula; it's to clear whatever is blocking the spill range. This is the dynamic-array equivalent of a locked cell reference pointing at the wrong place — the bug is adjacent to the formula, not inside it.