The Excel LARGE function returns the k-th largest value in a data set — the 1st largest is the maximum, the 2nd largest is the runner-up, and so on. It is the ranking counterpart of SMALL.
Syntax
| Argument | Description | |
|---|---|---|
array | Required | The array or range of data whose k-th largest value you want. |
k | Required | The position from the largest. 1 returns the maximum, 2 the second largest, and so on. |
How to use it
LARGE ranks the values from highest to lowest and returns the one at position k.
Duplicates count as separate positions — in {8,8,5} both the 1st and 2nd largest are 8. To list the top N at once, use =LARGE(range, ROW(1:N)) as a spilling array, or sum the top three with =SUM(LARGE(range,{1,2,3})).
Watch the k range: if k is less than 1 or greater than the number of values, LARGE returns #NUM!.
Try it: interactive demo
Pick a LARGE example to see the formula and its result.
Practice workbook
Frequently asked questions
How is LARGE different from MAX?
MAX only ever returns the single highest value. LARGE takes a position k, so LARGE(range,1) equals MAX, but you can also ask for the 2nd, 3rd, or k-th largest.How does LARGE handle duplicate values?
How do I sum the top 3 values?
=SUM(LARGE(range,{1,2,3})) adds the three largest values in one formula.Why does LARGE return #NUM!?
k is out of range — less than 1 or larger than the count of numbers in the array — or the array contains no numbers.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class