Excel LARGE Function

LARGE returns the nth largest number in a data set. Ask it for the 1st largest and it behaves like MAX. Ask for the 2nd, 3rd, or 10th largest and it hands you exactly that, no sorting required. The one thing that trips people up: if your data has ties, LARGE treats duplicate values as separate ranks, so two identical scores can occupy both the 1st and 2nd position.

Syntax

=LARGE(array, k)

Parameter	Required	Description
`array`	Yes	The range or list of numbers to rank. LARGE ignores text and blank cells automatically.
`k`	Yes	The position from the top you want returned. `1` is the largest value, `2` is the second largest, and so on.

Basic Example

You track monthly sales totals for a 12-person team in a named range called sales. To find the second-highest sales figure of the year:

=LARGE(sales, 2)

// sales = the range of 12 monthly totals
// 2     = return the value in the 2nd position from the top

LARGE scans all 12 values, ranks them from highest to lowest, and returns whatever sits in position 2. If January was the top month at $84,000 and March came in second at $79,500, this formula returns 79500. You never have to sort the data yourself.

How LARGE Works

Ties count as separate ranks

If two values in your range are identical, LARGE still assigns them separate positions. Given the scores 95, 95, and 88, LARGE(scores, 1) returns 95 and LARGE(scores, 2) also returns 95, not 88. The third-highest position is where 88 finally shows up.

k must fall within the count of numbers

If k is zero, negative, or larger than the number of values in your array, LARGE has nothing to return. A 10-row list with k set to 15 will fail every time.

It ignores text and blank cells

You don't need to clean your data before running LARGE. Excel skips text entries, empty cells, and logical values automatically when it counts positions.

It doesn't require sorted data

Unlike a manual sort-and-scroll approach, LARGE works directly on unsorted ranges. Add new rows to the bottom of a growing sales log and the formula still ranks correctly without you touching the sort order.

Common Use Cases

Build a top 5 leaderboard

You want the five highest order values from a named range called order_values, without building a helper column. This one requires Excel 365 or 2021 — SEQUENCE isn't available in earlier versions.

=LARGE(order_values, SEQUENCE(5))

// SEQUENCE(5) generates 1, 2, 3, 4, 5
// LARGE returns all five ranks at once as a spilled array

This one formula spills five results down the column: the largest order, then the second-largest, and so on through the fifth.

Match the name tied to the largest value

You need the name of the sales rep who posted the top total, not just the number.

=INDEX(rep_names, MATCH(LARGE(sales_totals, 1), sales_totals, 0))

// LARGE(sales_totals, 1) finds the top total
// MATCH locates which row that total sits in
// INDEX pulls the rep name from that same row

⚠

If two reps tie for the top total, MATCH always returns the row for the first match it finds. That means both LARGE(...,1) and LARGE(...,2) will point back to the same name, even though the second-highest value technically belongs to a different rep. Check for ties before trusting a name lookup like this.

Rank distinct values only, ignoring duplicates

Your exam scores include repeats, and you want the second-highest distinct score, not the second position overall. UNIQUE is Excel 365/2021 only — on older versions you'd need to remove duplicates manually first.

=LARGE(UNIQUE(scores), 2)

// UNIQUE strips out duplicate scores first
// LARGE then ranks within that shorter, distinct list

If three students scored 95 and one scored 88, LARGE(scores, 2) still returns 95. LARGE(UNIQUE(scores), 2) correctly returns 88, since it's ranking distinct values, not raw rows.

Nth largest value that meets a condition

You want the second-highest order value, but only from the East region. This combination needs FILTER, another Excel 365/2021-only function.

=LARGE(FILTER(order_values, region="East"), 2)

// FILTER narrows the array down to East region orders only
// LARGE then ranks within that filtered subset

FILTER does the narrowing, LARGE does the ranking. Change the criteria in FILTER and the ranked result updates automatically.

Handling Errors

LARGE returns #NUM! when it can't find a valid position to return. This happens more often than you'd expect once a formula supplies k instead of you typing it directly.

Common causes of #NUM!:

k is zero, negative, or not a whole number
k is greater than the count of numeric values in the array
The array is empty or contains no numbers at all

=IFERROR(LARGE(sales_totals, k_cell), "Not enough data")

✓

If k comes from a growing or filtered list, add a check like =IF(COUNT(range)>=k, LARGE(range,k), "N/A"). It's more explicit than wrapping everything in IFERROR and tells you exactly why the formula came up short.

Notes & Gotchas

Why does LARGE return #NUM!?

The most common cause is k exceeding the number of values in your range. If your list only has 8 numbers and you ask for the 10th largest, there's nothing in that position. A negative or zero k triggers the same error, and so does an array with no numeric values at all.

Does LARGE count duplicate values as separate ranks?

Yes. If a value appears three times, it occupies three consecutive ranks, not one. This is the single biggest source of confusion with LARGE. Wrap your array in UNIQUE() first if you need to rank distinct values instead of raw entries.

Does LARGE work with dates?

Yes. Excel stores dates as serial numbers behind the scenes, so LARGE(dates, 1) returns the most recent date in a range as its underlying serial value. Format the result cell as a date to display it correctly, or the answer will show as a five-digit number instead.

What happens if the range contains text or blank cells?

LARGE skips them automatically. You don't need to filter them out first. This is different from a function like SUM, which handles text differently depending on context. LARGE simply excludes anything that isn't a number when it counts positions.

How do I return the top N values as a list instead of one at a time?

Feed LARGE an array of positions instead of a single number. =LARGE(range, SEQUENCE(5)) spills the top 5 values down a column in one formula, ranked from highest to lowest, with no need to copy the formula five separate times. This requires Excel 365 or 2021; on older versions, copy the LARGE formula down manually with k set to 1, 2, 3, and so on.

Related Functions

Function	Use this when...
`MAX`	You only need the single largest value and don't care about 2nd or 3rd place.
`RANK`	You want to label every value in a list with its rank, rather than pull out just one value.
`FILTER`	You need to narrow data down by a condition before ranking it with LARGE.
`UNIQUE`	Your data has duplicate values and you want to rank distinct entries only.