How to rank numbers in Excel
Formula (Excel and Google Sheets)
=RANK.EQ(B2,$B$2:$B$7)Use =RANK.EQ(value, list) for largest = 1, adding 1 as the third argument for smallest = 1. Lock the list with $.
- Result on the example
- one result per row in
C2, filled down toC7 - Works in
- Excel 2010 and later
- Google Sheets
- Same formula
See it work
The formula is already in C2, filled down to C7. Change any input and the result updates, or edit the formula to experiment.
How it works
RANK.EQ returns where the number would sit if the list were sorted, highest first. Ties share a rank and the next rank is skipped: Ben and Eli are both 1, so Cara is 3.
=RANK.EQ(B2,$B$2:$B$7)Result4
B2$48,200The value in B2.
$B$2:$B$76 rows × 1 col: $48,200, $61,750, $57,300, $39,800, …A range: 6 cells in one column.
RANK.EQ(B2,$B$2:$B$7)4RANK: Rank of a number in a list. 0 or omitted = largest is 1.- number
B2$48,200 - ref
$B$2:$B$76 rows × 1 col: $48,200, $61,750, $57,300, $39,800, …
- number
In Google Sheets
The same formula works in Google Sheets: =RANK.EQ(B2,$B$2:$B$7). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =COUNTIF($B$2:$B$7,">"&B2)+1 | 4, 1, 3… | Counts how many values are larger and adds one; the same result, written without RANK. |
| =RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1 | 4, 1, 3…Different result | Breaks ties by order, so every rank is unique. Eli becomes 2 instead of sharing 1. |
Common mistakes
- Without $, the list shifts as you fill down and the ranks come out wrong.
- RANK (without .EQ) is the older name for the same function. RANK.AVG gives tied values the average rank, like 1.5.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Rank numbers
C2, rank each rep by sales with the highest as 1, and fill down to C7.Questions
How do I rank from smallest to largest?
Add 1 as the order: =RANK.EQ(B2,$B$2:$B$7,1).
How do I rank within groups?
Use COUNTIFS: =COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,">"&B2)+1 ranks within the group in column A.
Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.