How to Use the RANK Function in Google Sheets
=RANK(value, data, [is_ascending]). By default the greatest value gets rank 1.
What RANK does
RANK tells you where a value stands in a list: first, second, third. Use it for sales leaderboards, grade tables and any "top N" report. To list the values in order instead, use SORT.
RANK syntax
=RANK(value, data, [is_ascending])
value: the value to rank. If it is not indata, RANK returns #N/A.data: the range containing the whole list.is_ascending(optional): defaults to 0, where the greatest value gets rank 1. With 1, the least value gets rank 1.
Google's help page does not say how tied values are ranked, so check your own data if ties are possible.
A step-by-step example
Here are four scores in cells A1:B5.
| A | B | |
|---|---|---|
| 1 | Name | Score |
| 2 | Ann | 82 |
| 3 | Ben | 54 |
| 4 | Cara | 67 |
| 5 | Dev | 91 |
=RANK(B2, B2:B5)
This returns 2. Here is why: Ann's 82 is the second greatest score, behind Dev's 91. To rank from the lowest instead:
=RANK(B2, B2:B5, 1)
This returns 3, because with ascending order the order is 54, 67, 82, 91 and 82 is third.
Fill it down safely
To rank every row, lock the data range with dollar signs so it does not shift when you copy the formula down: =RANK(B2, $B$2:$B$5).
Common mistakes
- Forgetting the dollar signs. The range slides down and the ranks come out wrong.
- Expecting ascending by default. Rank 1 is the greatest value unless the third argument is 1.
- Ranking a value that is not in the data. The result is #N/A.
Try it yourself
Use the table above (A1:B5). Type your formula into the box, starting with =, then press Check.
Exercise 1: Highest is 1
Write a formula that ranks the score in B2 within B2:B5, with the highest score as rank 1.
Exercise 2: Lowest is 1
Write a formula that ranks the score in B2 within B2:B5, with the lowest score as rank 1.
What to learn next
- SORT: sort a range with a formula.
- ROUNDUP and ROUNDDOWN: always round up or always round down.
- NOW: the current date and time.
- SORTN: return the top or bottom N rows.
Syntax and rules in this guide were checked against Google's RANK help page. See all guides.