Statistics function

Excel RANK.EQ function

Q: How do I rank in Excel without duplicates?

Use =RANK.EQ(B2,$B$2:$B$9)+COUNTIF($B$2:B2,B2)-1 in row 2 and fill down. The first copy of a value keeps its rank and each repeat below it gets the next place.

Q: How do I rank from lowest to highest?

Add 1 as the third argument: =RANK.EQ(B2,$B$2:$B$9,1). The smallest value is then 1st.

RANK.EQ returns the position of a number in a list: 1 for the largest, 2 for the next, and so on, or the other way round. Tied values share the best place, so two people in 3rd place are followed by 5th. RANK.AVG gives ties the average of the places they fill instead, and the older RANK works exactly like RANK.EQ.

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

Arguments

number: The number to rank, usually a cell in the list.
ref: The list to rank against. It must be a cell range, not a typed list. Text and empty cells are ignored.
order: Optional. 0 or left out ranks the largest value 1st. Any other number, such as 1, ranks the smallest 1st.

RANK.EQ examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

	A	B
1	Rep	Sales ($)
2	Alvarez	48200
3	Brooks	51900
4	Chen	39400
5	Diaz	51900
6	Evans	45000
7	Fox	62300
8	Gupta	39400
9	Hale	57100

Download this sheet as an Excel practice file (.xlsx)

=RANK.EQ(B2,$B$2:$B$9)
Result: 5
Alvarez is 5th: four reps sold more. Lock the list with $ before you fill the formula down.
=RANK.EQ(B4,$B$2:$B$9,1)
Result: 1
With order 1 the smallest is 1st. Chen and Gupta tie on the lowest sales, so both are 1st.
=RANK.EQ(B3,$B$2:$B$9)&" or "&RANK.AVG(B3,$B$2:$B$9)
Result: 3 or 3.5
Brooks and Diaz tie for 3rd and 4th. RANK.EQ gives both 3; RANK.AVG gives both 3.5.
=RANK.EQ(B5,$B$2:$B$9)+COUNTIF($B$2:B4,B5)
Result: 4
A unique rank: add how many times the value appeared above. Brooks keeps 3rd and Diaz, lower in the list, becomes 4th.

How to use RANK.EQ

Click the first cell of a rank column.
Type =RANK.EQ(B2,$B$2:$B$9) with the list locked by $.
Add ,1 before the closing bracket if the smallest value should be 1st (fastest time, lowest cost).
Fill the formula down. In Excel 365, =RANK.EQ(B2:B9,B2:B9) ranks the whole list at once.

Common RANK.EQ mistakes

Forgetting the $ signs

Without $B$2:$B$9 the list moves as you fill down, and the lower rows rank against fewer and fewer values.

Expecting no gaps after a tie

Two reps in 3rd place mean nobody is 4th. Use the COUNTIF trick for unique ranks, or RANK.AVG if ties should share an average place.

Passing a computed list

ref must be a cell range. A typed list such as {1,2,3} or a FILTER result is not accepted; put the values in cells, or use COUNTIF(list,">"&x)+1.

RANK.EQ FAQ

What is the difference between RANK, RANK.EQ and RANK.AVG?

RANK and RANK.EQ are the same: ties share the best place. RANK.AVG gives ties the average of the places they would fill, so a two-way tie for 3rd shows 3.5 for both.

How do I rank in Excel without duplicates?

Use =RANK.EQ(B2,$B$2:$B$9)+COUNTIF($B$2:B2,B2)-1 in row 2 and fill down. The first copy of a value keeps its rank and each repeat below it gets the next place.

How do I rank from lowest to highest?

Add 1 as the third argument: =RANK.EQ(B2,$B$2:$B$9,1). The smallest value is then 1st.

Why does RANK.EQ return #N/A?

The number is not in the list, often because it was rounded or stored as text. RANK.EQ only ranks values that appear in ref.

Guides that use RANK.EQ

Related functions

Updated November 4, 2026