Excel RANK Formula: rank scores or sales from highest to lowest

RANK tells you where a number stands in a list. The highest score gets 1, the next gets 2, and so on.

The formula

=RANK.EQ(number, ref, [order])

• number — the cell you want to rank.
• ref — the whole list. Lock it with $ so it does not move when you copy down.
• order — 0 or blank = highest is 1. 1 = lowest is 1 (good for times or costs).

Example

Scores in B2:B4. In C2:

=RANK.EQ(B2,$B$2:$B$4)

Copy down. Ravi (95) is 1, Asha (88) is 2, Meena (72) is 3.

Forgot the $ signs? The list shrinks as you copy and ranks go wrong. See absolute reference.

Ties

Two people on 88 both get rank 2, and the next person gets 4. That is how sports rankings work. If you want no gaps or no duplicates:

• Unique rank (break ties by order in the list):
  =RANK.EQ(B2,$B$2:$B$10)+COUNTIF($B$2:B2,B2)-1
• Average rank for ties (2.5, 2.5): =RANK.AVG(B2,$B$2:$B$10)

Rank within a group

Rank each salesperson inside their own region (region in A):

=COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,">"&B2)+1

Just want the top 3 values?

Use LARGE instead: =LARGE(B2:B10,1), ,2), ,3).