Excel RANK Function with Ties: How to Handle Them

Formulas & functions · Updated November 22, 2026

The Excel RANK function assigns a rank to a number within a list, and when two values are identical, they receive the same rank (a tie). By default, RANK uses a descending order, so the largest number gets rank 1, and ties cause the next rank to be skipped (e.g., two values tied for 2nd place mean the next rank is 4th). To handle ties differently, you can use the RANK.EQ function (which behaves the same as RANK) or RANK.AVG, which assigns the average rank to tied values. This article explains how each function works, how to choose between them, and how to break ties with additional criteria.

Background: Why Ties Matter in Ranking

Ranking is a common task in spreadsheets—whether you’re ranking sales teams, student scores, or sports standings. When two or more values are identical, the question becomes: should they share the same rank, or should one be forced ahead of the other? The standard RANK function (and its modern equivalent RANK.EQ) treats ties as “equal,” which is useful for competitions where equal performance deserves equal placement. However, this creates gaps in the rank sequence, which can be confusing when you expect ranks 1, 2, 3, 4 but instead see 1, 2, 2, 4. Excel also offers RANK.AVG, which averages the ranks that the tied values would have occupied, giving 2.5 for two values tied for 2nd and 3rd. Understanding these differences is essential for accurate reporting.

How the RANK Function Works

The RANK function syntax is: =RANK(number, ref, [order]). The number is the value you want to rank, ref is the range containing all values, and order is optional: 0 or omitted for descending (largest gets rank 1), or any non-zero value for ascending (smallest gets rank 1). For example, with values in A2:A10, the formula =RANK(A2, $A$2:$A$10, 0) ranks each value in descending order. If A2 contains the largest value, it returns 1. If two values are equal, both get the same rank, and the next rank is skipped. This is the classic “competition ranking” (also called “1224” ranking).

In Excel 2010 and later, Microsoft introduced RANK.EQ and RANK.AVG to replace RANK, though RANK still works for backward compatibility. RANK.EQ is identical to RANK in behavior. RANK.AVG, on the other hand, assigns the average of the ranks that the tied values would have received. For instance, if two values tie for 2nd place (occupying ranks 2 and 3), both get 2.5. This is often used in statistical contexts where fractional ranks are acceptable.

How to Choose Between RANK.EQ and RANK.AVG

The choice depends on your goal. Use RANK.EQ (or RANK) when you want a simple competition ranking where ties share the same rank and you don’t mind gaps. This is typical for sports leagues or sales contests where equal performance deserves equal recognition. Use RANK.AVG when you need a rank that reflects the average position, which is common in educational grading (e.g., percentile calculations) or when you plan to use the rank in further statistical analysis. For example, if you have scores 90, 85, 85, 80, RANK.EQ gives ranks 1, 2, 2, 4, while RANK.AVG gives 1, 2.5, 2.5, 4. If you need a rank that uniquely identifies each row (no ties), neither function will do; you’ll need to break ties manually.

Breaking Ties with Additional Criteria

Sometimes you want a unique rank even when values tie. For example, in a sales contest, two salespeople with the same revenue might be ranked by who reached that revenue first. To break ties, you can add a secondary column (e.g., a timestamp or a tiebreaker score) and use a helper column. One method: add a tiny fraction based on the tiebreaker to the original value, then rank the adjusted values. For instance, if sales are in B2:B10 and you want to break ties by a smaller “units sold” in C2:C10, create a helper column D with =B2 + (C2/1000000) (assuming C is much smaller than B) and rank column D. Alternatively, use the COUNTIFS function to count how many values are greater than the current value, plus 1, which gives a unique rank when you add a tiebreaker condition. For example, =COUNTIFS($B$2:$B$10, ">"&B2) + COUNTIFS($B$2:$B$10, B2, $C$2:$C$10, ">"&C2) + 1 ranks by sales descending, and for ties, by units sold descending. This formula works in Excel 2010 and later.

Common Problems and Misconceptions

One common misconception is that RANK and RANK.EQ are different—they are not; RANK.EQ is just the modern name. Another issue: users often forget to lock the range reference with absolute references (e.g., $A$2:$A$10) when copying the formula down; otherwise, the range shifts and ranks become incorrect. Also, if your data contains text or blank cells, RANK ignores them, but if you have errors, they may cause #N/A. Another problem: when using RANK.AVG, the result may be a decimal, which can be surprising if you expected whole numbers. Finally, note that RANK works with numbers only—if you try to rank text, it returns #VALUE!.

Comparing RANK, RANK.EQ, and RANK.AVG

Here’s a quick comparison of the three functions:

  • RANK – Legacy function, identical to RANK.EQ. Use for compatibility with older Excel versions.
  • RANK.EQ – Modern equivalent, same behavior as RANK. Recommended for new workbooks.
  • RANK.AVG – Assigns the average rank to tied values. Useful for statistical analysis.

All three accept the same arguments and are available in Excel 2010 and later. In Excel for Mac, the functions work identically, and the formula syntax is the same. In Excel for the web, they are also supported.

Alternative Approaches: DENSE_RANK and Other Methods

If you want a “dense” ranking where ties share a rank but the next rank is not skipped (e.g., 1, 2, 2, 3), Excel does not have a built-in DENSE_RANK function like SQL. However, you can achieve it with a formula: =SUMPRODUCT((A2:A10>0)/COUNTIF(A2:A10, A2:A10)) is one way, but a simpler method is to use the UNIQUE function (Excel 365) to list unique values, sort them, and then use MATCH. For example, with values in A2:A10, the formula =MATCH(A2, SORT(UNIQUE($A$2:$A$10), 1, -1), 0) gives a dense rank in descending order. This requires Excel 365 or Excel 2021. For older versions, you can use an array formula with FREQUENCY, but it’s more complex. Another alternative is to use the RANK function on a helper column that has been transformed to make ties unique (as described earlier).

Frequently Asked Questions

Q: How do I rank without skipping numbers after a tie?
A: Use the dense ranking method. In Excel 365, use =MATCH(A2, SORT(UNIQUE($A$2:$A$10), 1, -1), 0) for descending order. For older versions, you can use a helper column with a formula like =SUMPRODUCT((A2:A10>=A2)/COUNTIF(A2:A10, A2:A10)) but that’s an array formula and may require Ctrl+Shift+Enter.

Q: Can I rank in ascending order?
A: Yes, set the third argument of RANK to any non-zero number, e.g., =RANK(A2, $A$2:$A$10, 1) gives rank 1 to the smallest value.

Q: What does RANK.AVG do exactly?
A: For tied values, it returns the average of the ranks they would have occupied. For example, if two values tie for 2nd and 3rd, both get 2.5.

Q: Why does my RANK formula return #N/A?
A: This happens if the number argument is not found in the ref range. Check that the value exists and that the range is correct.

Q: How do I rank only visible rows after filtering?
A: Use the SUBTOTAL function with function number 104 (for RANK.AVG) or 103 (for COUNT), but SUBTOTAL doesn’t directly rank. Instead, you can use a helper column with a formula that checks if the row is visible, but it’s tricky. A simpler approach is to use the AGGREGATE function, but ranking visible rows requires a more complex array formula. In Excel 365, you can use FILTER to create a filtered array and then rank that.

Q: Is there a way to rank with ties broken by a second column automatically?
A: Yes, use a helper column that combines the primary and secondary criteria, then rank that. For example, if you have sales in B and units in C, create a helper D with =B2*1000 + C2 (assuming C is always less than 1000) and rank D. Or use the COUNTIFS method described earlier.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.