RANK vs LARGE in Excel: Position vs Value

The short answer to RANK vs LARGE in Excel: they work in opposite directions. RANK takes a value and tells you its position in a list, such as 3rd. LARGE takes a position and gives you the value in that spot, such as the third highest number. This comparison is for anyone building leaderboards, sales rankings or top-5 reports, and for anyone who has mixed the two up in a test.

RANK vs LARGE at a glance#

RANK turns values into positions; LARGE turns positions into values.

How RANK and LARGE work#

Both functions look at the same list of numbers; they just take and return different things.

RANK: from value to position#

RANK returns the position of a number within a list of numbers.

=RANK(number, ref, [order])

• number is the value to rank, usually the cell on the same row.
• ref is the whole list. Lock it with dollar signs, such as $B$2:$B$7, so it doesn't move when you fill down.
• order is optional. Leave it out (or use 0) to rank the highest value as 1. Use 1 to rank the lowest value as 1, which suits race times or costs.

RANK.EQ works exactly like RANK. RANK.AVG differs only with ties, as shown below. The guide to the RANK function covers both in more depth.

LARGE: from position to value#

LARGE returns the k-th largest value in a range.

=LARGE(array, k)

• array is the range of numbers.
• k is the position you want: 1 for the highest, 2 for the second highest, and so on.

=LARGE(B2:B7,1) gives the same result as MAX. SMALL is the mirror image: =SMALL(B2:B7,1) gives the lowest value, like MIN. The comparison of MAX vs LARGE explains when each fits, and the LARGE and SMALL lesson practises both.

The same sales list with RANK and LARGE#

Here are six reps and their sales:

RANK fills a column with each rep's position. Kofi and Ana are tied on 880, so both get 3, and no one gets 4. Priya, the next value, gets 5.

Now use LARGE to list the values from the top:

LARGE doesn't skip anything: the tied value 880 appears at positions 3 and 4. Asking for k = 7 returns #NUM!, because there are only six numbers. The guide to the #NUM! error lists other causes.

The two are inverses#

Because they work in opposite directions, each undoes the other. =RANK(910,$B$2:$B$7) returns 2, and =LARGE($B$2:$B$7,2) returns 910. Thinking of them as a pair makes it easy to remember which is which: RANK answers "where?", LARGE answers "what?"

Showing names, not just numbers#

LARGE returns values, but a top-N report usually needs names. Combine LARGE with INDEX and MATCH:

=INDEX($A$2:$A$7,MATCH(LARGE($B$2:$B$7,2),$B$2:$B$7,0))

This returns Mei, the rep with the second highest sales. LARGE finds 910, MATCH finds its row, and INDEX returns the name.

Ties cause a problem here. For k = 3 and k = 4, LARGE returns 880 both times, and MATCH finds the first 880 both times, so Kofi appears twice and Ana not at all. In Excel 2021 or Microsoft 365, SORTBY avoids this:

=INDEX(SORTBY($A$2:$A$7,$B$2:$B$7,-1),4)

This returns Ana, the fourth name in a list sorted by sales. The guides to top-N lists and LARGE and SMALL for top N show complete layouts.

Breaking ties in RANK#

If every row needs a unique rank, add a running count of earlier duplicates:

=RANK(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1

Kofi (row 5) stays at 3 and Ana (row 7) becomes 4, because her 880 is the second one in the list. The mixed reference $B$2:B2 grows as you fill down. To share ties fairly instead, use RANK.AVG, which gives both 880s a rank of 3.5.

When to use RANK#

Use RANK when every row needs its position. It fits:

• Leaderboards and league tables, with a rank column next to each name.
• Performance reviews, such as ranking stores or reps by sales.
• Flagging the top group, such as =IF(RANK(B2,$B$2:$B$7)<=3,"Top 3","").
• Lowest-is-best rankings, such as race times or costs, using order 1.
• Readable positions, such as showing that a rep is 2nd of 6, by joining the rank and a count: =RANK(B2,$B$2:$B$7)&" of "&COUNT($B$2:$B$7).

When to use LARGE#

Use LARGE when you need values from a chosen position. It fits:

• Top-N lists, such as the three highest sales: k = 1, 2 and 3.
• The k-th highest value, such as the second best score for a tie-break rule.
• Totals of the top values: =SUM(LARGE(B2:B7,{1,2,3})) adds the top three, 2,740 here.
• Thresholds, such as "anyone at or above the 3rd highest score qualifies".

Common mistakes with RANK and LARGE#

1. Using one when you need the other. If the question asks for a position, use RANK; if it asks for a value, use LARGE.
2. Not locking the RANK list. Without $B$2:$B$7, the range shifts as you fill down and the ranks go wrong.
3. Forgetting how ties work. RANK skips the next position after a tie, so a list can have no rank 4.
4. Using k outside the range. LARGE needs k from 1 to the count of numbers, or you get #NUM!.
5. Looking up names with MATCH when values tie. The first match is returned every time. Use SORTBY or a tie-breaker column.
6. Ranking text or blanks. RANK and LARGE ignore text and empty cells in the list. Numbers stored as text are left out, which can make ranks look wrong.

Our recommendation#

Use RANK to label rows with their position, and LARGE to pull out values by position. For a full leaderboard, you often need both: RANK for the column next to each name, and LARGE (or SORTBY in newer Excel) for a top-N summary above it. Decide early how ties should be handled, and use RANK.AVG or a tie-breaker formula if shared ranks would confuse readers.

In short: RANK answers "where does this value stand?", LARGE answers "what value stands here?"

Practise it#

Try the exercise on this page: rank a rep's sales against the whole list. Then practise LARGE and SMALL in a live spreadsheet: you type each formula, and it checks your answer instantly.