Spreadsheet formulas explained, side by side

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

AppSupportSyntax & notes
Excel✔ Built inSMALL(array, k)
Google Sheets✔ Built inSMALL(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≈ Workaroundlist.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.
Try SMALL with your own values
One value per line, or separate with commas
Result (as Excel & Google Sheets return it)
61
Your formula, ready to paste
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

ArgumentWhat it means
arrayThe range or list of numbers.
kPosition from the bottom: 1 = smallest. Must be between 1 and the number of values, or you get #NUM!.

Examples

FormulaResultWhyCopy
=SMALL(A2:A7, 2)61Tester data: sorted 55, 61, 78, 85, 92, 92.
=SMALL(A2:A7, 1)55Same as MIN.
=AVERAGE(SMALL(A2:A7, {1, 2}))58Average 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.

Part of the Statistics & Counting functions. Results in the tester follow Excel and Google Sheets behaviour; always double-check important work in your own file.