RANK Function in Excel, Google Sheets, Airtable & Notion
RANK returns where a number stands in a list: 1 for the largest by default, or 1 for the smallest with order 1. Tied values share the same rank.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | RANK.EQ(number, ref, [order])The older RANK() gives the same result. RANK.AVG gives tied values the average of their positions. |
| Google Sheets | ✔ Built in | RANK(value, data, [is_ascending])RANK.EQ and RANK.AVG are also available. |
| Airtable | ✘ Not available | — Airtable formulas cannot compare one record with the others. Sort a view by the field to see the order, or use an extension or automation script to write ranks into a number field. |
| Notion | ≈ Workaround | list.filter(current > n).length() + 1Count how many values beat the number and add 1. Use < for ascending order. Unlike Excel, it does not return #N/A when the number is not in the list. |
| Excel | =RANK.EQ(87, A2:A6, 0) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =RANK(87, A2:A6, 0) | open sheets.new ↗ |
| Airtable | ✘ No equivalent. Airtable formulas cannot compare one record with the others. Sort a view by the field to see the order, or use an extension or automation script to write ranks into a number field. | |
| Notion workaround | [87, 92, 75, 92, 68].filter(current > 87).length() + 1 | |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Score 87 92 75 92 68
Arguments
| Argument | What it means |
|---|---|
number | The value whose rank you want; it must appear in ref. |
ref | The range of numbers to rank against. Excel requires a real range here, not an array constant. |
[order] optional | 0 or omitted = descending (largest is 1). Any non-zero value = ascending (smallest is 1). |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=RANK.EQ(87, A2:A6) | 3 | Tester data: two scores of 92 are higher, so 87 is 3rd. | |
=RANK.EQ(92, A2:A6) | 1 | Both 92s are ranked 1; the next rank used is 3. | |
=RANK.EQ(87, A2:A6, 1) | 3 | Ascending: 68 and 75 are lower. | |
=RANK.EQ(80, A2:A6) | #N/A | 80 is not in the list. |
Examples use Excel / Google Sheets syntax unless noted.
How RANK works
RANK is the leaderboard function: fill =RANK.EQ(B2, $B$2:$B$20) down a column and every score gets its place. Lock the ref range with $ signs so it does not shift as you fill. By default the largest value is ranked 1, which suits sales and scores; set order to 1 for times, costs or golf scores where lower is better.
Ties share a rank and the following rank is skipped (1, 1, 3), the way sports tables usually work. RANK.AVG gives tied values the average position instead (1.5, 1.5, 3). To break ties so every row gets a unique rank, add a count of earlier duplicates: =RANK.EQ(B2, $B$2:$B$20) + COUNTIF($B$2:B2, B2) - 1. If the number is not in ref, RANK returns #N/A; text and blanks in ref are ignored.
Notion has no rank function, but a rank is just “how many values are bigger, plus one”: prop("Scores").filter(current > 87).length() + 1. Ranking a row against other rows of the same database needs a relation (or rollup) that brings all the values into the formula. Airtable formulas only see one record, so ranks need a sorted view or a script.
Frequently Asked Questions
- How do I rank without gaps after ties (1, 1, 2)?
- Count the distinct values above it: in Excel 365 or Sheets,
=COUNT(UNIQUE(FILTER($B$2:$B$20, $B$2:$B$20>B2))) + 1. - Why does RANK return #N/A?
- The number is not found in the ref range, often because of rounding or a number stored as text.