How to use the RANK.EQ function in Microsoft Excel
Manually re-sorting your Excel tables every time a score changes is a waste of time. The RANK.EQ feature automates rankings by instantly assigning positions. Whether you’re tracking sales or race times, here’s how to keep your data dynamic and accurate.
Previously, RANK was Excel’s only filing function. However, in 2010, Microsoft introduced RANK.EQ (identical to the old RANK) and MEDIUM RANK (a new option that returns the average tie ranking). In this guide I will use RANK.EQ, as it is the modern standard and works exactly the same as RANK.
How the RANK.EQ function works
Understand the syntax
The RANK.EQ feature identifies where a number ranks relative to others in a list, providing a “live” ranking that updates automatically. The function follows a simple syntax:
=RANK.EQ(number, ref, [order])
Or:
- number (required) is the value you want to rank.
- ref (required) is the range or array of numbers you are comparing the value with. Non-numeric values (like text or spaces) are ignored.
- order (optional) determines the ranking direction. Use 0 (or leave it blank) for descending rank, or 1 for ascending rank.
If several values have the same rank, RANK.EQ assigns them the first rank of this group. For example, if two people are tied for the highest score, they both receive rank 1 and the next highest score receives rank 3.
The RANK function works best with Excel tables because they automatically handle range expansion and structured references. However, if you must use a regular cell range, lock your ref using dollar signs, such as $B$2:$B$10.
To follow along with your reading, download a free copy of the Excel workbook used in the examples. After clicking the link, you will find the download button in the upper right corner of your screen and when you open the file, you can access each use case in a separate spreadsheet tab.
Use case 1: Create a dynamic leaderboard
Classify entries without rearranging rows
RANK.EQ identifies the best performers while keeping your data in its original order.
The scenario: You have an Excel table named T_Sales. It includes an ID column representing the order in which the details were signed. You want the filing to be in column A so the filing is visible, but you need to keep the table sorted by ID to match your physical receipts.
To generate these rankings, type this formula in the cell A2 and press Enter:
=RANK.EQ([@Revenue], [Revenue], 0)
Here’s how the formula works:
- [@Revenue] examines the income of the current line. For ID 101 it sees 52,000.
- [Revenue] compares this value to each number in the entire column.
- 0 guarantees that the person with the highest income (Elena) is assigned rank 1. You can leave that out for a cleaner formula.
Why not just use sort by table?
It’s tempting to sort from largest to smallest using the filter arrow at the top of the Income column. However, aside from messing up your original ID command, this is a static solution to a dynamic problem. As soon as you add a new sale or update a turnover, you will need to reapply or keep the sort.
By using RANK.EQ you create a “set it and forget it” system. It allows you to keep your main table in its original order and the formula extends downward for any new rows you add, meaning the ranking is updated instantly.
Get the most out of your data
To make the result even more meaningful, use the rank in column A as the driver of a SORTBY formula to automatically display your data from rank 1 down.
In an empty cell, type:
=SORTBY(T_Sales, T_Sales[Rank], 1)
This creates a dynamic copy of your table that remains ranked from 1 to 5. Even though your source table remains sorted by ID, this new list ensures that rank 1 is always on top. As soon as the data changes, this list updates automatically.
Use Case 2: Ranking Fastest Times
Handle scenarios where the lowest number wins
By returning the order argument to the RANK.EQ function, you can ensure that the smallest numbers are recognized as the “winners”.
The scenario: You track lap times in a named table T_Results. You need to keep this table in its original starting grid order (StartPos), but you still want to know who currently has the fastest lap.
To order them from fastest to slowest, type this formula in the cell B2:
=RANK.EQ([@Seconds], [Seconds], 1)
This time, by setting the order the argument to 1 uses ascending ordering, ensuring that the lowest time is rank 1.
Get the most out of your data
By placing the leaderboard in column B, you can extract a clean podium display using the FILTER and SORT functions together in a separate cell:
=SORT(FILTER(T_Results[[Rank]:[Seconds]], T_Results[Rank] <= 3), 1, 1)
This creates a secondary table that extracts only the three fastest drivers. Because it is linked to the Rank column, the podium updates automatically even if your main table remains sorted by the StartPos column.
Use Case 3: Breaking Ties Using a Secondary Metric
Use bonus points to decide the final ranking
RANK.EQ assigns the same rank to tied scores. To create a unique leaderboard that rewards secondary performance (like bonus points), you can add COUNTIFS to your formula. This counts how many people share the same score but have a higher bonus.
The scenario: You have a table named T_League. Three players are tied at 88 points, so you want to use their bonus points to break the tie so each rank is unique and fair.
To create a single ranking based on merit, enter this formula in the cell A2:
=RANK.EQ([@Points], [Points]) + COUNTIFS([Points], [@Points], [Bonus], ">"&[@Bonus])
This formula calculates a base ranking, then “pushes” linked players down based on their bonus points:
- RANK.EQ([@Points], [Points]) identifies the standard rank. For the three players with 88 points, this returns 2 (because Dakota is #1).
- ACCOUNTS(…) is the tiebreaker. It only looks at players who have the exact same points as the current line and counts how many of them have a bonus higher than the current line.
Let’s take Blake (88 points, 15 bonuses) as an example:
- RANK.EQ sees that Blake is tied for second place.
- ACCOUNTS looks at everyone with 88 points and asks, “Who has a bonus higher than Blake’s 15?” Only Emerson qualifies, so the count is 1.
-
Excel then adds this number (1) to the base rank (2). As a result, Blake is identified as rank 3.
Since this method uses standard functions, it is incredibly fast, returns a single value per row (making it ideal for Excel tables), and is easy for others to understand.
These ranking techniques ensure that each new entry finds its place in the rankings from the second you type it, meaning it’s a huge win for anyone tracking live competitive data. That said, if you just need a sequential list that reacts to your data entries or deletions as they occur, combine the SEQUENCE and COUNTA functions. Either way, the key is automation and time saving: the benefits these two Excel workflows offer.
- Operating system
-
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, 1TB of OneDrive storage, and more.
