# UNIQUE & SORT (Excel lesson 26 of 45)

> One formula, many results that spill into the cells below.

Source: https://spreadsheetpractice.com/learn/unique-sort · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Dynamic arrays"
- **Time:** about 9 minutes, 3 graded exercises
- **Functions:** `UNIQUE`, `SORT`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=UNIQUE(B2:B11)`: Distinct regions

## 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

| Formula | What it does |
|---|---|
| `=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

Solve them in the live grid at [UNIQUE & SORT](https://spreadsheetpractice.com/learn/unique-sort). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

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.

Previous: [Tenure, quarters & weekdays](https://spreadsheetpractice.com/learn/tenure-quarters-weekdays) · Next: [FILTER](https://spreadsheetpractice.com/learn/filter-function)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
