How to rank numbers in Excel
RANK.EQ tells you where a value places in a list. By default the largest value ranks 1.
Tied values share a rank, and the next rank is skipped, just like a race with two people in second place.
The formula
| A | B | |
|---|---|---|
| 1 | Name | Score |
| 2 | Amara | 72 |
| 3 | Ben | 90 |
| 4 | Chloe | 65 |
| 5 | Dev | 90 |
| 6 | Elena | 83 |
=RANK.EQ(B6,$B$2:$B$6)Result: 3
Elena’s 83 ranks 3rd: Ben and Dev share 1st with 90.
Step by step
- Type =RANK.EQ( and click the first score.
- Add a comma, select all the scores, and press F4 to lock them.
- Close the bracket, press Enter, and fill down.
Other ways to write it
=COUNTIF($B$2:$B$6,">"&B6)+1Counts how many scores beat this one, then adds 1. Handy in older spreadsheets.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- An unlocked list
- Without $, the list shrinks as you fill down and the ranks go wrong.
- Wanting smallest first
- Add 1 as a third argument, as in RANK.EQ(B2,$B$2:$B$6,1), to rank the smallest value first.
Reading isn’t the same as doing
Top performers and rankings gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.