This places each value against the rest: first, second, third, and so on. Handy for leaderboards, top-performer lists, or any ordering by size.
The formula
=RANK(B2,B:B) in column C
What each part does
B2
The value to rank, taken from this row.
B:B
The list it is ranked against: the whole of column B. Every value is compared to these.
(nothing)
The last part is left off, so the largest value is ranked first. Put a 1 there to rank the smallest first instead.
How it works, and what to watch for
The range is locked with dollar signs so every row ranks against the same full list. Leave them off and the list shrinks as you fill down, throwing the ranks out.
A zero at the end ranks largest first. Swap it for a one and the smallest value takes first place.
Ties share a rank, and the next rank is skipped. If you need ties broken, COUNTIF can nudge the duplicates apart.
Questions
Two values tied and it skipped a rank, why?
That is how RANK works: equal values share a place, and the next one is skipped. Adding a small COUNTIF adjustment gives every row a unique rank if you need one.
How do I rank smallest first?
Change the last argument to 1. Leaving it as 0, or leaving it out, ranks largest first.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
