The LARGE, SMALL guide
=LARGE(range, 2) returns the second largest number in a range, and =SMALL(range, 2) the second smallest. Put k in a cell next to the formula and one formula, filled down, gives a top 3 or a bottom 3.
The syntax
=LARGE(array, k)
=SMALL(array, k)
k = 1 is the largest (or smallest), 2 the next, and so on. LARGE(range, 1) is the same as MAX and SMALL(range, 1) the same as MIN. Empty cells and text are skipped.
A top 3 that fills down
Type 1, 2 and 3 in a rank column, then write =LARGE($B$2:$B$11, D2) next to the 1 and fill it down. k follows the rank, and the locked range stays on the sales. Without the $ signs the range slides down a row at a time and the first reps drop out of it.
Writing the k into each formula, LARGE($B$2:$B$11, 1), …, 2), …, 3), also works, but filling it down copies the 1 into every row and shows the top value three times.
Ties take two places
If two reps both sold 52,900 and that is the second highest figure, LARGE returns 52,900 for k = 2 and again for k = 3. That is correct: they are two of the top three. A distinct top 3 needs the duplicates removed first.
The names that go with the numbers
To show who made each sale, look the value up: =INDEX($A$2:$A$11, MATCH(E2, $B$2:$B$11, 0)) or XLOOKUP. With a tie, both lookups find the first rep each time, so the same name shows twice.
Common mistakes
- k stuck at 1. Every row shows the largest value.
- LARGE and SMALL swapped. The top 3 comes out as the bottom 3.
- A k bigger than the count.
LARGE(range, 11)on ten numbers returns #NUM!.
For just the largest and smallest: MIN and MAX.
FAQ
- How do I find the top 3 values in Excel?
- Use LARGE with k = 1, 2 and 3: =LARGE($B$2:$B$11,1) and so on. Put 1, 2, 3 in a column and point k at it to fill one formula down.
- What is the difference between LARGE and MAX?
- MAX returns only the largest number. LARGE returns the k-th largest, so LARGE(range,1) equals MAX and LARGE(range,2) is the second largest.
- Why does LARGE show the same number twice?
- Two cells hold that value, so it takes two places in the ranking. That is how ties work in LARGE and SMALL.
- Why does LARGE return #NUM!?
- k is bigger than the number of values in the range, or less than 1.



































































