Yes, Excel’s RANK.EQ function assigns the same rank to tied values by default, so two identical scores both get the same rank (e.g., both get rank 2, and the next rank is 4). To produce ranks with no ties—where each value gets a unique rank—use RANK.EQ combined with COUNTIF to break ties by adding a small adjustment based on the row order.
How RANK.EQ Works and Why It Creates Ties
The RANK.EQ function (which replaced the older RANK in Excel 2010) returns the rank of a number within a list of numbers. Its syntax is RANK.EQ(number, ref, [order]). The order argument is optional: 0 or omitted ranks descending (largest gets rank 1), and any non-zero value ranks ascending (smallest gets rank 1).
When two values are identical, RANK.EQ gives both the same rank. For example, with scores 90, 85, 85, 70 in cells A2:A5, =RANK.EQ(A2, $A$2:$A$5) returns 1 for 90, 2 for the first 85, 2 for the second 85, and 4 for 70. The rank 3 is skipped because two values share rank 2.
How to Get Unique Ranks (No Ties)
To break ties, you add a tie-breaker to the rank. The most common method uses COUNTIF to count how many earlier cells in the list have the same value, then add that count to the rank. The formula assumes your data is in a column, say A2:A20, and you enter the formula in B2 and copy down.
With values in A2:A20, enter this in B2:
=RANK.EQ(A2,$A$2:$A$20)+COUNTIF($A$2:A2,A2)-1
How it works: COUNTIF($A$2:A2,A2) counts how many times the current value appears from the start of the list up to the current row. Subtracting 1 gives the number of previous ties. Adding that to the base rank ensures the first tie in the list gets the lower rank, the second gets the next rank, and so on. For the example 90, 85, 85, 70, the results are 1, 2, 3, 4—no ties.
If you want the tie-break to favor the last occurrence instead (e.g., give the later row a better rank), use COUNTIF(A2:$A$20,A2)-1 instead.
Alternatives: Using COUNTIFS for Multiple Columns
If you need to break ties based on a second column (e.g., same total score, then higher sales wins), use COUNTIFS to count rows that have the same primary value and a greater secondary value. For example, with scores in A2:A20 and sales in B2:B20, rank by score descending, and for ties, higher sales gets a better rank:
=RANK.EQ(A2,$A$2:$A$20)+COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,">"&B2)
This adds the number of rows with the same score but higher sales, effectively pushing the current row down by that count. The result is a unique rank where ties are broken by the secondary column. Note that if two rows have the same score and same sales, they still tie; to fully break ties you’d need a third criterion or row order.
Common Problems and Misconceptions
One common mistake is using RANK (the legacy function) instead of RANK.EQ—they behave identically, but RANK is still available for compatibility. Another is forgetting to lock the range with absolute references ($A$2:$A$20) when copying the formula down; otherwise the range shifts and ranks become incorrect.
A frequent misconception is that Excel has a built-in “no ties” option. It doesn’t—you must use a tie-breaker formula. Also, note that the COUNTIF method depends on the order of rows in the list. If you sort the data, the tie-break order changes, which may or may not be what you want. If you need a stable tie-break independent of row order, use a secondary column as described above.
Comparing RANK.EQ, RANK.AVG, and RANK
Excel offers three rank functions: RANK.EQ (ties get same rank), RANK.AVG (ties get the average of the ranks they would occupy), and the legacy RANK (same as RANK.EQ). For example, with 90, 85, 85, 70, RANK.AVG gives 1 for 90, 2.5 for both 85s, and 4 for 70. RANK.EQ gives 2 for both 85s. If you need no ties, neither function does that directly—you must add a tie-breaker.
Using Excel Tables and Dynamic Arrays
If your data is in an Excel Table (Insert > Table), you can use structured references, but the COUNTIF trick gets trickier because the expanding range syntax is limited. A simpler approach is to use the SORTBY and SEQUENCE functions (available in Excel 365) to generate ranks dynamically. For example, with scores in A2:A20, this formula in C2 returns a unique rank for each row, breaking ties by row order:
=MATCH(A2,SORTBY($A$2:$A$20,$A$2:$A$20,-1),0)+COUNTIF($A$2:A2,A2)-1
But that’s more complex. A cleaner dynamic array approach uses SORTBY to sort the values and then XMATCH to find each value’s position, but that still ties. For a true no-tie dynamic array, you can use RANK.EQ with COUNTIF on the entire column, but that requires a helper column. The simplest is still the helper column with the formula in section 2.
Step-by-Step Example with Real Data
Suppose you have student names in A2:A6 and test scores in B2:B6: Alice 88, Bob 92, Carol 88, David 75, Eve 92. You want ranks with no ties, where higher score = better rank, and ties broken by alphabetical order (or row order, which is the same here).
- In C2, enter
=RANK.EQ(B2,$B$2:$B$6)+COUNTIF($B$2:B2,B2)-1. - Copy C2 down to C6.
Results: Bob and Eve both have 92, so Bob (row 2) gets rank 1, Eve (row 5) gets rank 2. Alice and Carol both have 88, so Alice (row 3) gets rank 3, Carol (row 4) gets rank 4. David gets rank 5. The ranks are 1,2,3,4,5—no ties.
Frequently Asked Questions
Why does my RANK.EQ formula show the same rank for duplicate values? That’s the intended behavior of RANK.EQ. It assigns the same rank to equal values. To get unique ranks, you must add a tie-breaker like COUNTIF as shown above.
Can I use RANK.AVG to avoid ties? No, RANK.AVG gives tied values the average rank (e.g., 2.5 for two values sharing ranks 2 and 3). That still results in duplicate ranks (both get 2.5). Only a tie-breaker formula gives unique integer ranks.
How do I rank in descending order (largest first) with no ties? Use the same formula with the order argument omitted or set to 0: =RANK.EQ(A2,$A$2:$A$20)+COUNTIF($A$2:A2,A2)-1. For ascending (smallest first), add ,1 to the RANK.EQ part: =RANK.EQ(A2,$A$2:$A$20,1)+COUNTIF($A$2:A2,A2)-1.
Does the tie-breaker formula work in Google Sheets? Yes, both RANK.EQ and COUNTIF exist in Google Sheets, and the formula works the same. In Google Sheets, you can also use RANK (which behaves like RANK.EQ).
What if I want ties to be broken by a random order? You can add a random number column (e.g., =RAND()) and use that as a secondary criterion in COUNTIFS, but note that random numbers recalculate each time the sheet changes, so the ranks will change. To keep them stable, copy and paste values over the random column.
Is there a way to do this without a helper column? Yes, with dynamic array formulas in Excel 365, you can use BYROW or MAKEARRAY to compute ranks in a single formula, but it’s complex. The helper column method is simpler and works in all versions.
Related
- Excel Gradebook Template for Teachers: How to Use
- Excel Group Lake Jackson TX: What It Is & Services
- Excel Flexi Timesheet Template UK: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MATCH Function: Arguments and Match TypesThe Excel MATCH function takes a value to find, a one-dimensional range to search, and an optional match…
- Excel Split Cell at a Space: Methods and Formulas ExplainedTo split text at a space in Excel for Microsoft 365 on Windows, select the cells and choose…
- Excel Formulas for Budget Sheet: Top 10 to KnowThe fastest way to build a budget in Excel is to use =SUM for totals, =SUMIF to total…
- CAGR Calc Excel Formula: Calculate Annual Growth RateIf the beginning value is in B2, the ending value is in C2, and the number of years…
- Excel Protect Sheet: What to Do If You Forgot the PasswordIf you forgot an Excel worksheet password, Microsoft Excel has no built-in way to reveal or reset it:…
- Excel Median Formula: How to Calculate the Middle ValueUse Excel’s MEDIAN function to find the middle number in a set: if your values are in A2:A10,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.