SMALL Function in Excel, Google Sheets, Airtable & Notion
SMALL returns the k-th smallest number in a range: k = 1 is the minimum, k = 2 the second smallest, and so on. Duplicates count separately.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | SMALL(array, k) |
| Google Sheets | ✔ Built in | SMALL(data, n) |
| Airtable | ✘ Not available | — Airtable formulas cannot sort a list of values, so there is no way to pick the k-th value or a percentile from a rollup. For k = 1 use a rollup with MIN(values). |
| Notion | ≈ Workaround | list.sort().at(k - 1)Sort ascending and pick by position. Notion lists count from 0, so the 2nd smallest is .at(1). Returns empty instead of #NUM! when k is too large. |
| Excel | =SMALL(A2:A7, 2) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =SMALL(A2:A7, 2) | open sheets.new ↗ |
| Airtable | ✘ No equivalent. Airtable formulas cannot sort a list of values, so there is no way to pick the k-th value or a percentile from a rollup. For k = 1 use a rollup with MIN(values). | |
| Notion workaround | [55, 92, 78, 92, 61, 85].sort().at(1) | |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Score 55 92 78 92 61 85
Arguments
| Argument | What it means |
|---|---|
array | The range or list of numbers. |
k | Position from the bottom: 1 = smallest. Must be between 1 and the number of values, or you get #NUM!. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=SMALL(A2:A7, 2) | 61 | Tester data: sorted 55, 61, 78, 85, 92, 92. | |
=SMALL(A2:A7, 1) | 55 | Same as MIN. | |
=AVERAGE(SMALL(A2:A7, {1, 2})) | 58 | Average of the two lowest values. | |
=SMALL(A2:A7, 0) | #NUM! | k starts at 1. |
Examples use Excel / Google Sheets syntax unless noted.
How SMALL works
SMALL is the mirror of LARGE: it sorts the numbers from low to high and returns the one at position k. Use it for the fastest times, the cheapest quotes or the earliest dates (dates are numbers, so SMALL(dates, 2) is the second-earliest). SMALL(range, 1) equals MIN.
Text and empty cells are ignored, duplicates count separately, and k outside 1…count gives #NUM!. A classic use is listing values in order without sorting the source: put =SMALL($A$2:$A$20, ROW()-1) in B2 and fill down. In Excel 365 and Google Sheets, SORT does that in one step.
In Notion, .sort() puts a list in ascending order and .at() picks by a 0-based position, so the k-th smallest is .sort().at(k - 1). Airtable cannot sort inside a formula; only the minimum is available through a rollup with MIN(values).
Frequently Asked Questions
- How do I find the second smallest value ignoring zeros?
- Filter them out first:
=SMALL(FILTER(A2:A20, A2:A20<>0), 2)in Excel 365 or Google Sheets.