RANK Formula Generator
Describe what you want ranked and in which direction, in plain English, and get a ready-to-use RANK.EQ formula for Excel or Google Sheets.
5 free tries without an account
RANK.EQ syntax
=RANK.EQ(number, ref, [order])number is the value to rank, ref is the full range it's being ranked against (usually locked with $ signs so it doesn't shift when copied), and order controls direction — 0 (or omitted) for largest-is-1, any non-zero value for smallest-is-1. Formulon locks the range and sets the direction correctly based on what you describe.
Examples
Rank sales figures highest to lowest
=RANK.EQ(B2,$B$2:$B$20,0)Ranks the value in B2 against the full range B2:B20, with the largest sales figure getting rank 1. The range is locked with $ signs so it doesn't shift when copied down to other rows.
Rank race times, fastest (smallest) first
=RANK.EQ(B2,$B$2:$B$20,1)Same setup, but the 1 at the end flips it to ascending order — the smallest time in the range gets rank 1, which makes sense for something like race results where lower is better.
Frequently asked questions
What's the difference between RANK, RANK.EQ, and RANK.AVG?
RANK.EQ is the modern version of the older RANK function — both give tied values the same (top) rank and then skip the next rank number. RANK.AVG instead gives tied values the average of the ranks they'd otherwise occupy (e.g. two people tied for 2nd both get 2.5).
How do ties get handled?
With RANK.EQ, if two values are tied for 2nd place, they both show rank 2, and the next distinct value is ranked 4 (3 is skipped). Use RANK.AVG if you want ties to split the difference instead (both showing 2.5).
How do I rank from smallest to largest instead of largest to smallest?
Set the optional 3rd argument (order) to 1 (or any non-zero value) for ascending rank — smallest value ranked 1. Leave it as 0 or omit it for the default descending rank, where the largest value is ranked 1.
Is RANK the same in Google Sheets?
Yes, Google Sheets supports RANK, RANK.EQ, and RANK.AVG with the same arguments and behavior as Excel.
Don't want to write it by hand?
Describe what you need in plain English and get a ready-to-use formula in seconds.
Try the formula generator