RANK Function in Excel: Order, Ties and Top N
· 8 min read
To rank a number against a list, use RANK.EQ with the value, the whole list locked with $ signs, and an optional order:
=RANK.EQ(number, ref, [order])Leave the order out (or use 0) and the largest value gets rank 1. Use 1 and the smallest value gets rank 1. Equal values share the same rank, and the next rank is skipped. That is the whole RANK function Excel uses; the rest of this guide is about ties, filling it down safely and getting a top N list out of it.
RANK.EQ and the older RANK give identical results. RANK is kept for compatibility with files from Excel 2007 and earlier, so use RANK.EQ in new work, but do not "fix" a working RANK.
The example table
Q3 revenue and average reply time for eight sales reps. Two reps tie on revenue. Columns E to I hold the formulas from this guide, including a broken one from an AI assistant.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Q3 revenue | Reply hours | Revenue rank | Reply rank | Unique rank | Rank in region | AI's rank |
| 2 | Ana Ruiz | North | 48,200 | 3.5 | 5 | 4 | 5 | 2 | 5 |
| 3 | Ben Okafor | South | 61,750 | 5.0 | 1 | 6 | 1 | 1 | 1 |
| 4 | Chloe Park | East | 39,900 | 2.0 | 7 | 1 | 7 | 1 | 5 |
| 5 | Dev Mehta | West | 61,750 | 4.5 | 1 | 5 | 2 | 1 | 1 |
| 6 | Ella Novak | North | 52,300 | 6.0 | 4 | 7 | 4 | 1 | 2 |
| 7 | Femi Adeyemi | East | 28,400 | 2.0 | 8 | 1 | 8 | 2 | 3 |
| 8 | Grace Liu | South | 44,100 | 3.0 | 6 | 3 | 6 | 2 | 2 |
| 9 | Hugo Brandt | West | 57,600 | 7.5 | 3 | 8 | 3 | 2 | 1 |
Descending: biggest value is rank 1
Column E ranks revenue with no order argument:
=RANK.EQ(C2, $C$2:$C$9)Filled down, it gives Ana 5, Ben 1, Chloe 7, Dev 1, Ella 4, Femi 8, Grace 6 and Hugo 3.
The first argument moves with each row (C2, C3, C4...). The list must not move, which is why it has $ signs. Every rep has to be compared with the same eight numbers.
Ascending: smallest value is rank 1
For reply time, lower is better, so the fastest rep should be first. Add 1 as the third argument (column F):
=RANK.EQ(D2, $D$2:$D$9, 1)Chloe and Femi both reply in 2 hours and share rank 1. Grace (3 hours) is 3, and Hugo, the slowest at 7.5 hours, is 8.
It is easy to forget the order argument and get the list upside down. Before you share a ranking, check the extreme: does the best performer have rank 1?
How RANK.EQ handles ties
Ben and Dev both made 61,750, so both get rank 1, and nobody gets rank 2. Hugo, the next highest, is rank 3. This is called competition ranking ("1, 1, 3"), and it is what sports tables use. It also means the highest rank in the column is always the number of values, here 8, even with ties.
Two related functions you may see:
| Function | Ties get | Example for two equal top values |
|---|---|---|
| RANK.EQ / RANK | The best shared rank | 1, 1, 3 |
| RANK.AVG | The average of the ranks they cover | 1.5, 1.5, 3 |
| COUNTIF trick (below) | Different ranks, first one listed wins | 1, 2, 3 |
RANK.AVG is mostly used in statistics. For a report, RANK.EQ is the usual choice, with a note that equal values share a rank.
Unique ranks: break ties by order
Sometimes every row needs its own number, for example to pull a top 3 list without duplicates. Add a running count of how many times the value has appeared so far (column G):
=RANK.EQ(C2, $C$2:$C$9) + COUNTIF($C$2:C2, C2) - 1$C$2:C2 is a range that grows as you fill down: C2:C2 on row 2, C2:C5 on row 5. The first time 61,750 appears (Ben), COUNTIF returns 1, so nothing is added and Ben keeps rank 1. The second time (Dev), COUNTIF returns 2, so Dev becomes 2. Everyone else is unchanged.
This breaks ties by the order of the rows, which is arbitrary. If the tie-break should mean something, such as "higher revenue first, then faster reply", sort the data that way first or build the second criterion into the formula, and say which rule you used.
Rank within a group
RANK.EQ has no criteria argument, so it cannot rank Ella only against the North reps. COUNTIFS can: count how many values in the same group are bigger, then add 1 (column H):
=COUNTIFS($B$2:$B$9, B2, $C$2:$C$9, ">"&C2) + 1For Ana, it counts North reps with revenue above 48,200. There is one (Ella), so Ana is rank 2 in North. Each region has two reps here, so every rep is either 1 or 2. This version gives the same tie handling as RANK.EQ. To rank smallest first, use "<"&C2 instead.
LARGE and SMALL: the top N values
RANK.EQ tells you the position of each value. LARGE does the opposite: give it a position and it returns the value. =LARGE(range, 1) is the largest, =LARGE(range, 2) the second largest. SMALL works the same way from the bottom.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Q3 revenue | Unique rank | Position | Top revenue | Rep by revenue | Rep by unique rank | Lowest revenue | |
| 2 | Ana Ruiz | 48,200 | 5 | 1 | 61,750 | Ben Okafor | Ben Okafor | 28,400 | |
| 3 | Ben Okafor | 61,750 | 1 | 2 | 61,750 | Ben Okafor | Dev Mehta | 39,900 | |
| 4 | Chloe Park | 39,900 | 7 | 3 | 57,600 | Hugo Brandt | Hugo Brandt | 44,100 | |
| 5 | Dev Mehta | 61,750 | 2 | ||||||
| 6 | Ella Novak | 52,300 | 4 | ||||||
| 7 | Femi Adeyemi | 28,400 | 8 | ||||||
| 8 | Grace Liu | 44,100 | 6 | ||||||
| 9 | Hugo Brandt | 57,600 | 3 |
The positions 1, 2 and 3 are typed in column E, so one formula can be filled down instead of typing 1, 2 and 3 into three different formulas:
=LARGE($B$2:$B$9, E2)The top three revenues are 61,750, 61,750 and 57,600. LARGE does not skip duplicates: two equal values take two positions. The three lowest, from =SMALL($B$2:$B$9, E2), are 28,400, 39,900 and 44,100.
Getting the name next to the value
To show who made the revenue, look the value up with INDEX MATCH (column G):
=INDEX($A$2:$A$9, MATCH(F2, $B$2:$B$9, 0))This returns Ben, Ben, Hugo. MATCH always finds the first 61,750, so Dev never appears. Any top N list built this way is wrong as soon as two values tie, and there is no error to warn you.
The fix is to match on the unique rank instead of the value (column H):
=INDEX($A$2:$A$9, MATCH(E2, $C$2:$C$9, 0))Now the list reads Ben, Dev, Hugo. If INDEX MATCH is new to you, see how to use INDEX MATCH.
In Microsoft 365 you can also sort the whole table with SORT or SORTBY and take the first rows, which handles ties without a helper column. The LARGE approach works in every version.
Common mistakes
The list is not locked. Column I of the first sheet is what an AI assistant often writes:
=RANK.EQ(C2, C2:C9)Filled down, the list shrinks: row 3 compares Ben with C3:C10, row 6 compares Ella with C6:C13. Each rep is only compared with the reps below them, so Chloe climbs from 7 to 5 and Hugo, on the last row, is ranked 1 against himself. The giveaway is too many low ranks: three reps share rank 1, two share rank 2, and nobody is ranked 7 or 8. Add $ signs to the list. Our absolute reference guide explains why ranges slide.
Wrong direction. Ranking costs or reply times without the 1 puts the worst performer first. Always check who is rank 1.
Ranking text or blanks. RANK.EQ ignores text and empty cells in the list. A number stored as text is skipped silently, so a rep can be missing from the ranking. If the highest rank is lower than the number of rows, look for text numbers.
Using a top N built on values when values tie. As shown above, INDEX MATCH on LARGE returns the first match twice. Use a unique rank, or check for ties with COUNTIF before you trust the list.
Rounding hides ties, or creates them. Two values that display as 61,750 may differ by a cent and get different ranks. If ranks look odd, widen the decimals.
To practise ranks on a full year of sales data, try the ranking drills on the practice page.
FAQ
What is the difference between RANK and RANK.EQ?
None in the results. RANK is the older name, kept for compatibility. RANK.EQ was added in Excel 2010 next to RANK.AVG, which averages tied ranks.
How do I rank in ascending order?
Add 1 as the third argument: =RANK.EQ(D2, $D$2:$D$9, 1). The smallest value gets rank 1.
How do I rank without duplicate ranks?
Add a running count: =RANK.EQ(C2, $C$2:$C$9) + COUNTIF($C$2:C2, C2) - 1. Tied values then get consecutive ranks in row order.
How do I rank within a category?
Use COUNTIFS: =COUNTIFS(category_range, B2, value_range, ">"&C2) + 1. It counts the larger values in the same category and adds 1.
How do I get the top 5 values?
Put 1 to 5 in a column and use =LARGE(range, cell_with_position) next to them. Use SMALL for the bottom 5.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Rank South’s customers
- Too many products are number oneAudit
- West’s three biggest accounts
- The “biggest” refunds look tinyAudit