Excel RANK Function: Position a Value Within a List
Returns the position of a number within a list, largest first by default.
RANK tells you where a value sits in a list: 1 for the largest, 2 for the next, and so on. It is how a leaderboard column gets its numbers, and how you turn a column of scores into a column of positions without sorting anything.
Ties are handled by giving both values the same rank and then skipping the next one. Two values tied at 3rd means the next value is 5th, not 4th, which is standard competition ranking but surprises people the first time they see a 4 missing from the column. RANK.AVG averages the tied positions instead, so those two would both show 3.5.
The order argument is easy to get backwards. Leave it out or pass 0 and you get descending — largest is rank 1, which is what a leaderboard wants. Pass 1 for ascending, where the smallest value ranks first, which is what you want for lap times or delivery costs.
Syntax
=RANK.EQ(number, ref, [order])Arguments
numberRequired- The value whose position you want. Usually the cell on the current row.
refRequired- The list to rank within. Lock it with absolute references before filling down, or each row ranks against a different list.
orderOptional- 0 or omitted ranks descending (largest is 1). Any non-zero value ranks ascending (smallest is 1).
The example data
Headers in row 1, data in A2:C8. Note how far F8's salary sits above the rest.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Salary | Team |
| 2 | Alice Moreau | 32000 | Support |
| 3 | Bruno Santos | 28000 | Support |
| 4 | Chen Wei | 45000 | Engineering |
| 5 | Dana Okafor | 28000 | Support |
| 6 | Erik Halls | 38000 | Engineering |
| 7 | Farah Idris | 41000 | Engineering |
| 8 | Greg Nolan | 154000 | Executive |
Worked examples
=RANK.EQ(B2, $B$2:$B$8)Result: 5
Alice's salary is fifth largest. The $ signs matter — without them, filling down would shift the list.
=RANK.EQ(B3, $B$2:$B$8)Result: 6
Bruno ties with Dana on 28,000, so both get 6 and nothing gets 7 — the next rank down is 8... except there is no 8th row, so the sequence simply ends.
=RANK.EQ(B2, $B$2:$B$8, 1)Result: 3
The same salary ranked ascending. Order 1 makes the smallest value rank first.
=RANK.EQ(B3, $B$2:$B$8) + COUNTIF($B$2:B3, B3) - 1Result: 6
The tie-breaker. COUNTIF over an expanding range gives the first tied value its rank and the second the one after, producing 6 and 7 instead of 6 and 6.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#N/A
Why it happens: The number is not present in ref.
How to fix it: Check the range covers the cell you are ranking. This usually means the absolute references are wrong.
Every row returns 1
Why it happens: ref was not locked, so as the formula fills down it ranks each value against a shrinking list.
How to fix it: Use $B$2:$B$8, not B2:B8.
Rank numbers skip
Why it happens: Ties consume the positions after them. Two firsts means no second.
How to fix it: That is competition ranking and usually correct. Use RANK.AVG for shared fractional ranks, or the COUNTIF pattern to force distinct ones.
Ranking is upside down
Why it happens: The order argument is inverted.
How to fix it: Omit it or pass 0 for largest-first; pass 1 for smallest-first.
Tips worth knowing
- RANK is the legacy name and still works; RANK.EQ is the modern equivalent with identical behaviour.
- SORT is better when you want the rows reordered; RANK is better when the rows must stay put and gain a position column.
- RANK ignores text and blanks, so a partially filled column ranks only its numbers.
- For ranking inside groups, RANK cannot help — use COUNTIFS counting how many rows in the same group beat this one, plus 1.
Frequently asked questions
Why does my rank skip numbers?
Because of ties. When two values share a position, both take the lower number and the next position is consumed — two 3rds means the next is 5th. This is standard competition ranking. RANK.AVG gives both 3.5 instead, and a COUNTIF adjustment forces genuinely sequential ranks.
How do I rank smallest to largest?
Pass 1 as the third argument: =RANK.EQ(B2, $B$2:$B$100, 1). Omitting it or passing 0 ranks largest first, which is the default because leaderboards are the common case.
How do I rank within groups?
RANK has no grouping argument. Use COUNTIFS instead: =COUNTIFS(team_range, team, score_range, ">"&score) + 1 counts how many rows in the same team scored higher, and adding one turns that into a position.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.