RANK Function in Excel: Rank Scores and Sales

The RANK function in Excel tells you where each value stands in a list: 1st, 2nd, 3rd and so on. Today the main version is RANK.EQ, which ranks the largest value as 1 by default. This guide shows how to rank a sales team, how to rank smallest first, what happens with ties and how to break them, and how to turn ranks into a sorted leaderboard.

What does the RANK function do?#

RANK.EQ compares one number with a list and returns its position. It has three arguments:

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

There are three versions. RANK is the original and still works everywhere. RANK.EQ (Excel 2010 and later) gives the same results under a clearer name. RANK.AVG handles ties differently, as shown below.

The examples use this sales list in A1:B8:

How to rank values from highest to lowest#

Type the formula next to the first value, lock the list, and fill down.

1. Click C2 and type =RANK.EQ(B2,$B$2:$B$8).
2. Press Enter. Amira's rank is 3.
3. Fill down to C8 (Windows: Ctrl+D; Mac: ⌘D).

The ranks are: Amira 3, Ben 5, Chloe 2, Dev 5, Elena 1, Farid 4, Grace 7.

The $ signs are the key detail. Without them, the list slides down one row each time you fill, and the last rows are ranked against a shrinking list. Press F4 (Windows) or ⌘T (Mac) after typing a range to add the dollar signs.

How to rank from lowest to highest#

Add 1 as the third argument when a smaller number is better, such as race times, costs or error counts.

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

On the sales data, Grace becomes rank 1 and Elena rank 7. Ben and Dev share rank 2, so no one is 3rd.

How RANK handles ties#

Tied values get the same rank, and the next rank is skipped. Ben and Dev both sold 39,500, so both are 5th, and Grace is 7th. Nobody is 6th.

RANK.AVG gives tied values the average of the ranks they cover instead:

Use RANK.EQ for leaderboards and competitions. RANK.AVG is mostly used in statistics. If you share files with people on Excel 2007 or older, use plain RANK: it behaves like RANK.EQ and opens everywhere. Newer Excel keeps RANK for compatibility, so old workbooks still calculate.

How to break ties and get unique ranks#

Add a COUNTIF that counts how many times the value has already appeared above, and subtract 1.

=RANK.EQ(B2,$B$2:$B$8)+COUNTIF($B$2:B2,B2)-1

The trick is the half-locked range $B$2:B2. It grows as you fill down: B2:B2, then B2:B3, and so on. For Ben (row 3), the value 39,500 appears once so far, so he keeps rank 5. For Dev (row 5), it has appeared twice, so Dev gets 5 + 2 - 1 = 6. Every rank is now unique, which helps when you need to look people up by rank.

This rewards whoever is higher in the list. For a fairer tie-break, such as fewer sick days, rank on a second column instead.

RANK vs LARGE: position or value?#

RANK and LARGE answer opposite questions. RANK takes a value and returns its position. LARGE takes a position and returns the value.

Use LARGE and SMALL to build a top-3 list; use RANK to label every row. The lesson on LARGE and SMALL practises both, and the comparison of RANK vs LARGE goes deeper. To pull the names of the top performers, see how to build a top N list.

How to turn ranks into a sorted leaderboard#

In Excel 2021 or Microsoft 365, SORT can return the whole table ordered by sales, with no manual sorting.

=SORT(A2:C8,2,-1)

This sorts A2:C8 by its 2nd column (Sales), largest first (-1). The result spills into as many rows as needed, so leave empty space below the formula. Elena comes first, then Chloe, Amira and so on, with each rank beside the name. The SORT function guide explains every argument, and the SORT lesson lets you try it.

To sort by rank and then by name, read how to sort by multiple columns. For a list of distinct values in order, see unique sorted lists, and for choosing between the two sorting functions, see SORT vs SORTBY.

Common RANK mistakes#

Check these if the ranks look wrong:

1. No $ signs. The list shifts as you fill down. Lock it: $B$2:$B$8.
2. Wrong order. If the smallest value shows rank 1 by surprise, remove the 1 at the end.
3. Numbers stored as text. Text values are ignored, and ranking one returns #N/A.
4. Range too short. A value outside the list returns #N/A.
5. Expecting no gaps after ties. RANK.EQ skips ranks after a tie; use the COUNTIF tie-breaker if you need 1 to 7 with no repeats.

Practise it#

Ranking questions are common in data analyst screening tests, often combined with LARGE or SORT. Practise LARGE and SMALL in a live spreadsheet: you type the formula and it checks it instantly. Then practise SORT to build a leaderboard, and try the data analyst Excel test when you're ready.