LARGE Function

Summary

LARGE returns the k-th largest value in a dataset. Instead of only returning the single highest value like MAX, it lets you ask for a ranked result such as the 2nd, 3rd, or 5th largest value.

That makes it useful for leaderboards, top-performer lists, and threshold checks. If you want more than just the winner, LARGE gives you a much better view of the upper end of the data.

LARGE is useful because ranking questions are often about one of the top values rather than the absolute maximum alone. It helps the workbook pull the 2nd, 3rd, or nth highest result without sorting the data manually.

Syntax

=LARGE(array, k)

array is the set of numbers to rank, and k is the position from the largest. So 1 means largest, 2 means second largest, and so on.

Arguments

• array - [required] The range or array of numeric data to rank.
• k - [required] The position to return from the top of the list.

LARGE vs Other Functions

Using the LARGE Function

The most common use is ranking without sorting the source data. You might want the second highest sale, the third highest score, or the fifth largest order amount. LARGE gives you that number directly, so you do not have to reorder the sheet or build a helper column first.

Microsoft notes a few rules that matter. If the array is empty, LARGE returns #NUM!. The same error appears if k is less than or equal to zero, or if it is greater than the number of data points. Microsoft also points out that if n is the number of data points, then LARGE(array,1) is the largest value and LARGE(array,n) is the smallest value.

Duplicate values are kept in the ranking. If the two highest numbers are both 100, then the first and second ranked values can both be 100. That is normal because LARGE returns the value at the requested position, not a list of unique values.

Example 1 - Find the 2nd largest value

This is the classic runner-up example. The list stays in its original order, and Excel works out which value ranks second from the top without needing you to sort anything manually.

That is useful when you want a ranked answer from live data. You can ask for second place directly instead of rearranging the sheet every time the numbers change.

=LARGE(B1:B10,2) // Returns the second highest value in the range.

Example 2 - Use LARGE as a max function

When k is 1, LARGE returns the very top value in the list. That means it can behave like MAX when you want first place instead of a lower rank.

This is a useful reminder because it shows how LARGE covers both simple and ranked use cases with one function.

=LARGE(B1:B10,1) // Returns the largest value in the range.

Example 3 - Return the 3rd largest result

This example moves past the winner and runner-up and asks for third place. That is useful in ranked summaries, leaderboards, and reports where the top three matter more than just the single best result.

The key point is that the sheet does not need to be sorted for you to get that rank. Excel does the ranking inside the formula.

=LARGE(B1:B10,3) // Returns the third largest value in the range.

Example 4 - Use a deeper rank as a target

A deeper rank like 5th highest can work as a benchmark instead of using only the best-case result. That gives you a target that is still strong, but not based on the single top outlier.

This is a practical way to turn ranking into decision-making. Instead of asking who came first, you can ask what level counts as top-tier performance.

=LARGE(B1:B10,5) // Returns the fifth largest value in the range.

Conclusion Recap

LARGE is helpful when you want a ranked result from the top of a list instead of only the single highest value. In this lesson, that meant finding the first, second, third, or fifth highest result without sorting the sheet.

The key part is the k value. It tells Excel which position to return, so 1 means the top value, 2 means the runner-up, and so on. That makes LARGE useful for leaderboards, top lists, and benchmark checks.

• Main job: LARGE returns the k-th largest value in a dataset.
• k controls the rank: 1 means largest, 2 means second largest, and so on.
• Useful for ranked reporting: It helps when you want more than just the top value.
• Duplicates count: Repeated high values can appear in multiple ranking positions.
• Error case: Excel returns #NUM! if the array is empty or k is outside the valid range.
• Relationship to MAX: LARGE(array,1) gives the same result as MAX.