Excel RANK and RANK.EQ Functions

last modified October 5, 2026

The RANK and RANK.EQ functions return the position of a value inside a list of numbers. The rank of a number is its size relative to the other values: sort the list and the rank is the number's place in it. Both functions are the backbone of scoreboards, leaderboards, and any report that asks how a figure compares with the rest. This tutorial provides a comprehensive guide with detailed examples. You'll learn the syntax and the arguments, why the order argument defaults to the direction most people do not expect, how ties share a rank and which ranks are skipped, how to pass the list to the function with a named range, and how to diagnose the common errors.

RANK/RANK.EQ Function Basics

The RANK and RANK.EQ functions return the rank of a number in a list of numbers. The rank is its size relative to other values in the list. The two names are interchangeable: they take the same arguments and return the same results. RANK.EQ was introduced in Excel 2010, while RANK is the legacy name that Microsoft keeps for backward compatibility.

Component	Description
Function Names	RANK (legacy), RANK.EQ (current)
Syntax	=RANK.EQ(number, ref, [order])
Arguments	number, ref, order (optional)
Return Value	Rank position as an integer
Related Function	RANK.AVG, which returns the average rank of tied values

This table breaks down the essential components of the ranking functions. RANK.EQ is the modern equivalent of the legacy RANK function. Both work identically.

Argument	Required	Meaning
number	Yes	The value whose rank you want to find. If it does not appear in ref, the function returns #N/A.
ref	Yes	A reference to the list of numbers to rank against. Text, logical values, and empty cells in ref are ignored, so a header row inside the range is harmless.
order	No	A number that selects the direction. 0 or an omitted value ranks in descending order; any nonzero value ranks in ascending order.

number is the value being judged and ref is the field it is judged against. Only numbers in ref take part: labels, logical values, and blanks are skipped, but they never raise an error either. The third argument decides which end of the list earns rank 1, and its default is the most common source of surprising results.

order value	Ranking direction	Rank 1 goes to
Omitted or 0	Descending	The largest number
1 or any nonzero value	Ascending	The smallest number

Leaving order out does not mean a neutral order; it means the largest value wins. Scores, sales, and other more-is-better measures are usually ranked with the default, while times, prices, ages, and every other measure where less is better need order set to 1. The examples below show both directions.

Basic RANK.EQ Example

This example demonstrates the simplest use of RANK.EQ with a small dataset of test scores. The Rank column holds the formula, which keeps the formula out of its own reference.

A	B	C
Student	Score	Rank
Alice	85	=RANK.EQ(B2, B2:B5)
Bob	92
Carol	78
Dave	72

The table shows student test scores with a RANK.EQ formula in cell C2 that ranks Alice's score against the whole class. Because order is omitted, the highest score takes rank 1.

=RANK.EQ(B2, B2:B5)

This formula ranks the value in B2 (85) against every value in B2:B5. The result is 2: 92 is higher, while 78 and 72 are lower, so 85 is the second largest number in the list. Copy the formula down and Bob takes rank 1, Carol rank 3, and Dave rank 4. Values that are equal would all receive the same rank.

RANK.EQ with Ascending Order

By default, RANK.EQ assigns rank 1 to the largest value. You can reverse this by setting the optional order argument to 1, which gives rank 1 to the smallest value instead.

A	B	C
Product	Price	Rank
Widget	19.99	=RANK.EQ(B2, B2:B4, 1)
Gadget	24.99
Thingy	14.99

This table demonstrates ranking prices in ascending order (lowest price gets rank 1). The order argument (1) changes the ranking direction from default.

=RANK.EQ(B2, B2:B4, 1)

This formula ranks the price in B2 (19.99) in ascending order against B2:B4. The result is 2, because 14.99 is cheaper and 24.99 is dearer. Setting order to 1 makes lower values rank higher: with the default order, Thingy's 14.99 would take rank 3, but here it takes rank 1. Any nonzero value works, so order 2 behaves exactly like order 1, and only 0 or an omitted argument gives the descending ranking.

RANK.EQ with Ties

When values tie, RANK.EQ assigns them the same rank and skips the ranks that follow, which is the usual competition ranking. Because a lower time wins a race, this example passes order 1 so that the fastest runners take the top ranks.

A	B	C
Runner	Time	Rank
Alice	10.2	=RANK.EQ(B2, B2:B5, 1)
Bob	10.5
Carol	10.2
Dave	10.7

=RANK.EQ(B2, B2:B5, 1)

This formula ranks Alice's time (10.2) against all runners. Since Carol also ran 10.2, both share rank 1. The tie consumes rank 2 as well, so Bob's 10.5 receives rank 3 and Dave's 10.7 receives rank 4. With the default order the two 10.2 times would instead be tied at rank 3, because the default ranks from the largest value down.

Time	RANK.EQ rank	RANK.AVG rank
10.2	1	1.5
10.2	1	1.5
10.5	3	3
10.7	4	4

The table compares the two tie policies on the same data. RANK.EQ returns the top rank of the tied set and then skips ahead, producing 1, 1, 3, 4. RANK.AVG instead averages the positions the tied values occupy: the two 10.2 times sit in places 1 and 2, so each becomes 1.5, and the remaining ranks are unchanged.

RANK.EQ with Named Ranges

ref must describe a single list of numbers, and a named range is the cleanest way to hand that list to the function, especially when the data is reused in many formulas.

A	B	C
Q1	Q2	Annual Rank
1500	1800	=RANK.EQ(A2, AnnualSales)
2200	1900

The table assumes cells A2:B3 are named "AnnualSales". The RANK.EQ formula references this named range to rank Q1 sales against all quarterly sales data.

=RANK.EQ(A2, AnnualSales)

