Excel RANK and RANK.EQ Functions
last modified April 4, 2025
The RANK and RANK.EQ functions are essential for
determining the position of a value within a dataset. These functions help
analyze data by showing relative standing. This tutorial provides a
comprehensive guide to using both functions with detailed examples.
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.
| 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 integer |
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.
Basic RANK.EQ Example
This example demonstrates the simplest use of RANK.EQ with a small dataset of test scores.
| A | B |
|---|---|
| Student | Score |
| Alice | 85 |
| Bob | 92 |
| Carol | 78 |
| Dave | =RANK.EQ(B2, B2:B5) |
The table shows student test scores with a RANK.EQ formula in cell B5 that ranks Dave's score relative to others. Higher scores get better (lower) ranks by default.
=RANK.EQ(B2, B2:B5)
This formula ranks the value in B2 (85) against all values in B2:B5. The result will be 2, as 85 is the second highest score in the list. Ties receive the same rank.
RANK.EQ with Descending Order
By default, RANK.EQ assigns rank 1 to the largest value. You can reverse this by setting the optional order argument to 1.
| A | B | C |
|---|---|---|
| Product | Price | Rank |
| Widget | 19.99 | =RANK.EQ(B2, B2:B5, 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:B5, 1)
This formula ranks the price in B2 (19.99) in ascending order against B2:B5. The result will be 2, as it's the middle price. Setting order to 1 makes lower values rank higher.
RANK.EQ with Ties
When values tie, RANK.EQ assigns them the same rank and skips subsequent ranks. This example shows how ties are handled.
| A | B | C |
|---|---|---|
| Runner | Time | Rank |
| Alice | 10.2 | =RANK.EQ(B2, B2:B5) |
| Bob | 10.5 | |
| Carol | 10.2 | |
| Dave | 10.7 |
The table shows race times with two runners tying for first place. Both receive rank 1, and the next runner receives rank 3 (rank 2 is skipped).
=RANK.EQ(B2, B2:B5)
This formula ranks Alice's time (10.2) against all runners. Since Carol also has 10.2, both share rank 1. Bob's 10.5 gets rank 3, demonstrating how ties affect subsequent rankings.
RANK.EQ with Non-Adjacent Ranges
RANK.EQ can work with non-adjacent cell ranges by using named ranges or union references. This example uses a named range.
| 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 all values in the AnnualSales range (A2:B3). The result shows its position among all quarterly figures. Named ranges make formulas more readable.
RANK.EQ with Filtered Data
RANK.EQ considers all values in the reference range, even if filtered out. This example demonstrates this 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.
=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.
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 | Description | Tie Handling |
|---|---|---|
| RANK | Legacy function | Same as RANK.EQ |
| RANK.EQ | Current standard | Gives same rank, skips next |
| RANK.AVG | Alternative | Gives average rank for ties |
This table compares Excel's ranking functions. RANK.EQ is the
modern replacement for RANK, while RANK.AVG offers
different tie-breaking behavior.
The RANK and RANK.EQ functions are powerful tools
for data analysis in Excel. They help understand relative standing in datasets
of any size. Remember that RANK.EQ is the current standard, while RANK is
maintained for compatibility. For alternative tie handling, consider
RANK.AVG.
Author
List all Excel Formulas.