Math function
Excel LARGE function
LARGE returns the nth biggest number in a range. LARGE(range,1) is the same as MAX, LARGE(range,2) is the runner-up, and so on. Use it to build a top 3 or top 5 list.
=LARGE(array, k)Arguments
array- The range of numbers to rank.
k- Which position you want, counting from the largest. 1 is the biggest.
LARGE examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | |
|---|---|---|
| 1 | Rep | Q3 sales |
| 2 | Maria | 48200 |
| 3 | Tom | 39750 |
| 4 | Priya | 52900 |
| 5 | Jake | 41300 |
| 6 | Lena | 39750 |
| 7 | Omar | 45600 |
Result: 52900
With k = 1 you get the top value, same as MAX.
Result: 48200
The second-best quarter in the team.
Result: 146700
Add the top three results together for a podium total.
Result: #NUM!
There are only six numbers, so asking for the 7th largest returns #NUM!.
How to use LARGE
- Click an empty cell.
- Type =LARGE( and select the numbers.
- Type a comma and the position you want, like 2.
- Close the bracket and press Enter.
Common LARGE mistakes
k is bigger than the number of values
LARGE returns #NUM! if k is less than 1 or more than the count of numbers. Check with COUNT(range).
Expecting duplicates to be skipped
Ties are counted separately. If two reps both have 39750, LARGE(range,5) and LARGE(range,6) both return it.
Hard-coding k in a top 5 list
Use ROWS($A$1:A1) or SEQUENCE(5) for k so the position changes as you copy down.
LARGE FAQ
How do I find the second largest value in Excel?
Use =LARGE(range,2). Change the 2 to 3, 4 and so on for the next positions.
How do I get the top 5 values in Excel?
Put =LARGE($B$2:$B$20,ROWS($A$1:A1)) in a cell and copy it down five rows. In Excel 365 =LARGE(B2:B20,SEQUENCE(5)) spills all five at once.
What is the difference between LARGE and MAX?
MAX only returns the single biggest value. LARGE can return any position, so LARGE(range,1) equals MAX(range).
Related functions
Updated