=RANK.EQ(B2,$B$2:$B$7) returns the position of B2 among the values in B2:B7, with the largest value ranked 1. To rank the smallest value first, add 1 as a third argument: =RANK.EQ(B2,$B$2:$B$7,1).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Sales | Rank (high = 1) | Rank (low = 1) |
| 2 | Ana | 4200 | 3 | 4 |
| 3 | Ben | 3100 | 5 | 2 |
| 4 | Cleo | 5600 | 1 | 6 |
| 5 | Dan | 2800 | 6 | 1 |
| 6 | Eve | 4900 | 2 | 5 |
| 7 | Finn | 3700 | 4 | 3 |
Cleo has the highest sales and ranks 1 in column C and 6 in column D. Change Dan's sales to 6000 and every rank updates.
RANK.EQ syntax
=RANK.EQ(number, ref, [order])
number: the value to rank, usually the cell in the same row.ref: the list to rank it in. Make it absolute ($B$2:$B$7) so it stays the same in every row when you fill the formula down.order: leave it out or use0for descending (largest is 1), use1for ascending (smallest is 1).
Text and empty cells in ref are ignored. If number is not in ref at all, RANK.EQ returns #N/A.
RANK.EQ and RANK.AVG arrived in Excel 2010. The old RANK function still works and gives the same result as RANK.EQ; Google Sheets has all three.
Ties: RANK.EQ vs RANK.AVG
When values tie, RANK.EQ gives them all the same rank and skips the ranks they use up, the way sports results do: two people in second place, then fourth. RANK.AVG gives each tied value the average of the positions they share.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Score | RANK.EQ | RANK.AVG | Unique rank |
| 2 | Ana | 95 | 1 | 1 | 1 |
| 3 | Ben | 88 | 2 | 3 | 2 |
| 4 | Cleo | 88 | 2 | 3 | 3 |
| 5 | Dan | 76 | 5 | 5 | 5 |
| 6 | Eve | 88 | 2 | 3 | 4 |
| 7 | Finn | 64 | 6 | 6 | 6 |
Ben, Cleo and Eve tie at 88. RANK.EQ gives all three a 2 and Dan a 5. RANK.AVG gives them 3, the average of positions 2, 3 and 4.
Column E breaks the tie by order in the list: COUNTIF($B$2:B2,B2) counts how many times the score has appeared so far, from the top down to this row (only the end of the range moves as you fill). The first 88 adds 0, the second adds 1, the third adds 2, so every rank from 1 to 6 appears exactly once. That is the version to use when a rank feeds a lookup such as "who is number 3", because a lookup needs each rank to be unique.
Rank within a group
There is no RANKIF. To rank each salesperson only against others in the same region, count how many people in that region sold more, and add 1:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Region | Sales | Rank in region |
| 2 | Ana | North | 4200 | 3 |
| 3 | Ben | South | 3100 | 3 |
| 4 | Cleo | North | 5600 | 1 |
| 5 | Dan | South | 2800 | 4 |
| 6 | Eve | North | 4900 | 2 |
| 7 | Finn | South | 3700 | 1 |
| 8 | Gia | North | 3900 | 4 |
| 9 | Hal | South | 3300 | 2 |
Cleo leads North and Finn leads South. Change Ana's region to South: the North ranks close the gap and South gets a fifth person. For ascending ranks inside a group, use "<"&C2 instead. Ties behave like RANK.EQ: tied values share a rank. More on counting with several conditions is on the COUNTIFS page.
Show the top 3 by rank
A rank column answers "what place is this person"; to answer "who is in first place", look the rank up. With unique ranks in column C, =XLOOKUP(1,C2:C7,A2:A7) returns the name ranked 1 (in Excel 2019 and older, =INDEX(A2:A7,MATCH(1,C2:C7,0))). Without a rank column, =LARGE(B2:B7,2) returns the second largest value directly, and =SORTBY(A2:B7,B2:B7,-1) lists the whole table from highest to lowest (Excel 2021 or Microsoft 365; see SORT and SORTBY).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Sales | Rank | Place | Name | |
| 2 | Ana | 4200 | 3 | 1 | Cleo | |
| 3 | Ben | 3100 | 5 | 2 | Eve | |
| 4 | Cleo | 5600 | 1 | 3 | Ana | |
| 5 | Dan | 2800 | 6 | |||
| 6 | Eve | 4900 | 2 | |||
| 7 | Finn | 3700 | 4 |
Column C is the unique rank from the ties section, so a tie can never leave a place empty. F2 to F4 list Cleo, Eve and Ana. Change Ana's sales to 6000 and she moves to the top.
Try it: fastest runner first
| A | B | C | |
|---|---|---|---|
| 1 | Runner | Time | Rank |
| 2 | Ana | 58.4 | |
| 3 | Ben | 55.1 | |
| 4 | Cleo | 61.9 | |
| 5 | Dan | 53.7 | |
| 6 | Eve | 57.3 | |
| 7 | Finn | 60.2 |
Your turn: In C2, rank Ana's time in B2 among all the times in B2:B7, so that the fastest (lowest) time ranks 1. The formula is filled down to C7, so use absolute references for the list.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Class | Score | Rank in class |
| 2 | Ana | Red | 81 | |
| 3 | Ben | Red | 92 | |
| 4 | Cleo | Blue | 77 | |
| 5 | Dan | Red | 69 | |
| 6 | Eve | Blue | 95 | |
| 7 | Finn | Red | 85 | |
| 8 | Gia | Blue | 88 |
Your turn: In D2, rank Ana's score among the students of her own class only (column B), highest score ranked 1. The formula is filled down to D8.
Common mistake: a relative range
Without the $ signs, the range moves down one row each time the formula is filled, so each row is ranked against a shrinking list that starts at its own row. The ranks look plausible but are wrong:
| A | B | C | |
|---|---|---|---|
| 1 | Name | Sales | Rank |
| 2 | Ana | 4200 | 3 |
| 3 | Ben | 3100 | 4 |
| 4 | Cleo | 5600 | 1 |
| 5 | Dan | 2800 | 3 |
| 6 | Eve | 4900 | 1 |
| 7 | Finn | 3700 | 1 |
Click C4: its range is B4:B9, so Cleo is compared only with the people from her row down. Eve shows rank 1 although Cleo sold more, and Finn shows 1 because he is compared with himself alone. Change C2 to =RANK.EQ(B2,$B$2:$B$7) and the whole column corrects itself. Press F4 while the cursor is in the range to add the $ signs (on a Mac, Cmd+T or Fn+F4); absolute references explains why.
Frequently Asked Questions
How do I rank numbers in Excel?
Use =RANK.EQ(B2,$B$2:$B$7) and fill it down. The largest value gets rank 1. Keep the $ signs on the range so it does not shift as you fill.
How do I rank from smallest to largest in Excel?
Add 1 as the third argument: =RANK.EQ(B2,$B$2:$B$7,1). Use it for race times, prices or anything where the lowest value should be first.
What is the difference between RANK.EQ and RANK.AVG?
They differ only on ties. If two values tie for second place, RANK.EQ gives both 2 and the next value 4. RANK.AVG gives both 2.5, the average of positions 2 and 3.
How do I rank without duplicates in Excel?
Add a running count of the value: =RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1. The first of two tied values keeps the rank and the second gets the next one.
How do I rank within a group in Excel?
Count the values in the same group that are larger, plus 1: =COUNTIFS($A$2:$A$9,A2,$C$2:$C$9,">"&C2)+1. There is no RANKIF function.
