# RANK, LARGE & SMALL (Excel lesson 34 of 45)

> Who's on top, and by how much.

Source: https://spreadsheetpractice.com/learn/rank-large-small · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Analysis"
- **Time:** about 9 minutes, 3 graded exercises
- **Functions:** `RANK.EQ`, `LARGE`, `SMALL`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=RANK.EQ(B2,$B$2:$B$9)`: Rank, 1 = highest

## How it works

`RANK.EQ(number, range)` returns a value's place in a list: 1 is the largest. `LARGE(range, k)` returns the k-th largest value; `SMALL` the k-th smallest.

To get the *name* that goes with a top value, look it up: `XLOOKUP(MAX(B2:B9),B2:B9,A2:A9)`.

## Syntax

| Formula | What it does |
|---|---|
| `=RANK.EQ(B2,$B$2:$B$9)` | Rank, 1 = highest |
| `=LARGE($B$2:$B$9,E2)` | k-th largest |
| `=SMALL(B2:B9,1)` | Smallest |
| `=XLOOKUP(MAX(B2:B9),B2:B9,A2:A9)` | Name of the top value |

## Watch out for

- Lock the range in RANK, or the list shrinks as you fill down.
- Ties share a rank, and the next rank is skipped.

## The exercises

Solve them in the live grid at [RANK, LARGE & SMALL](https://spreadsheetpractice.com/learn/rank-large-small). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Rank the team.** In C2, rank each rep's sales (1 = highest). Fill down to C9.
2. **Top three values.** In F2, return the k-th largest sale using the k in E2. Fill down to F4.
3. **Name the leader.** In H2, return the name of the rep with the highest sales.

Previous: [ROUND, ROUNDUP & ROUNDDOWN](https://spreadsheetpractice.com/learn/round-roundup-rounddown) · Next: [SUMPRODUCT](https://spreadsheetpractice.com/learn/sumproduct)

---

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