How to Rank Numbers in Google Sheets
Type =RANK(A2,$A$2:$A$100) to rank A2 against the whole list, with 1 being the highest number. Lock the range with $ so it doesn't shift as you drag down.
=RANK(A2,$A$2:$A$100)
Ranks A2 within the fixed range; largest value gets rank 1. The $ signs keep the range fixed when you fill down.
How it works
RANK tells you where a number sits in a list. By default the largest value is rank 1 (descending). Add a third argument of 1 to rank smallest-first (ascending). Always lock the comparison range with $ so every row compares against the same list. Tied numbers get the same rank and skip the next one; use RANK.AVG if you'd rather average tied ranks.
Variations
Rank smallest to largest
=RANK(A2,$A$2:$A$100,1)
The 1 flips it so the smallest number is rank 1.
Average tied ranks
=RANK.AVG(A2,$A$2:$A$100)
Two tied values share the average of the ranks they'd occupy.
Rank the whole column at once
=ARRAYFORMULA(IF(A2:A="","",RANK(A2:A,A2:A)))
Fills a rank for every filled row without dragging.
Examples
| Scenario | Formula |
|---|---|
| Leaderboard, highest first | =RANK(A2,$A$2:$A$100) |
| Fastest time, lowest first | =RANK(A2,$A$2:$A$100,1) |
FAQ
How do I rank so the highest number is 1?
That's the default: =RANK(A2,$A$2:$A$100). The largest value gets rank 1.
How do I rank ascending (smallest first)?
Add 1 as the third argument: =RANK(A2,$A$2:$A$100,1).
Why do two ranks skip a number?
Ties share a rank and skip the next, so two 1sts leave no 2nd. Use RANK.AVG to average tied ranks instead.