Spreadsheet formulas explained, side by side

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

AppSupportSyntax & notes
Excel✔ Built inRANK.EQ(number, ref, [order])
The older RANK() gives the same result. RANK.AVG gives tied values the average of their positions.
Google Sheets✔ Built inRANK(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≈ Workaroundlist.filter(current > n).length() + 1
Count 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.
Try RANK with your own values
One value per line, or separate with commas
Result (as Excel & Google Sheets return it)
3
Your formula, ready to paste
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

ArgumentWhat it means
numberThe value whose rank you want; it must appear in ref.
refThe range of numbers to rank against. Excel requires a real range here, not an array constant.
[order] optional0 or omitted = descending (largest is 1). Any non-zero value = ascending (smallest is 1).

Examples

FormulaResultWhyCopy
=RANK.EQ(87, A2:A6)3Tester data: two scores of 92 are higher, so 87 is 3rd.
=RANK.EQ(92, A2:A6)1Both 92s are ranked 1; the next rank used is 3.
=RANK.EQ(87, A2:A6, 1)3Ascending: 68 and 75 are lower.
=RANK.EQ(80, A2:A6)#N/A80 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.

Part of the Statistics & Counting functions. Results in the tester follow Excel and Google Sheets behaviour; always double-check important work in your own file.