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
- How to Rotate a Table in Excel: 3 Easy Ways
- Excel How to Select Certain Cells: 6 Quick Ways
- Excel Transpose: How to Quickly Flip Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Number Format: How to Show Zero as a Dash in CellsYes. In Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, choose Custom, and enter #,##0;(#,##0);"-";@.…
- How to Archive Emails in Outlook on a MacBook: StepsTo archive an email in Outlook on a MacBook, select the message and choose Archive on the Outlook…
- Remove Formatting Marks in Word: Hide the ¶ SymbolsTo remove formatting marks from view in Word, go to Home > Paragraph > Show/Hide ¶ and click…
- Can You Shade Half a Cell in Excel? Options ExplainedExcel does not have a built-in cell format that fills exactly half of a cell with one color…
- Hide Columns Based on Cell Values in Excel AutomaticallyYes. In Excel for Microsoft 365 on Windows, use a worksheet VBA event to hide or show a…
- Excel Insert Row Greyed Out? Causes and FixesIn Excel for Microsoft 365 on Windows, Insert Row is usually greyed out because the worksheet is protected,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.