The Excel LARGE function, explained interactively.
LARGE returns the k-th largest number in a range. Think of it as a generalised MAX: LARGE(range, 1) is identical to MAX(range), but you can ask for the 2nd, 3rd, or any rank you like. The catch — and the one error almost everyone hits — is that k must be at least 1 and no larger than the count of numeric values in the array.
How to use LARGE
- Type
=LARGE(and select the array — the range of numbers you want to rank. Text and blanks in the range are silently skipped. - Add a comma, then type
k— the rank you want.k=1returns the largest (same as MAX),k=2the second-largest, and so on. - Close the parenthesis. If
kis 0, negative, or larger than the count of numeric cells, LARGE returns#NUM!. Wrap withIFERRORto suppress it gracefully. - For a top-N list, put LARGE in consecutive rows with
k=1,k=2,k=3— or drivekfrom a helper column so the formula can be copied down.
Use the + / − buttons on the k card to step through ranks and watch the highlighted row and result update live — that’s the core mechanic of LARGE.
| A | B | D | |
|---|---|---|---|
| 1 | Student | Score | LARGE result |
| 2 | Ava | 87 | 95 |
| 3 | Ben | 92 | |
| 4 | Chloe | 78 | |
| 5 | Dev | 95 | |
| 6 | Elena | 88 | |
| 7 | Farid | 73 | |
| 8 | Grace | 91 |
=LARGE(B2:B8, 1). LARGE with k=1 returns 95 — Dev holds the #1 spot. LARGE(range, 1) = MAX(range).LARGE syntax and arguments
Two required arguments. See Microsoft’s official LARGE 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. Logical values passed as literals (e.g. TRUE) are coerced to 1/0.k=1 is the largest value, k=2 is the second-largest, 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.LARGE examples
Four patterns covering the most common real-world LARGE uses.
Example 1: Top 3 scores from a list
The canonical use case — populate a podium or a trophy board without manually sorting.
Put each formula in a consecutive row with k driven from a helper column (1, 2, 3…) and the pattern copies down automatically. On ties, both tied values occupy their respective rank positions — so two scores of 95 would both appear in the top-3 list.
Example 2: Second-highest sales commission
A practical business case — reward the runner-up without sorting the full table.
Returns the second-highest commission in the column. Changing any commission in column B instantly updates the result — no manual re-sort needed. Pair with =INDEX(A2:A8, MATCH(LARGE(B2:B8,2), B2:B8, 0)) to also surface the rep’s name.
Example 3: 90th-percentile approximation
Use LARGE with a dynamic k derived from COUNT to approximate a percentile cut-off.
COUNT * 0.1 gives the number of values in the top 10%, which becomes k. MAX(1, …) stops k from hitting 0 on tiny datasets. For precise statistical percentiles, use Excel’s PERCENTILE.INC function instead — but this LARGE approach is handy when you specifically want the actual ranked value rather than an interpolated one.
Example 4: LARGE inside IF — flag the top scorer
Embed LARGE in another formula to mark or act on the row whose value equals the Nth largest.
The absolute reference $B$2:$B$8 locks the range so the formula can be copied down column D without drift. On ties every tied row gets the flag — useful for highlighting all co-winners. Swap 1 for any k to flag that rank instead.
Common LARGE 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 4-value range is equally invalid.
Check =COUNT(array) to confirm how many numeric cells exist. Wrap with =IFERROR(LARGE(…), "—") 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 (e.g. the cell referenced for k is empty and returns a string), LARGE propagates #VALUE!.
Ensure the cell or formula providing k is numeric. Use =ISNUMBER(k_cell) to diagnose. Decimal values are silently truncated (e.g. 2.9 → 2), but text is not coerced.
Text and logical values are silently ignored
Behaviour: LARGE skips non-numeric cells in range references (same as MAX / MIN). A range with 10 cells where 3 are text has an effective size of 7 for k-validation — so k=8 gives #NUM!.
Use =COUNT(range) rather than =COUNTA(range) to measure the true numeric size of the array before choosing k.
Ties — the same value occupies multiple ranks
Behaviour: if the two largest values are both 95, LARGE(range, 1) and LARGE(range, 2) both return 95. The third slot returns the next distinct value below 95. This matches Excel’s RANK behaviour for ties.
To get distinct top values (no repeats), filter the ranked list with UNIQUE (Excel 365 / Google Sheets) before applying LARGE, or use LARGE(IF(COUNTIF(spillRange, range)=0, range), k) as an array formula.
LARGE vs MAX, SMALL & RANK
Four functions that answer ranking questions — pick by what output you need and whether you want a value or a position.
| Function | Returns | k / n argument | Typical use |
|---|---|---|---|
| LARGE | Nth largest value | k (position from top) | Top-N lists, podium scores, percentile cut-offs |
| MAX | Largest value (= LARGE k=1) | None | Single highest value — simpler when only #1 matters |
| SMALL | Nth smallest value | k (position from bottom) | Bottom-N lists, 2nd earliest date, SLA worst cases |
| RANK | Ordinal position of a value | order (0 = desc, 1 = asc) | Leaderboard column — "what rank is this specific cell?" |
Rule of thumb: use LARGE when you know the rank and want the value; use RANK when you have the value and want to know its rank. LARGE(range, 1) and MAX(range) are interchangeable — prefer MAX when k will always be 1, LARGE when you need multiple ranks in adjacent cells.
LARGE frequently asked questions
6.01Is LARGE(range, 1) the same as MAX(range)?▸
Yes, always. LARGE(range, 1) returns the identical result to MAX(range). Use MAX when you only need the single top value — it’s one argument shorter and a touch more readable. Switch to LARGE when you also need the 2nd or 3rd largest in nearby cells; the consistent pattern reads more clearly than mixing MAX and LARGE.
6.02What does LARGE return on ties?▸
Duplicate values each occupy their own rank. If the two highest scores are both 95, LARGE(range, 1) and LARGE(range, 2) both return 95. The third rank then returns the next distinct lower value. This mirrors how RANK handles ties — both functions count duplicate occurrences as separate ranked positions.
6.03Why does LARGE 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 4 numbers. Text and logical values don’t count toward that limit. Wrap with =IFERROR(LARGE(…), "—") to return a dash instead.
6.04Does LARGE count text or logical values?▸
No. LARGE silently skips text and logical values (TRUE / FALSE) in cell references — exactly like MAX. Only numeric cells count toward both the ranking and the effective array size for k-validation. A 10-cell range where 3 cells are text has an effective size of 7; k=8 gives #NUM! even though 10 cells exist.
6.05How do I get the top N values and their row labels?▸
Use LARGE to get each value, then INDEX/MATCH to retrieve the label: =INDEX(A2:A100, MATCH(LARGE(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, making the LARGE + INDEX/MATCH approach mostly needed for older Excel versions or when you want just a single rank.
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.