How to rank numbers in Excel

Method 1: COUNTIF rank (highest = 1)

A rank is just "how many people beat me, plus one". COUNTIF counts the sales greater than this row, so the top seller gets 0 + 1 = 1. Put it in D2 and fill down to D9.

The dollar signs lock the range so it does not slide down as you fill. Without them, the last row would only compare itself against itself and every rep would look like a winner.

Method 2: RANK.EQ and RANK.AVG

Excel has built-in ranking functions, available since Excel 2010. =RANK.EQ(C2,$C$2:$C$9) works exactly like the COUNTIF formula above: tied values share the best rank and the next rank is skipped, so Marcus and Tom are both 3rd and Priya is 5th. The older RANK function does the same thing and still works for compatibility.

=RANK.AVG(C2,$C$2:$C$9) gives tied values the average of the ranks they cover. Marcus and Tom occupy places 3 and 4, so both get 3.5. Sports tables and many statistics methods expect this version.

The optional third argument sets the direction: leave it out (or use 0) for highest first, use 1 for lowest first. The second argument must be a real range on the sheet, not a typed list. Pick RANK.EQ when you just want a leaderboard; pick the COUNTIF version when you need extra conditions, because COUNTIFS can add them.

Rank the lowest value first

For delivery times, costs, golf scores or error counts, smaller is better. Flip the comparison to "<" so you count the values below this one. With RANK.EQ you would add 1 as the third argument instead.

Average rank for ties (like RANK.AVG)

If you need the averaged tie rank in a formula you can build on, count the values above, then add the middle position of the tied block. COUNTIF($C$2:$C$9,C3) is the number of tied values, including this one.

Break ties so every rank is unique

Bonus tables and "top 3" lookups often need ranks 1 to 8 with no repeats. Add a second COUNTIF over an expanding range ($C$2:C3, with only the start locked). It counts how many times this value has appeared so far, so the first of two tied reps gets the better rank.

This breaks ties by row order. If you want a fairer tie-break, such as fewer returns or an earlier close date, sort by that column first.

Rank within each region

RANK.EQ cannot filter by group, but COUNTIFS can. Add a condition that the region matches this row, so each rep is only compared with their own region.