The Excel SMALL function, explained interactively.
SMALL returns the k-th smallest number in a range. It’s a generalised MIN: SMALL(range, 1) is identical to MIN(range), but you can ask for the 2nd, 3rd, or any rank from the bottom. Like its counterpart LARGE, the main pitfall is choosing a k that exceeds the count of numeric values — that’s when you get #NUM!.
How to use SMALL
- Type
=SMALL(and select the array — the range of numbers you want to rank from the bottom. Text and blanks are silently skipped. - Add a comma, then type
k— the rank from the smallest.k=1returns the minimum (same as MIN),k=2the second-smallest, and so on. - Close the parenthesis. If
kis 0, negative, or larger than the count of numeric cells, SMALL returns#NUM!. Wrap withIFERRORto handle it gracefully. - Because dates are stored as numbers, SMALL works on date columns too — the smallest serial number is the earliest date. Format the result cell as a date.
The demo starts at k = 2 so you can immediately see how SMALL differs from a plain MIN. Use the + / − buttons to step through ranks.
| A | B | D | |
|---|---|---|---|
| 1 | Student | Score | SMALL result |
| 2 | Ava | 87 | 78 |
| 3 | Ben | 92 | |
| 4 | Chloe | 78 | |
| 5 | Dev | 95 | |
| 6 | Elena | 88 | |
| 7 | Farid | 73 | |
| 8 | Grace | 91 |
=SMALL(B2:B8, 2). SMALL with k=2 returns 78 — Chloe holds the #2 spot from the bottom. SMALL(range, 1) = MIN(range).SMALL syntax and arguments
Two required arguments. See Microsoft’s official SMALL reference for the canonical specification.
B2:B100), a named range, or an array literal. Text, blanks, and logical values in cell references are silently skipped. Works on dates because Excel stores them as serial integers.k=1 is the smallest value (= MIN), k=2 is the second-smallest, and so on. Must be a positive integer between 1 and the count of numeric values in the array — k=0 or k > COUNT(array) returns #NUM!. Decimal k values are truncated to an integer.SMALL examples
Four patterns covering the most common real-world SMALL uses.
Example 1: Bottom 3 performers from a list
Populate a bottom-N table or identify the three lowest-performing items without sorting.
Drive k from a helper column (1, 2, 3…) so the formula copies down automatically. On ties both tied values occupy their respective rank positions — two scores of 73 would each appear in the bottom-N list.
Example 2: Second-earliest date in a column
Find the second order placed, second hire date, or any “second earliest” without sorting the table.
Excel stores dates as serial numbers, so SMALL ranks them chronologically. The smallest serial is the earliest date. Format the result cell as a date (Home → Number → Short Date) to display it correctly. Works identically in Google Sheets.
Example 3: Skip the minimum — use SMALL(range, 2)
When the absolute minimum is an outlier or a known anomaly, the second-smallest is often the more useful floor price or lower bound.
The widget priced at $0.99 may be a discontinued SKU or a pricing error. SMALL(range, 2) skips it and returns the next-lowest price instead. Pair with =SMALL(range, 1) in a nearby cell to show both the absolute floor and the “working minimum” side by side.
Example 4: SMALL with IFERROR — graceful k overflow
When generating a bottom-N list of unknown length, IFERROR prevents #NUM! from appearing past the last valid rank.
With only 3 numeric values in the array, k = 4 and k = 5 would normally produce #NUM!. Wrapping with IFERROR returns a clean dash instead. Copy this pattern down as many rows as you like — rows past the array size gracefully show “—”.
Common SMALL errors and fixes
Four failure modes and how to recover from each.
#NUM! — k is 0 or exceeds the array size
Cause: k must be between 1 and the count of numeric values in the array, inclusive.k=0 and k < 0 are always invalid; k=5 on a 3-number range is equally invalid.
Check =COUNT(array) to see how many numeric cells exist. Wrap with =IFERROR(SMALL(…), "—") to return a dash instead of the error when k might overflow.
#VALUE! — k is text or a formula error
Cause: if k resolves to text, a boolean, or an error value, SMALL propagates #VALUE!. An empty cell referenced for k may behave as zero or text depending on context.
Ensure the cell or formula providing k is numeric. Use =ISNUMBER(k_cell) to diagnose. Decimal values are silently truncated (2.9 → 2), but text is not coerced.
Text and logical values are silently ignored
Behaviour: SMALL skips non-numeric cells in range references — same as MIN. A range with 10 cells where 3 are text has an effective size of 7; k=8 gives #NUM! even though 10 cells exist.
Use =COUNT(range) rather than =COUNTA(range) to measure the true numeric size of the array before setting k.
Ties — the same value occupies multiple ranks
Behaviour: duplicate values each occupy their own rank position. If the two lowest scores are both 73, SMALL(range, 1) and SMALL(range, 2) both return 73. The third rank returns the next distinct higher value.
To get a distinct bottom-N list (no repeated values), wrap with UNIQUE (Excel 365 / Google Sheets) before ranking, or use an array formula with COUNTIF to exclude already-seen values.
SMALL vs MIN, LARGE & RANK
Four functions that answer ranking questions — pick by whether you need a value or a position, and from which end.
| Function | Returns | k / n argument | Typical use |
|---|---|---|---|
| SMALL | Nth smallest value | k (position from bottom) | Bottom-N lists, 2nd earliest date, skip-outlier floor |
| MIN | Smallest value (= SMALL k=1) | None | Single lowest value — simpler when only the floor matters |
| LARGE | Nth largest value | k (position from top) | Top-N lists, podium scores, percentile cut-offs |
| RANK | Ordinal position of a value | order (0 = desc, 1 = asc) | Leaderboard column — "what rank is this specific cell?" |
Rule of thumb: use SMALL when you know the rank and want the value from the bottom; use RANK when you have the value and want to know its position. SMALL(range, 1) and MIN(range) are interchangeable — prefer MIN when k will always be 1, SMALL when you need multiple bottom ranks in adjacent cells. Pair with LARGE for the symmetric top-end case.
SMALL frequently asked questions
6.01Is SMALL(range, 1) the same as MIN(range)?▸
Yes, always. SMALL(range, 1) returns the identical result to MIN(range). Use MIN when you only need the absolute floor — it’s one argument shorter. Switch to SMALL when you also need the 2nd or 3rd smallest in nearby cells; the consistent SMALL(range, k) pattern reads more clearly than mixing MIN and SMALL.
6.02Can SMALL work with dates?▸
Yes. Excel stores dates as numeric serial numbers, so SMALL ranks them chronologically — the smallest serial is the earliest date. SMALL(dateRange, 2) returns the second-earliest date. Format the result cell as a date (Home → Number → Short Date) to display it correctly. Works identically in Google Sheets.
6.03Why does SMALL return #NUM!?▸
Two causes: (1) k is 0 or negative — must be at least 1. (2) k exceeds the count of numeric values in the array — e.g. k=5 on a range that has only 3 numbers (text cells don’t count). Wrap with =IFERROR(SMALL(…), "—") to return a dash instead.
6.04What does SMALL return on ties?▸
Duplicate values each occupy their own rank. If the two lowest scores are both 73, SMALL(range, 1) and SMALL(range, 2) both return 73. The third rank returns the next distinct higher value. This mirrors LARGE and RANK behaviour on ties.
6.05How do I get the row label for the Nth smallest value?▸
Use INDEX/MATCH: =INDEX(A2:A100, MATCH(SMALL(B2:B100, k), B2:B100, 0)). MATCH returns the first occurrence on ties. In Excel 365 / Google Sheets, =SORTBY(A2:A100, B2:B100, 1) gives the full sorted table in one formula — the INDEX/MATCH approach is most useful for older Excel versions or when you want a single rank without spilling.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.