If you’ve ever been confused by Excel’s three different ranking functions, you aren’t alone. A rank is a rank, right? But they actually differ in how they handle ties and which versions of Excel they work in. Here is how to choose the right one for your next spreadsheet.

The legacy of the RANK function

Why Microsoft updated a spreadsheet classic

The original RANK function was the cornerstone of Excel standings for decades. It returns the position of a number within a list, but it’s now classified as a “compatibility” function. This means that while Microsoft has kept it in the software so that older workbooks created years ago don’t break, it’s no longer the recommended way to work, and Microsoft notes it may not be available in future versions of Excel.

The reason for the change wasn’t because the original RANK function was broken, but because it was too vague. In 2010, Microsoft introduced two clearer ranking functions to give users more control over how ties are handled. RANK.AVG was created to provide a new, nuanced way of averaging tied rank positions, and RANK.EQ was introduced as a direct partner. So, while RANK.EQ is mathematically identical to the old RANK, the new naming convention makes it clear that you’re choosing equal tie-handling instead of average tie-handling.

Even though it’s a compatibility function, I’ll cover it here for those using older versions of Excel. And even if you’re on the latest version of Microsoft 365, having this knowledge ensures you aren’t thrown off if someone sends you an older file that still uses it.

Here’s the syntax:

=RANK(number, ref, [order])

where number is the number whose rank you want to find, ref is a reference to a list of numbers, and order (optional) is a number that tells Excel to rank the number as if the reference were in a descending list (0 or omitted) or an ascending list (1).

Suppose you’re working on a pre-2010 version of Excel to manage a list of regional sales figures. You need to assign a rank to each salesperson based on their total revenue to see who’s leading the pack.

To do this, type this formula into the first cell of your Rank column and press Enter:

=RANK([@Revenue], [Revenue], 0)

This looks at the value in the current row’s Revenue column and compares it to the entire Revenue column. In this case, Mike and David both have $48,500, so they’re both ranked #3. Because two people occupy the third spot, Chloe is assigned a rank of 5.

RANK.EQ: The modern standard

Assigning top-tier positions to tied values

RANK.EQ is the modern replacement for the original RANK function and is usually the best choice for traditional leaderboards. The “EQ” stands for “equal,” meaning that tied values receive the same rank. If two people tie for first, they both get #1, and the next person in line is #3.

The syntax is the same as the RANK function:

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

This means that simply adding “.EQ” to the formula upgrades it to the modern version:

=RANK.EQ([@Revenue], [Revenue], 0)

Just like the legacy version, Mike and David are tied at rank 3, so Chloe is assigned rank 5.

RANK.AVG: The statistical average

Calculating the mean position for equal scores

RANK.AVG is primarily intended for statistical analysis. It handles ties by calculating the average of the positions the tied values would have occupied. This is a more mathematically balanced approach often used in academic grading, surveys, or scientific research, where skipping ranks can distort how the data distribution is interpreted.

The syntax is the same as its modern counterpart, except for a tweak to the function name:

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

Imagine you’re analyzing a customer satisfaction survey. Multiple customers have given the same score (4 out of 5), and you need to find the average rank of these scores to assess relative performance across responses without the top-heavy bias of a standard leaderboard.

To do this, type this formula into your Rank column:

=RANK.AVG([@Score], [Score], 0)

Since four people tied for the second, third, fourth, and fifth spots, RANK.AVG calculates the midpoint—(2 + 3 + 4 + 5)/4 = 3.5—meaning every customer who gave a score of 4 is ranked #3.5. This shows that the average position for a score of 4 is right in the middle of that cluster, providing a much more accurate picture of data density than a standard RANK.EQ would.

Handling the “order” argument

Deciding if the lowest or highest number wins

One of the most common mistakes with ranking in Excel is forgetting that rank #1 isn’t always the biggest number. All three ranking functions include an optional third argument—order. By default, Excel assumes you want a descending rank (highest is #1), but many scenarios—like race times—require the smallest number to be the winner.

To rank your data from lowest to highest, type 1 for the order argument:

=RANK.EQ([@Time], [Time], 1)

Notice how Chloe, who completed the lap in the quickest time, is ranked #1. By changing the final argument to 1, you tell Excel to use an ascending sort for the ranking logic. It’s a small detail, but it’s the difference between a functional ranking and an inverted set of results.

RANK, RANK.EQ, and RANK.AVG at a glance

Choosing the right tool for your data

Use this table to decide which function fits your dataset, logic, and compatibility.

While the legacy RANK function is still there for older files, moving to RANK.EQ or RANK.AVG gives you much more control over how equal scores impact your standings. This ensures your data remains accurate and professional regardless of ties. Identifying where your values sit relative to each other is just the first step—if you need to physically reorder your rows based on those standings, you can master dynamic organization by using the SORT and SORTBY functions.

OS

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.