Syntax
=SMALL(data, n)
data- The range of numbers to look in.
n- Which rank to return: 1 is the smallest.
SMALL function in Google Sheets
SMALL returns the nth smallest value in a range. Use it for the second-fastest time, the third-cheapest quote, and similar rankings from the bottom.
Practice SMALL
Task 1 of 1
Lower lap times are faster. What is the 3rd fastest lap? Answer in D2.
Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.
SMALL examples
Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Product | Units | Revenue |
| 2 | Ava | West | Monitor | 12 | $2,400.00 |
| 3 | Liam | East | Keyboard | 30 | $1,500.00 |
| 4 | Maya | West | Mouse | 45 | $900.00 |
| 5 | Noah | North | Monitor | 8 | $1,600.00 |
| 6 | Zara | East | Monitor | 15 | $3,000.00 |
| 7 | Omar | West | Keyboard | 22 | $1,100.00 |
| Formula | Result | What it shows |
|---|---|---|
=SMALL(E2:E7, 1) | $900.00 | Same as MIN. |
=SMALL(E2:E7, 2) | $1,100.00 | Second-lowest revenue. |
=SMALL(D2:D7, 3) | 15 | Third-fewest units. |
Tips and common mistakes
- Blank cells and text are ignored, so they never show up as the "smallest" value.
- If n is larger than the count of numbers, SMALL returns #NUM!.
Related functions
Learning step by step? SMALL is covered in Lesson 31: LARGE & SMALL of the free course.