# How to rank numbers in Excel

Use =RANK.EQ(value, list) for largest = 1, adding 1 as the third argument for smallest = 1. Lock the list with $.

**Excel:** `=RANK.EQ(B2,$B$2:$B$7)` (Excel 2010 and later)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Rep | Sales | Rank |
| Ana | 48200 |  |
| Ben | 61750 |  |
| Cara | 57300 |  |
| Dev | 39800 |  |
| Eli | 61750 |  |
| Fay | 44150 |  |

Formula in C2, filled to C7: `=RANK.EQ(B2,$B$2:$B$7)`

Result: 4; 1; 3; 6; 1; 5

## How it works

RANK.EQ returns where the number would sit if the list were sorted, highest first. Ties share a rank and the next rank is skipped: Ben and Eli are both 1, so Cara is 3.

## Other ways

- `=COUNTIF($B$2:$B$7,">"&B2)+1`: Counts how many values are larger and adds one; the same result, written without RANK.
- `=RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1`: Breaks ties by order, so every rank is unique. (Eli becomes 2 instead of sharing 1.)

## Common mistakes

- Without $, the list shifts as you fill down and the ranks come out wrong.
- RANK (without .EQ) is the older name for the same function. RANK.AVG gives tied values the average rank, like 1.5.

## Questions

**How do I rank from smallest to largest?**

Add 1 as the order: `=RANK.EQ(B2,$B$2:$B$7,1)`.

**How do I rank within groups?**

Use COUNTIFS: `=COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,">"&B2)+1` ranks within the group in column A.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-29.

Source: https://spreadsheetpractice.com/formulas/rank-values