Guides / Excel

Excel Guide

Ranking Values in Excel: RANK, LARGE, SMALL

Sorting a column tells you the order, but it rearranges your data. These three functions let you ask "where does this rank?" or "what's the 3rd highest?" without touching the row order at all.

RANK: where does this value stand?

RANK tells you a number's position within a list — 1st, 2nd, 3rd, and so on — without sorting anything:

=RANK(A2, A2:A10) // rank, highest = 1 (default) =RANK(A2, A2:A10, 1) // rank, lowest = 1 (ascending)

The third, optional argument controls direction: leave it out (or use 0) to rank highest-first, like a leaderboard; use 1 to rank lowest-first, like a race time.

Ties: if two values are equal, RANK gives them the same rank — and skips the next one. Two people tied for 2nd means the next rank down is 4th, not 3rd.

LARGE and SMALL: the Nth highest or lowest

Where RANK answers "what position is this value?", LARGE and SMALL answer the reverse: "what value is at this position?"

=LARGE(A2:A10, 1) // the highest value =LARGE(A2:A10, 3) // the 3rd highest value =SMALL(A2:A10, 1) // the lowest value

This is the standard way to build a "top 3" or "bottom 5" summary without sorting the underlying data — useful when the row order needs to stay intact for other formulas referencing it.

A practical combination

LARGE and INDEX/MATCH together are a common pattern for "who scored the Nth-highest, and what was their score" — LARGE finds the value, MATCH finds which row it's in, and INDEX pulls the name from that row.

Try it yourself

Practice RANK, LARGE, SMALL and more with real interactive exercises and instant feedback.

Start practicing ranking formulas →

Related guides