Spreadsheet Practice

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)
Pro9 minutes, 3 exercisesUNIQUESORT

Watch it done

3 steps

Watch 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, descending

Watch 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

  1. Distinct regions. In E2, list each region once.
  2. Sorted rep list. In F2, list each rep once, sorted A to Z.
  3. 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.

  1. Distinct regions
  2. Sorted rep list
  3. Leaderboard