SMALL Function
Returns the k-th smallest number in a set.
Category
Statistical
Available in
All versions, Microsoft 365
Returns
A number
Overview
SMALL(range, 1) is the smallest value (same as MIN), SMALL(range, 2) the second smallest, and so on. Tied values each take a position.
Use it for bottom-N lists, or to sort numbers with a formula.
Syntax
=SMALL(array, k) Explain
Arguments
| Argument | Required? | What it does |
|---|---|---|
| array | Yes | The range or array of numbers. |
| k | Yes | The position from the bottom: 1 = smallest. |
Examples
Example 1 — Bottom 3 sales results
Ben and Gus tie on 48,500, so it appears twice.
E2fx=SMALL($C$2:$C$8,D2)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Place | Sales |
| 2 | Ana | North | 62000 | 1 | 39800 |
| 3 | Ben | South | 48500 | 2 | 48500 |
| 4 | Cy | North | 50000 | 3 | 48500 |
| 5 | Di | East | 71250 | ||
| 6 | Ed | South | 39800 | ||
| 7 | Fay | North | 55400 | ||
| 8 | Gus | East | 48500 |
=SMALL($C$2:$C$8,D2) Explain
Example 2 — Sort with a formula
With ROWS counting up as it fills down, SMALL lists the values in ascending order.
=SMALL($C$2:$C$8,ROWS($E$2:E2)) Explain
Common mistakes
- #NUM! k is 0 or less, or bigger than the number of values.
Notes
- In Microsoft 365, SORT does a whole sorted list in one formula.
- Related functions: LARGE, MIN, RANK.EQ, SORT.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.