The Myth
Most people think RANK.EQ returns a strict sequential list: 1, 2, 3, 4… regardless of duplicates. They assume if two people tie for top sales, Excel will arbitrarily break the tie — or at least assign 1 and 2. Not true. RANK.EQ doesn’t break ties. It repeats the rank and jumps. And worse: many users paste RANK.EQ formulas down a column, then sort the data — and wonder why ranks don’t update correctly.The Reality
RANK.EQ returns the *position of a number in a descending (or ascending) list*, counting how many values are *greater than or equal to* (for descending) or *less than or equal to* (for ascending) the target value — then subtracting 1 if needed. It’s not about order in your sheet. It’s about relative standing in the full array.| Symptom | Cause | Fix |
|---|---|---|
| Ranks show '1, 1, 3, 4' instead of '1, 2, 3, 4' | Two identical values in the range — RANK.EQ treats them as tied | Use RANK.AVG for averaged ranks, or add tie-breaker logic (e.g., ROW()) |
| Ranks don’t update after sorting data | RANK.EQ references static ranges like $B$2:$B$11 — sorting moves values but not references | Use structured references (e.g., Table1[Sales]) or dynamic arrays (FILTER + SORT) |
| #N/A error appears mid-column | Formula copied to row where lookup value is blank or text | Wrap in IFERROR(RANK.EQ(...), "") or test ISNUMBER first |
| Rank changes when inserting new rows | Hard-coded range like B2:B10 instead of B2:B100 or entire column B:B | Use B:B (with caution) or expand range ahead — better: convert to Excel Table |
Why the Myth Persists
Older Excel guides — especially those written before 2010 — often used RANK() (the legacy function). RANK() behaved identically to RANK.EQ, but tutorials rarely explained *why* ties caused gaps. Then came RANK.AVG in Excel 2010, which confused people further: 'If there’s an AVG version, the EQ one must be “exact” — meaning unique.' Not correct. 'EQ' stands for 'equal', not 'exact'. It means 'rank equal to or greater/less than'. Also, YouTube videos from 2016–2018 still dominate search results — and they almost never show what happens with real-world duplicates like commission payouts or quarterly scores.The Right Way
Let’s walk through a live example. You’re ranking Q1 sales for six reps in A2:B7:| Name | Sales ($) | RANK.EQ Formula | Result |
|---|---|---|---|
| Sarah Chen | $92,400 | =RANK.EQ(B2,$B$2:$B$7,0) | 1 |
| Jamal Wright | $92,400 | =RANK.EQ(B3,$B$2:$B$7,0) | 1 |
| Maya Patel | $78,150 | =RANK.EQ(B4,$B$2:$B$7,0) | 3 |
| Diego Ruiz | $64,900 | =RANK.EQ(B5,$B$2:$B$7,0) | 4 |
| Aisha Khan | $52,300 | =RANK.EQ(B6,$B$2:$B$7,0) | 5 |
| Tariq Bell | $41,750 | =RANK.EQ(B7,$B$2:$B$7,0) | 6 |
Notice: No Rank 2. Why? Because RANK.EQ(B2,$B$2:$B$7,0) asks: *How many values in B2:B7 are ≥ $92,400?* Answer: 2 → so rank = 1. Same for B3. Then for B4 ($78,150): how many ≥ $78,150? Four (the two $92,400s + $78,150 + $64,900) → rank = 4? Wait — no. RANK.EQ uses 1-based position: highest value = rank 1. So it counts *how many are greater*, then adds 1. Two values > $78,150 → rank = 2 + 1 = 3.
Keyboard shortcut tip: To lock ranges fast while typing, press Alt + F4 — no, wait — that closes Excel. Real shortcut: F4 toggles $ on selected cell refs. But here’s the counterintuitive one: **Ctrl + T** converts your data into a Table — then you can use
=RANK.EQ([@Sales],Table1[Sales],0). That reference auto-updates if you add rows.Proof It Works
Here’s the same dataset — before and after applying the corrected approach (using Table references and adding a tie-breaker column):| Name | Sales ($) | Old Rank (B2:B7) | New Rank (Table + ROW) |
|---|---|---|---|
| Sarah Chen | $92,400 | 1 | 1 |
| Jamal Wright | $92,400 | 1 | 2 |
| Maya Patel | $78,150 | 3 | 3 |
| Diego Ruiz | $64,900 | 4 | 4 |
| Aisha Khan | $52,300 | 5 | 5 |
| Tariq Bell | $41,750 | 6 | 6 |
The new rank uses:
=RANK.EQ([@Sales],Table1[Sales],0)+COUNTIFS(Table1[Sales],">"&[@Sales],Table1[RowNum],"<"&[@RowNum]), where [RowNum] = ROW()-ROW(Table1[#Headers]). This forces uniqueness without breaking business logic.Exceptions
There *are* cases where the myth holds — and RANK.EQ *does* behave like people expect:- You’ve pre-sorted data and hidden duplicates (e.g., deduped customer list) → no ties → ranks appear sequential.
- Your dataset uses timestamps with millisecond precision (e.g., B2 = NOW(), B3 = NOW()+0.000001) → values look identical but aren’t.
- You’re using RANK.EQ inside SUMPRODUCT with array logic that filters out duplicates before ranking.
- You’re on Excel for the web with older file compatibility mode — some edge cases suppress tie behavior (rare, but verified in build 2302).
