Lesson 19 of 29, Dynamic arrays
UNIQUE & SORT
One formula, many results that spill into the cells below.
Watch it done
3 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
Dynamic array functions return a whole list from one cell. The results spill down and across; here they're shaded blue. If anything blocks the spill area you get #SPILL!.
UNIQUE removes duplicates. SORT(array, column, order) orders rows; use -1 for descending. Nest them for a sorted list of distinct values. Refer to a whole spill with #, as in E2#.
Syntax
=UNIQUE(B2:B11)Distinct regions=SORT(UNIQUE(A2:A11))Distinct reps, A–Z=SORT(A2:C11,3,-1)Whole table by column 3, descendingWatch out for
- Keep the cells under a dynamic formula empty, or it shows #SPILL!.
- Edit only the first cell of a spill; the rest are read-only.
The exercises
- Distinct regions. In E2, list each region once.
- Sorted rep list. In F2, list each rep once, sorted A to Z.
- Leaderboard. In H2, return the whole table A2:C11 sorted by amount, largest first.
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.
- Distinct regions
- Sorted rep list
- Leaderboard