Dynamic arrays — Excel Course

FILTER, SORT, SORTBY, UNIQUE and SEQUENCE. Learn it step by step with guided practice on Excelquro.

Understand

A dynamic-array formula occupies one anchor cell and can spill into neighboring cells. FILTER returns matching rows, SORT orders them, UNIQUE removes repeated categories and SEQUENCE generates controlled sequences. A blocked spill area causes #SPILL!, so the destination must remain available.

Worked example

FILTER chooses the population; SORT orders the returned array. Adding another Mumbai record can change the spill output automatically.

=SORT(FILTER(A2:C10,B2:B10="Mumbai"),3,-1)

Follow along

1. Create a clean source table.
2. Write a FILTER for one business condition.
3. Check the spill area for blocked cells.
4. Wrap FILTER in SORT to control presentation order.
5. Use UNIQUE for dimension lists used in dropdowns or summaries.
6. Change the source and observe the spill range resize.

Interactive practice runs in your browser.