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
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | LARGE(array, k) |
| Google Sheets | ✔ Built in | LARGE(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 | ≈ Workaround | list.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. |
| 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
| Argument | What it means |
|---|---|
array | The range or list of numbers. |
k | Position from the top: 1 = largest. Must be between 1 and the number of values, or you get #NUM!. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=LARGE(A2:A7, 2) | 92 | Tester 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})) | 269 | Sum 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.