Excel RANK vs RANK.EQ: Differences, Ties, and Examples

How-to basics · Updated November 19, 2026

RANK and RANK.EQ return the same result in current Excel: each gives a number’s position in a list, with tied values receiving the same rank. Use RANK.EQ in new work because its name makes the tie behavior explicit; use RANK when you need compatibility with older Excel files.

The difference between RANK and RANK.EQ

For ordinary ranking, there is no difference in the result. Both functions accept a value, a range or array to rank against, and an optional setting for the sort direction. Both assign equal values an equal rank, and both leave gaps in the ranks that follow a tie. If you replace RANK with RANK.EQ while keeping the same arguments and data, the ranking is unchanged.

The distinction is mainly one of naming and compatibility. Microsoft introduced RANK.EQ in Excel 2010 as the explicitly named version of the older RANK function. Excel retains RANK for compatibility with existing workbooks, but new formulas are generally clearer with RANK.EQ. The EQ refers to equal values receiving equal ranks; it does not mean that the function tests whether two cells are equal.

How the ranking functions work

The syntax for RANK.EQ is RANK.EQ(number,ref,[order]). The older RANK uses the same argument pattern: RANK(number,ref,[order]). The square brackets in the syntax indicate that order is optional; do not type the brackets into a formula.

  • number: The value whose position you want to find. This can be a number or a cell containing a number.
  • ref: The range or array of values used for comparison. It can include the value being ranked, but does not have to.
  • order: Use 0 or omit this argument to rank from largest to smallest. Use a nonzero value, commonly 1, to rank from smallest to largest.

In descending order, the largest value is rank 1. This is the usual choice for test scores, sales totals, or points, where the highest result should come first. In ascending order, the smallest value is rank 1, which is useful for measures such as elapsed time or error counts when lower is better. Changing the order changes the ranking direction, not the rule for ties.

What happens when values are tied

Both RANK and RANK.EQ give matching values the same rank. The next rank is then skipped so that each value’s rank reflects its position in the ordered list. For example, if two values share second place, the following value receives fourth place, not third. This is often called competition ranking, because it matches the familiar result of a tie in a competition.

That gap is expected, not an error. A rank is a position in the full set of values, and two tied entries occupy two positions. If a report needs consecutive labels such as 1, 2, 3, 4 even when some values match, the rank function alone will not provide that numbering. You need to define an additional tie-breaking rule, such as sorting by a second measure, or use a separate sequential numbering formula.

Excel also has RANK.AVG, which handles ties differently: it returns the average of the positions the tied values would occupy. If two values occupy positions two and three, each gets rank 2.5. Choose that function only when average ranks are appropriate for your analysis; it is not a more accurate replacement for RANK.EQ in every situation.

A worked example with tied scores

Suppose cells B2:B5 contain the scores 95, 90, 90, and 82, in that order. To rank the score in B2 against all four scores, with the highest score first, enter this formula in C2:

With scores in B2:B5 and the formula entered in C2, =RANK.EQ(B2,$B$2:$B$5,0) returns 1.

Copy the formula down through C5. The results are 1, 2, 2, and 4. Both scores of 90 tie for second, so the score of 82 is fourth. Replacing the function name with RANK gives exactly the same results:

With scores in B2:B5 and the formula entered in C2, =RANK(B2,$B$2:$B$5,0) returns 1; copied down, the results are 1, 2, 2, and 4.

The dollar signs make the comparison range absolute. When the formula is copied down, B2 changes to B3, B4, and B5, while the reference to B2:B5 stays fixed. If you omit the third argument, Excel uses descending order, so the results in this example are unchanged.

For a fastest-time-first list, the same values ranked in ascending order would give 82 rank 1, both 90s rank 2, and 95 rank 4. With scores in B2:B5 and the formula entered in C2, =RANK.EQ(B2,$B$2:$B$5,1) returns 4 for the 95 in B2. This illustrates why it is worth checking the order argument before interpreting a ranking.

Using ranks in a real worksheet

