Spreadsheet formulas explained, side by side

LARGE Function in Excel, Google Sheets, Airtable & Notion

LARGE returns the k-th largest number in a range: k = 1 is the maximum, k = 2 the second largest, and so on. Duplicates count separately.

Syntax in each app

AppSupportSyntax & notes
Excel✔ Built inLARGE(array, k)
Google Sheets✔ Built inLARGE(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 MAX(values); for a top-N list, sort a view and limit it, or use an interface.
Notion≈ Workaroundlist.sort().reverse().at(k - 1)
Sort descending and pick by position. Notion lists count from 0, so the 2nd largest is .at(1). Returns empty instead of #NUM! when k is too large.
Try LARGE with your own values
One value per line, or separate with commas
Result (as Excel & Google Sheets return it)
92
Your formula, ready to paste
Excel=LARGE(A2:A7, 2) open excel.new ↗
Google Sheets=LARGE(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 MAX(values); for a top-N list, sort a view and limit it, or use an interface.
Notion
workaround
[55, 92, 78, 92, 61, 85].sort().reverse().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 top: 1 = largest. Must be between 1 and the number of values, or you get #NUM!.

Examples

FormulaResultWhyCopy
=LARGE(A2:A7, 2)92Tester data: 92 appears twice, so it is both the 1st and the 2nd largest.
=LARGE(A2:A7, 3)85
=SUM(LARGE(A2:A7, {1, 2, 3}))269Sum of the top three: 92 + 92 + 85.
=LARGE(A2:A7, 7)#NUM!There are only 6 values.

Examples use Excel / Google Sheets syntax unless noted.

How LARGE works

LARGE is how you pull the runners-up: the second-highest sale, the top three scores, the third-biggest expense. LARGE(range, 1) is the same as MAX. Text and blanks in the range are ignored. Duplicates are not merged, so with scores 92, 92 and 85 the 2nd largest is 92, not 85. If you want distinct values, apply LARGE to UNIQUE(range) in Excel 365 or Google Sheets.

To list a top N, put 1, 2, 3… in a helper column and refer to it, or pass an array constant: =LARGE(A2:A100, {1,2,3}) spills three values in Excel 365. To get the names that go with the top values, use SORT or SORTBY with TAKE, or INDEX/MATCH on the LARGE result (careful with ties). The mirror image is SMALL.

Notion has no LARGE, but sorting a list and picking a position does the same job: prop("Scores").sort().reverse().at(1) for the second largest. Remember that .at() starts counting at 0. Airtable formulas cannot sort values, so only the top value (with MAX) is available in a rollup.

Frequently Asked Questions

How do I get the second largest distinct value?
Remove duplicates first: =LARGE(UNIQUE(A2:A100), 2) in Excel 365 or Google Sheets.
Why does LARGE return #NUM!?
k is less than 1 or greater than the number of numeric values in the range.

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.