Lesson 26 of 45, Dynamic arrays
UNIQUE & SORT
Quick answer
One formula, many results that spill into the cells below.
- Key formula
=UNIQUE(B2:B11)(Distinct regions)
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.
Questions people ask about this
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
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 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.
- Distinct regions
- Sorted rep list
- Leaderboard