A rank formula compares a value with the reference range each time Excel calculates the worksheet. For a sales list, you might put employee names in A2:A20, sales figures in B2:B20, and ranks in C2:C20. Enter the rank formula in C2 and fill it down. Each row then compares its sales figure with the same complete sales range.

With sales figures in B2:B20 and the formula entered in C2, =RANK.EQ(B2,$B$2:$B$20,0) ranks the largest sales total as 1. If lower values should rank first, change the final argument to 1. Keep the comparison range fixed with dollar signs; otherwise, copying a relative range down can make each row compare against a different, unintended subset of the data.

Ranks do not rearrange the original records. A rank of 1 identifies the top value, but the row remains where it is. To display records in ranking order, sort the data by the rank column, or sort by the original measure. If you sort a table that contains names and values, sort the whole table so that names remain paired with their corresponding figures.

Common errors and details to check

  • The direction seems backward: An omitted or zero order means largest is rank 1. Use a nonzero order when smallest should be rank 1.
  • Ranks have gaps: This is normal when values tie. Use RANK.AVG if average positions are wanted, or create a separate tie-break rule if every row needs a unique rank.
  • The formula returns #N/A: Check that the value being ranked is actually present in the reference range and that the value is numeric. A value outside the comparison list can produce this error.
  • Some entries seem to be ignored: Non-numeric entries in the reference are ignored. Numbers stored as text may therefore not behave as intended; convert them to numeric values before ranking.
  • New rows do not affect the results: The formula only compares against the range it references. Expand that range when adding data, or use a suitably maintained Excel table reference.
  • The results change after copying: Confirm that the comparison range is absolute, for example $B$2:$B$20, while the individual value reference changes by row.

Dates are stored as numbers in Excel, so ranking dates ranks their underlying serial values: earlier dates are smaller and later dates are larger. Similarly, ranking percentages or currency amounts ranks their numeric values; the displayed formatting does not change the calculation. If the visible values appear equal because of rounding but the stored values differ, Excel may assign them different ranks. Round the source data only if that matches the intended analysis, since rounding can change the results.

Which function should you choose?

For a new workbook in Microsoft 365 on Windows, use RANK.EQ when equal values should share the same rank. It communicates the tie rule clearly and is available in Excel 2010 and later, including current Microsoft 365 versions. The formulas work in Excel for Mac and Excel for the web as well; the function names and ranking behavior are the same. Regional settings can affect whether a formula uses commas or another argument separator, so follow the separator Excel inserts in your installation.

Use RANK if you are maintaining a workbook for Excel 2007 or an earlier version, because those versions predate RANK.EQ. An older Excel installation may not recognize the newer function name. In a workbook used by a mix of versions, compatibility requirements matter more than the small naming advantage of RANK.EQ. For a modern workbook that will not be opened in older Excel, there is generally no reason to prefer RANK.

To choose quickly, consider these points:

  • Choose RANK.EQ for an explicit, equal-rank treatment of ties in current Excel.
  • Choose RANK when older-version compatibility is required.
  • Choose RANK.AVG when tied values should receive the average of their occupied positions.
  • Use a separate tie-break rule when each record must have a distinct position.

Frequently Asked Questions

Is RANK.EQ better than RANK?
They return the same result for the same inputs. RANK.EQ is usually clearer in new workbooks, while RANK can be useful when a workbook must also work in Excel 2007 or earlier.

Does RANK.EQ give tied values different ranks?
No. Tied values receive the same rank, and later ranks have a gap. Use RANK.AVG if ties should receive the average of their occupied positions.

How do I make the lowest value rank first?
Set the optional order argument to a nonzero value, usually 1. For example, with values in B2:B10 and the formula in C2, =RANK.EQ(B2,$B$2:$B$10,1) ranks the smallest value as 1.

Why does my rank skip a number?
A skipped rank usually means two or more entries are tied. For instance, two second-place values occupy the second and third positions, so the next value is ranked fourth.

Can RANK.EQ rank a value that is not in the range?
Excel can calculate the position of a numeric value relative to the reference values, but if the result is #N/A, verify that the value and range contain the expected numbers and that the value is included in the comparison list. Including the value’s own cell in the range is the clearest setup for a normal list ranking.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.