# RANK.EQ function in Excel

> 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.

Source: https://spreadsheetpractice.com/functions/rank-eq · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** 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.
- **Syntax:** `=RANK.EQ(number, ref, [order])`
- **Most common use:** `=RANK.EQ(B2,B2:B7)` returns 2 (Ana is second; Lee has the top sales)
- **Available in:** Excel 2010 and later
- **Category:** [Statistical functions](https://spreadsheetpractice.com/functions/category/stats)
- **Returns:** The position of the number in the list, as a whole number.
- **Google Sheets:** Also available in Google Sheets.

## What it does

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.

## 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 was calculated by running the formula on the sample sheet.

| 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 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Rep | Sales |  |  | Check | Result |
| 2 | Ana | $48,200 |  |  | Ana, highest = 1 | 2 |
| 3 | Raj | $39,500 |  |  | Ana, lowest = 1 | 5 |
| 4 | Lee | $51,750 |  |  | Mo, tied with Raj | 4 |
| 5 | Kim | $44,100 |  |  |  |  |
| 6 | Mo | $39,500 |  |  |  |  |
| 7 | Ivy | $28,900 |  |  |  |  |

## 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.

## Related functions

- [LARGE](https://spreadsheetpractice.com/functions/large): LARGE returns the k-th largest value in a range: k = 1 is the largest, 2 the second largest, and so on.
- [SMALL](https://spreadsheetpractice.com/functions/small): SMALL returns the k-th smallest value in a range: k = 1 is the smallest, 2 the second smallest, and so on.
- [COUNTIFS](https://spreadsheetpractice.com/functions/countifs): COUNTIFS counts the rows where every condition is true: orders that are from the East region and shipped, deals between $500 and $1,000.

## 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.

## Practice

A graded RANK.EQ exercise ("Rank the team") is on [the RANK.EQ page](https://spreadsheetpractice.com/functions/rank-eq). It is checked instantly, then re-checked on changed data.

Lessons that use RANK.EQ:

- [Lesson 34: RANK, LARGE & SMALL](https://spreadsheetpractice.com/learn/rank-large-small) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
