Excel Rank Values in Range: RANK, RANK.EQ, RANK.AVG

How-to basics · Updated November 22, 2026

To rank values in an Excel range, use the RANK.EQ function (or the older RANK function) with the syntax =RANK.EQ(value, range, [order]) – for example, =RANK.EQ(B2, $B$2:$B$10) ranks the number in B2 against all numbers in B2:B10, with the largest getting rank 1. If you need ties to share the same rank, use RANK.AVG instead, which assigns the average rank to tied values.

Background: Why Ranking in Excel Matters

Ranking is a common analytical task – you might want to see which salesperson performed best, which student scored highest, or which product sold the most. Excel has provided ranking functions for decades, but the original RANK function was replaced in Excel 2010 by two more precise versions: RANK.EQ (equivalent to the old RANK) and RANK.AVG. The old RANK still works for backward compatibility, but Microsoft recommends using the newer functions. Knowing which function to use and how to handle ties, sorting, and dynamic ranges can save you hours of manual work.

How RANK.EQ and RANK.AVG Work

Both functions take three arguments: the number you want to rank, the range of numbers to compare against, and an optional order parameter. If you omit the order or set it to 0, Excel ranks in descending order (largest value gets rank 1). If you set order to any non-zero number (usually 1), Excel ranks in ascending order (smallest value gets rank 1).

For example, with values in A2:A5: 10, 20, 20, 30:

  • =RANK.EQ(20, A2:A5) returns 2 (both 20s get rank 2, and the next rank is skipped – 30 gets rank 1, 10 gets rank 4).
  • =RANK.AVG(20, A2:A5) returns 2.5 (the average of ranks 2 and 3).

The range can be a single column or row, but it must contain numbers – text or blanks are ignored. If the value you’re ranking isn’t in the range, the function still returns a rank as if it were inserted, which can be useful for “what-if” analysis.

How to Choose Between RANK.EQ and RANK.AVG

Your choice depends on how you want ties handled. If you’re ranking for a competition where tied contestants should share the same place (and the next place is skipped), use RANK.EQ. If you’re calculating statistical percentiles or need a continuous rank that averages ties, use RANK.AVG. For most business reporting, RANK.EQ is the standard. For example, if two salespeople have identical sales, RANK.EQ gives both rank 2, and the next person gets rank 4 – this is the classic “1, 2, 2, 4” pattern. RANK.AVG would give both 2.5, and the next person rank 4, which is more statistically correct for some analyses.

Common Problems and How to Fix Them

One frequent issue is that the range reference isn’t locked, causing errors when you copy the formula down. Always use absolute references (with $ signs) for the range, like $B$2:$B$10, so the range stays constant. Another problem is ranking with duplicates when you want unique ranks – for that, you can use a helper column with COUNTIF or use RANK.EQ plus a tiebreaker. For example, to break ties by a second column (e.g., earlier date wins), you can add a tiny decimal to the value: =RANK.EQ(B2, $B$2:$B$10) + COUNTIF($B$2:B2, B2)/100 – but this changes the rank value, so it’s better to use a separate tiebreaker column. Also, note that RANK functions ignore empty cells and text, but if you have errors (like #N/A) in the range, they will propagate to the result – you may need to clean your data first.

Ranking with Conditional Formatting and Dynamic Arrays

Ranking is often used alongside conditional formatting to highlight top performers. For example, to highlight the top 3 values in a range, select the range and go to Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items, then change 10 to 3. This uses a built-in rule that doesn’t require a formula. If you need more control, you can use a formula rule like =RANK.EQ(B2, $B$2:$B$10) <= 3 to highlight the top 3. In Excel 365, you can also use dynamic array functions like SORT to create a sorted list with ranks, but the classic RANK functions remain the simplest for a single formula.

Alternatives to RANK Functions

Besides RANK, you can achieve ranking with other functions. The LARGE and SMALL functions return the nth largest or smallest value, which can be used to manually assign ranks. For example, =LARGE($B$2:$B$10, 1) gives the highest value, and you could compare each value to that. The PERCENTRANK function returns a value's relative standing as a percentage, which is useful for percentile rankings. In Excel 365, you can use XMATCH with SORT to generate a rank column, but that's more complex. For simple ranking, stick with RANK.EQ or RANK.AVG.

Frequently Asked Questions

How do I rank in Excel without skipping numbers?

If you want tied values to share the same rank and the next rank to be consecutive (e.g., 1, 2, 2, 3), use RANK.AVG – but note that it gives fractional ranks for ties. If you want integer consecutive ranks, you need a helper column with a formula like =SUMPRODUCT((A2>A$2:A$10)/COUNTIF(A$2:A$10,A$2:A$10))+1 – this gives a dense rank (1, 2, 2, 3). That formula counts unique values greater than the current value, then adds 1.

Can I rank a column in Excel automatically?

Yes, you can use the RANK function in a new column next to your data. For example, if your values are in B2:B10, put =RANK.EQ(B2, $B$2:$B$10) in C2 and drag down. Excel will fill the ranks for each row. If you want to sort by rank, you can then sort the entire range by the rank column.

Why is my RANK function returning #N/A?

#N/A usually means the value you're trying to rank is not a number, or the range contains errors. Check that the value cell contains a number, not text. Also, if the range includes cells with errors (like #DIV/0!), the RANK function will return an error. Clean your data or use IFERROR to handle it.

What's the difference between RANK and RANK.EQ?

There is no difference in behavior – RANK.EQ is the modern replacement for RANK. Both return the same results. RANK is still available for compatibility with older Excel files, but Microsoft recommends using RANK.EQ. In Excel 2010 and later, you can use either; in older versions, only RANK exists.

How do I rank in descending order (smallest gets rank 1)?

Use the third argument of the RANK function. Set it to 1 (or any non-zero number) to rank in ascending order. For example, =RANK.EQ(B2, $B$2:$B$10, 1) gives rank 1 to the smallest value. If you omit the argument, Excel defaults to descending order.

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.