RANK.EQ function
RANK.EQ tells you where a number places within a list: 1 for the largest by default, or 1 for the smallest if you set the order to 1. Tied values share a rank and the next rank is skipped, the same way a leaderboard handles ties. The older RANK function works the same way.
- Available in
- Excel 2010 and later
- Category
- Statistical functions
- Returns
- The position of the number in the list, as a whole number.
- Google Sheets
- Also available in Google Sheets.
Syntax
=RANK.EQ(number, ref, [order])
| Argument | Required? | What it is |
|---|---|---|
number | Required | The value to rank, usually the cell on the same row. |
ref | Required | The full list of values to rank against. Lock it with $ before filling down. |
order | Optional | 0 or omitted ranks the largest as 1; any other number ranks the smallest as 1. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =RANK.EQ(B2,B2:B7) | 2 | Ana is second; Lee has the top sales |
| F3 | =RANK.EQ(B2,B2:B7,1) | 5 | Ascending order: rank 1 is the smallest value |
| F4 | =RANK.EQ(B6,B2:B7) | 4 | Mo and Raj tie for 4th, so no one is ranked 5th |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Rep | Sales | Check | Result | ||
| Ana | $48,200 | Ana, highest = 1 | 2 | ||
| Raj | $39,500 | Ana, lowest = 1 | 5 | ||
| Lee | $51,750 | Mo, tied with Raj | 4 | ||
| Kim | $44,100 | ||||
| Mo | $39,500 | ||||
| Ivy | $28,900 |
Practice RANK.EQ
From lesson 23, RANK, LARGE & SMALL. Graded instantly, then re-checked on changed data.
Rank the team
C2, rank each rep's sales (1 = highest). Fill down to C9.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| Ranks repeat or jump after filling down | The ref range shifted. Lock it: =RANK.EQ(B2,$B$2:$B$7). |
| #N/A | The number isn't in the ref list, often because ref doesn't cover the whole column of values. |
| Ties share a rank and leave a gap | That's how RANK.EQ works. Use RANK.AVG for an average rank, or add COUNTIF($B$2:B2,B2)-1 to break ties by order. |
Tips
- To rank within a group, such as by region, use COUNTIFS: =COUNTIFS(region,region_cell,sales,">"&sales_cell)+1.
Questions
What is the difference between RANK and RANK.EQ?
They return the same result. RANK.EQ is the name introduced in Excel 2010; RANK is kept for compatibility.
How do I rank from lowest to highest?
Set the third argument to 1: =RANK.EQ(B2,$B$2:$B$7,1).
How do I rank without ties?
Add a tie-breaker: =RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1 gives the first occurrence the better rank.
Lessons that use RANK.EQ
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.