What it does
RANK.EQ needs three things: the number to rank, the list to compare it with and the direction. If the direction is omitted or 0, the largest value ranks 1st (sales, scores). With 1, the smallest value ranks 1st (time, cost, number of errors).
Tied values share a rank and the next rank is skipped: if two students are 2nd, the next one is 4th. If ties should get the average rank, use RANK.AVG. To break ties and give every row its own rank, use =RANK.EQ(B2,$B$2:$B$9)+COUNTIF($B$2:B2,B2)-1; whichever comes first in the list goes ahead.
Lock the list reference as $B$2:$B$9; otherwise the range shifts when you fill the formula down and the ranks go wrong. The older RANK function returns the same result and is kept for compatibility.
Syntax
=RANK.EQ(number, ref, [order])=RANK.EŞİT(sayı; başv; [sıra])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
number | Required | The number or cell to rank. It must be in ref. |
ref | Required | The list to compare with, e.g. $B$2:$B$9. Cells that are not numbers are ignored. |
order | Optional | 0 or omitted: largest to smallest, so the largest value ranks 1st. 1: smallest to largest, so the smallest value ranks 1st. |
Examples
1Rank by sales
The third argument is left out so that the highest sale ranks 1st. The list range is locked with $.
=RANK.EQ(B2,$B$2:$B$6)| A | B | C | |
|---|---|---|---|
| 1 | Rep | Sales | Rank |
| 2 | Emma | 48,500 | 4 |
| 3 | Liam | 61,200 | 1 |
| 4 | Olivia | 52,300 | 3 |
| 5 | Noah | 29,750 | 5 |
| 6 | Ava | 57,800 | 2 |
- C2 (filled down to C6)
=RANK.EQ(B2,$B$2:$B$6)
Liam's rank is 1, Ava's 2 and Noah's 5.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Make the shortest delivery time rank first
For delivery times, smaller is better. With 1 as the third argument, the shortest time takes 1st place.
=RANK.EQ(B2,$B$2:$B$5,1)| A | B | C | |
|---|---|---|---|
| 1 | Courier | Average Days | Rank |
| 2 | Swift Post | 2.4 | 2 |
| 3 | Northern Freight | 3.1 | 3 |
| 4 | Express Carriers | 1.8 | 1 |
| 5 | Hillside Haulage | 4.6 | 4 |
- C2 (filled down to C5)
=RANK.EQ(B2,$B$2:$B$5,1)
Express Carriers gets rank 1 and Hillside Haulage rank 4. Without the third argument, the order would be reversed.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Give tied scores their own rank
Column C shows RANK.EQ's own result and column D the rank with ties broken. The range $B$2:B2 grows by one row each time; COUNTIF counts how many times the score has appeared up to that row.
=RANK.EQ(B2,$B$2:$B$7)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Rank | Unique Rank |
| 2 | Mia | 90 | 2 | 2 |
| 3 | Lucas | 85 | 4 | 4 |
| 4 | Ella | 90 | 2 | 3 |
| 5 | Jack | 78 | 6 | 6 |
| 6 | Grace | 85 | 4 | 5 |
| 7 | Henry | 95 | 1 | 1 |
- C2 (filled down to C7)
=RANK.EQ(B2,$B$2:$B$7) - D2 (filled down to D7)
=RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1
Mia and Ella share rank 2 in column C, and rank 3 is skipped. In column D Mia is 2 and Ella 3; Lucas and Grace are split into 4 and 5.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #N/A
Cause: number is not in ref; usually the list range has shifted or the value lies outside the list.
Fix: Lock the list reference with
$and make sure the number is in the list.- Ranks drift
Cause: ref was not locked before filling down, so each row was compared with a different range.
Fix: Use
$B$2:$B$9instead ofB2:B9.- A rank is skipped
Cause: Tied values got the same rank and the next rank number was skipped (1, 2, 2, 4).
Fix: That is how RANK.EQ works; if every row needs its own rank, break the tie with COUNTIF.
Good to know
- For rankings where the lowest value is best (time, cost), remember to pass 1 as the third argument.
- Instead of sorting the list by the rank column, build a sorted copy with SORT in Microsoft 365; the source list stays as it is.
In Nexell
Get Nexell to do it from a sentence
Add a Rank column to the sales list that shows each rep's sales ranking
Convert to tableBring your sales list into the tool and type this. Nexell adds the Rank column with real Excel formulas and shows it in the preview first; in the downloaded file, the ranking changes when the sales change.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I give tied values different ranks in Excel?
Use =RANK.EQ(B2,$B$2:$B$9)+COUNTIF($B$2:B2,B2)-1 and fill it down. COUNTIF counts how many times the value has appeared up to that row, so the second occurrence moves one place down.
What is the difference between RANK.EQ and RANK.AVG?
If two values tie for 2nd place, RANK.EQ gives both 2. RANK.AVG gives both 2.5, the average of the 2nd and 3rd places.