Excel LARGE Function

The Excel LARGE function returns the k-th largest number in an array or range of data.

Syntax

=LARGE(array, k)

Arguments

Return Value

The LARGE function returns a numeric value.

Function Notes

• LARGE ignores empty cells, text, and logical values.
• If array contains any errors, the first error will be returned.
• LARGE returns the #NUM! error value if:
  • array contains less numeric values than k;
  • k ≤ 0.
• In the case that an array contains n values, LARGE(array,1) will return the maximun value from the array, and LARGE(array,n) will return the minimum value.

Example

To get the 5th largest number from the table as shown below, please copy or enter the formula below in a blank cell, and press Enter to get the result:

=LARGE(B3:C10,5)

Example to retrieve top 5

To get the top 5 numbers from the table, you can add a rank column next to the result column for convenience as shown below. So that you can reference the rank that you want to retrieve its corresponding number, instead of entering the k argument (rank) yourself. After that, please copy or enter the formula below in the top cell (cell F4), and press Enter to get the result:

=LARGE(B3:C10,E4)

√ Note: After entering the formula, drag the fill handle down to apply the formula to the below cells.

Related functions