How to Use LARGE, SMALL, and RANK in Excel
Introduction
MAX and MIN give you the single highest and lowest values in a range. But what if you need the second highest, or the third lowest? LARGE and SMALL let you pull the nth largest or smallest value from a range. RANK goes the other direction, telling you where a specific value falls in the list.
The Dataset
Suppose you have a list of sales reps and their quarterly revenue:
| A | B | |
|---|---|---|
| 3 | Rep | Revenue |
| 4 | Sarah | 8200 |
| 5 | James | 5400 |
| 6 | Marcus | 9100 |
| 7 | Rachel | 6800 |
| 8 | David | 7500 |
| 9 | Olivia | 4300 |
LARGE
LARGE returns the nth largest value in a range, so you can go beyond just the maximum and find the second, third, or any position from the top. The syntax is:
=LARGE(array, k)
- array: The range of values.
- k: Which rank to return (1 for the largest, 2 for second largest, and so on).
To find the highest revenue, set k to 1:
=LARGE(B4:B9, 1)
The result is 9100 (Marcus), which is the same result you'd get from =MAX(B4:B9).
By changing k, you can step down through the list:
| k | Formula | Result | Rep |
|---|---|---|---|
| 1 | =LARGE(B4:B9, 1) | 9100 | Marcus |
| 2 | =LARGE(B4:B9, 2) | 8200 | Sarah |
| 3 | =LARGE(B4:B9, 3) | 7500 | David |
The top three performers are Marcus, Sarah, and David. If you need to find values from the bottom instead, that's where SMALL comes in.
SMALL
SMALL works the same way as LARGE, but counts from the bottom of the range instead of the top. The syntax is:
=SMALL(array, k)
To find the lowest revenue, set k to 1:
=SMALL(B4:B9, 1)
The result is 4300 (Olivia), which matches what =MIN(B4:B9) would return.
| k | Formula | Result | Rep |
|---|---|---|---|
| 1 | =SMALL(B4:B9, 1) | 4300 | Olivia |
| 2 | =SMALL(B4:B9, 2) | 5400 | James |
| 3 | =SMALL(B4:B9, 3) | 6800 | Rachel |
RANK
While LARGE and SMALL let you find values by position, RANK works in reverse. Give it a specific value, and it tells you where that value falls in the list. The syntax is:
=RANK(number, ref, [order])
- number: The value to rank.
- ref: The range of values to rank against.
- order: 0 or omitted for descending (largest = 1). 1 for ascending (smallest = 1).
To rank Sarah's revenue:
=RANK(B4, B4:B9)
Sarah's revenue of 8200 is the second highest in the range, so the result is 2.
Applying the same formula to each rep gives you the full ranking:
| Rep | Revenue | Rank |
|---|---|---|
| Sarah | 8200 | 2 |
| James | 5400 | 5 |
| Marcus | 9100 | 1 |
| Rachel | 6800 | 4 |
| David | 7500 | 3 |
| Olivia | 4300 | 6 |
Handling Ties
When two reps have the same revenue, RANK assigns both the same position and skips the next one. For example, if James and David both had 7500:
| Rep | Revenue | Rank |
|---|---|---|
| Sarah | 8200 | 2 |
| James | 7500 | 3 |
| Marcus | 9100 | 1 |
| Rachel | 6800 | 5 |
| David | 7500 | 3 |
| Olivia | 4300 | 6 |
Both James and David get rank 3, and rank 4 is skipped entirely, so Rachel drops to 5.
If you want ties to share the average rank instead, use RANK.AVG:
=RANK.AVG(B5, B4:B9)
James and David would both get 3.5 (the average of positions 3 and 4), while everyone else keeps the same rank they had before.
Key takeaway: RANK skips a position after a tie (3, 3, 5). RANK.AVG averages the tied positions instead (3.5, 3.5, 5). Use RANK.AVG when you don't want gaps after ties.
Conclusion
LARGE and SMALL let you find the nth highest or lowest value in a range, while RANK shows you exactly where a specific value falls. If ties are a concern, RANK.AVG distributes the tied positions evenly instead of leaving gaps.
If you need to find the highest or lowest value based on conditions, check out our guide on MAXIFS and MINIFS. To sort your data directly in formulas, see our guide on SORT and SORTBY.
Level up your Excel skills
Bite-sized lessons, drills, and daily challenges to build real spreadsheet skills.