LARGE, SMALL and RANK.EQ: top lists and rankings in Excel
LARGE returns the second, third or k-th largest number in a list, SMALL does the same from the bottom, and RANK.EQ tells you which place a number takes. Together they build top 5 lists and leaderboards, from sales figures to supplier lead times.
In short: =LARGE(B2:B8,2) returns the second highest value and =SMALL(B2:B8,2) the second lowest. =RANK.EQ(B2,$B$2:$B$8) returns the position of B2 in the list, where the largest value is rank 1.
Try it now
MAX tells you who sold the most. For the quarterly review you also want to know the runner-up figure. Click the yellow cell and write the formula.
Task For the quarterly review you need the second highest revenue on the team. In E2, write a formula that returns it from B2:B8.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Sales rep | Q3 revenue $ | |||
| 2 | Jana Weber | 48200 | 2nd place | ||
| 3 | Murat Kaya | 61750 | |||
| 4 | Lena Hoffmann | 39900 | |||
| 5 | Tom Schulz | 72400 | |||
| 6 | Sara Klein | 55300 | |||
| 7 | Ali Demir | 58900 | |||
| 8 | Mia Wagner | 44100 |
How the three functions work
| Function | Arguments | Returns |
|---|---|---|
| LARGE | (array,k) | The k-th largest value. k = 1 is the same as MAX. |
| SMALL | (array,k) | The k-th smallest value. k = 1 is the same as MIN. |
| RANK.EQ | (number,ref,[order]) | The position of the number in ref. With order left out or 0 the largest value ranks 1, with 1 the smallest does. |
They answer opposite questions. LARGE and SMALL ask which value sits in position k. RANK.EQ asks which position a given value holds. All three ignore blank cells and text in the range.
Rank a list with RANK.EQ
To rank a whole team, write the formula once in the first row and fill it down. That only works if the list is locked with $ signs. Without them the range shifts one row per row, and every person gets compared with a shorter list. The page on absolute references explains the dollar signs in detail.
Task Build a ranking: in C2, use RANK.EQ to return the rank of the revenue in B2, with the highest revenue ranked 1. The formula is filled down to C8.
| A | B | C | |
|---|---|---|---|
| 1 | Sales rep | October revenue $ | Rank |
| 2 | Paul Becker | 18450 | |
| 3 | Eva Richter | 22900 | |
| 4 | Noah Wolf | 15300 | |
| 5 | Sara Klein | 27100 | |
| 6 | Murat Kaya | 19800 | |
| 7 | Lena Hoffmann | 24650 | |
| 8 | Tom Schulz | 16900 |
For times, costs or error rates, lower is better. Add 1 as the third argument and the smallest value gets rank 1: =RANK.EQ(B2,$B$2:$B$8,1).
Build a top 3 or top 5 with LARGE and SMALL
The easiest way to list the top values is a helper column holding the places 1, 2, 3. Next to it, the place number becomes k. The formula is the same in every row, only k changes as you fill down:
Task Purchasing wants to see the three shortest lead times. In E2, write a formula with SMALL that returns the shortest lead time from B2:B9. The position is in D2. The formula is filled down to E4 and should show places 2 and 3 there.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Supplier | Lead time days | Place | Lead time | |
| 2 | Nordpack | 6 | 1 | ||
| 3 | Kontor Fischer | 3 | 2 | ||
| 4 | Elbe Office | 9 | 3 | ||
| 5 | Tiedemann | 4 | |||
| 6 | Mayer Logistik | 12 | |||
| 7 | Rheinbedarf | 2 | |||
| 8 | Schäfer Druck | 7 | |||
| 9 | Alpina Supply | 5 |
Many spreadsheets use =LARGE($B$2:$B$9,ROW(A1)) instead. ROW(A1) is 1 and turns into ROW(A2), which is 2, when filled down. That saves the helper column but is harder to read. Our exercises use the helper column. For a top 5, just extend the places to 5.
Who is in first place?
LARGE returns the number, not the name next to it. To get the name, look up that exact number, for example with XLOOKUP: =XLOOKUP(LARGE($B$2:$B$9,1),$B$2:$B$9,$A$2:$A$9). If two people share the same figure, the lookup always finds the first one.
Ties and duplicate values
RANK.EQ gives equal numbers the same rank. If two sales reps share the top revenue, both are rank 1 and the next one is rank 3. There is no rank 2, which is how sports tables work too.
RANK.AVG averages the tied positions instead: two values sharing places 1 and 2 both get 1.5. LARGE counts duplicates separately, so with 900, 900 and 700, LARGE(…,2) is also 900.
Common mistakes
#NUM! from LARGE or SMALL
k is larger than the number of values or smaller than 1. With six sales figures there is no seventh largest. Check the range, and check for numbers stored as text, which are not counted.
#N/A from RANK.EQ
The number in the first argument does not appear in ref. Usually the range slid down while filling because the dollar signs are missing.
The ranking is only right at the top
The first rows look fine, then several people further down are suddenly rank 1. Same cause: without an absolute reference the last row only compares itself with itself.
LARGE and RANK in German Excel
German Excel uses different names and separates arguments with semicolons instead of commas:
| English | German |
|---|---|
| LARGE | KGRÖSSTE |
| SMALL | KKLEINSTE |
| RANK.EQ | RANG.GLEICH |
| RANK.AVG | RANG.MITTELW |
| RANK | RANG |
So =LARGE(B2:B8,2) becomes =KGRÖSSTE(B2:B8;2). Our exercises accept both.
Which Excel versions have these functions?
LARGE and SMALL are available in every version of Excel. RANK.EQ and RANK.AVG were added in Excel 2010 and are in every current version since, including Excel for Mac. The older RANK function gives the same results as RANK.EQ, but Microsoft lists it as a compatibility function for older workbooks. Use RANK.EQ in new sheets.
With Microsoft 365, Excel 2021 or Excel 2024 you can also produce a sorted leaderboard in one go: =SORT(A2:B9,2,-1) sorts the table by revenue, highest first. See FILTER, SORT and UNIQUE for more.
FAQ
How do I find the second highest value in Excel?
Use =LARGE(B2:B20,2). Change 2 to 3 for the third highest. For the second lowest, use SMALL.
What is the difference between RANK and RANK.EQ?
None in the result. RANK.EQ is the newer name that makes clear ties get the same rank. RANK still exists so older files keep working.
How do I rank without duplicate ranks?
Add the number of identical values above the current row: =RANK.EQ(B2,$B$2:$B$9)+COUNTIF($B$2:B2,B2)-1. The first of two tied values keeps its place and the second moves down one.
Can I get the top 5 for one department only?
In Excel 2021 and Microsoft 365, narrow the range with FILTER first: =LARGE(FILTER(C2:C50,B2:B50="Sales"),1) returns first place, and k from 2 to 5 gives the rest. For whole rows, combine FILTER and SORT.