This formula ranks the Q1 value (1500) against the four quarterly figures in AnnualSales. The result is 4: 1800, 2200, and 1900 are all higher, so 1500 is the smallest number in the list. The name must point at one block of cells; a name that stitches together several separate areas makes the function return #N/A, so collect scattered values in a single block first. Named ranges make formulas more readable and easier to maintain.

RANK.EQ with Filtered Data

RANK.EQ considers every value in ref, including rows hidden by a filter or by the Hide command. This example demonstrates that behavior.

A	B	C
Region	Sales	Rank
North	5000	=RANK.EQ(B2, B2:B5)
South	7500
East	6000
West	5000

Even if rows are filtered out, RANK.EQ still includes those values in ranking calculations. Here, North and West tie at 5000 sales, both receiving rank 3 behind South (7500) and East (6000).

=RANK.EQ(B2, B2:B5)

This formula ranks North's sales (5000) against all regions. The result is 3 (shared with West), showing filtered data is still included in ranking calculations. Hiding or filtering South out of view would not change North's rank either, because the function reads every number in ref no matter what is on the screen.

Finding Ranks Without Ties

RANK.EQ is a competition ranking: equal values share a rank and the rank afterwards is skipped. When every row must carry its own position, for example before a lookup that matches on rank, a small COUNTIF counter separates the duplicates.

A	B	C
Region	Sales	Unique Rank
North	5000	=RANK.EQ(B2,$B$2:$B$5)+COUNTIF($B$2:B2,B2)-1
South	7500
East	6000
West	5000

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

The reference of RANK.EQ is locked with $ so it stays on the full list, while the second argument of COUNTIF grows as the formula is copied down: in C2 it covers B2:B2, in C3 it covers B2:B3, and so on. The counter therefore reports how many times the current value has already appeared, so the first 5000 adds 0 and the second 5000 adds 1. The results are South 1, East 2, North 3, and West 4: every rank from 1 to 4 is used exactly once and no position is missing.

If you would rather not pick an order among equals, use RANK.AVG instead. It gives both 5000 rows rank 3.5, the average of positions 3 and 4, and leaves the gaps in the sequence untouched. The choice is between unique positions (the COUNTIF tie-break) and honest averages (RANK.AVG).

RANK vs RANK.EQ vs RANK.AVG

Excel offers three ranking functions with subtle differences in handling ties. This comparison clarifies when to use each.

Function	Available since	Description	Tie Handling
RANK	All versions	Legacy name, kept for compatibility	Same as RANK.EQ: same rank, next rank skipped
RANK.EQ	Excel 2010	Current standard for competition ranking	Same rank for equals, following rank skipped
RANK.AVG	Excel 2010	Averages the positions of equal values	Average rank, for example 1.5 for two values in places 1 and 2

This table compares Excel's ranking functions. RANK and RANK.EQ are interchangeable: identical syntax, identical results, including the descending default and the skipped ranks after a tie. RANK.EQ is the name Microsoft recommends for new work, while RANK remains available for backward compatibility and may not be kept in future versions. RANK.AVG takes the same three arguments and reads order in the same way; it differs only in how it treats equals.

=RANK.EQ(5000,B2:B5)      ' 3, competition rank
=RANK.AVG(5000,B2:B5)     ' 3.5, averaged rank
=RANK.EQ(5000,B2:B5)+(COUNT(B2:B5)+1-RANK.EQ(5000,B2:B5,0)-RANK.EQ(5000,B2:B5,1))/2   ' 3.5

On the sales data from the previous examples, the two 5000 rows are tied for third and fourth place. RANK.EQ returns the top of that set, 3; RANK.AVG returns the average, 3.5. The third formula is the correction factor documented by Microsoft: it adds (COUNT(ref)+1-RANK.EQ(number,ref,0)-RANK.EQ(number,ref,1))/2 to a competition rank, so 3 becomes 3 + 0.5 = 3.5. The factor is 0 whenever the value occurs only once, so unique values are left alone, and it works with either direction of the order argument. Use it when you want averaged ranks while keeping the RANK.EQ name in the formula.

RANK Errors and Troubleshooting

Cause	Symptom	Fix
number does not appear in ref	#N/A error	Confirm the value is really in the range; numbers stored as text do not match numbers stored as values
ref is assembled from several separate areas	#N/A error	Rank against one contiguous range, or a name that covers a single block of cells
order was left out when the smallest value should win	Rank 1 goes to the largest value	Pass 1, or any nonzero number, for ascending order
The values contain ties	A rank repeats and the next rank is missing	Use RANK.AVG for averaged ranks, or the COUNTIF tie-break for unique positions
The formula sits inside its own ref	Circular reference warning	Keep the rank formula in another column, outside the ranked range
Numbers are stored as text in ref	Those cells are skipped, ranks come out too large	Convert the text to numbers, for example with VALUE or Text to Columns
A cell in ref contains an error	The formula returns that error instead of a rank	Repair the source value, for example with IFERROR, before ranking
Rows are filtered or hidden	Ranks ignore the filter	RANK.EQ reads every cell in ref; rank a helper column of visible rows with SUBTOTAL if only those rows should count

The RANK and RANK.EQ functions turn raw numbers into positions, and once three rules are in mind they rarely mislead. The order argument defaults to descending, so rank 1 goes to the largest value and only a nonzero order reverses it. Tied values share the top rank of their set and the ranks after them are skipped, which is exactly what RANK.AVG smooths into an average and what the COUNTIF tie-break replaces with unique positions. Finally, ref reads numbers only: text, blanks, and logical values slip past unnoticed, a missing number or a reference built from separate areas ends in #N/A, and an error inside the range is passed straight through. Choose the direction deliberately, decide in advance how ties should behave, and keep the formula out of its own reference; your rankings will be right the first time.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.