Google Sheets RANK formula builder
Type the column of numbers (or dates) you want to rank and get a formula to paste next to it. Choose whether the biggest or the smallest value is number 1, how ties are ranked, and optionally a group column so each team, region or month is ranked on its own. It is built in your browser and nothing you type here is sent anywhere. All 35 combinations were run in a real Google Sheet on 2 November 2026 (ties, a negative number, a decimal, a blank cell, groups, dates) and compared with an independent calculation, once in a US-locale spreadsheet and once in a German-locale one with semicolons: 70 of 70 matched.
You're on the list. We'll email you once.
How to use it
"One formula fills the whole column": type it once in the first row of an empty column next to your data (not in the header). It spills down by itself and new rows are ranked as they appear. The cells below must be empty or Sheets shows #REF!. "One row, drag it down": paste it in the first data row and drag the corner down; the references are already fixed with $ where they must stay put. Use this one if you want to edit single results or your data has no empty space below.
What the ties options give
| Scores 90, 80, 80, 70 | Rank | Formula idea |
|---|---|---|
| Share a rank, skip the next | 1, 2, 2, 4 | Same as the built-in RANK |
| Share a rank, no gap | 1, 2, 2, 3 | Counts the different larger values |
| Average place | 1, 2.5, 2.5, 4 | Same as RANK.AVG |
| No ties, first row wins | 1, 2, 3, 4 | Adds a running count of equal values |
Google's RANK only has the first kind, and it cannot rank inside a group. The builder uses COUNTIFS for groups (the group column is compared on the same rows) and, for "no gap" with groups, MAP with LAMBDA.
The catches (found by testing in a real Sheet)
- Numbers stored as text all get rank 1. With the scores 10, 20, 20, 5 typed as text (left-aligned), every row returned 1, with no error. Convert the column first (select it, Format > Number > Automatic) or rank a helper column
=ARRAYFORMULA(VALUE(C2:C)). - Blank cells get no rank and do not count as zero. A negative number such as -3 ranks correctly below 0.
- Decimals and dates work. A date is a number to Sheets; "rank 1 is the earliest date" ranks the oldest first. Cells that hold text instead of a date are not ranked right.
- Groups are compared without regard to case: "North" and "north" are one group. Rows with an empty group cell are ranked together as one group.
- Big sheets get slow. Counting formulas compare every row with every other row, fine for a few thousand rows. For tens of thousands, use a closed range such as B2:B5000 and the plain
RANK(first ties option, no group). - Ranking is not sorting. The rank column shows the place but the rows stay where they are. To list them in rank order use the sort formula generator, or let the add-on keep the table itself in order.
- Separators. If your sheet's locale writes decimals with a comma (File > Settings), Sheets wants
;between arguments: switch the separator above. The semicolon versions were run in a German-locale Sheet.
No code: KeepSorted
The add-on sorts the original table in place without code: no second copy, so formatting, checkboxes and notes move with their rows and people keep editing the same table. Select the range, pick the sort columns, Save. Free: one area per tab, up to 2 sort columns, no trial clock. Pro ($5/month or $39/year): up to 5 sort columns, custom orders like High > Medium > Low, ticked checkbox rows to the bottom, stacked tables sorted separately, and sorting after form responses, other editors' changes and an hourly check.
KeepSorted is built and in testing. It is not in the Google Workspace Marketplace yet. Leave your email and we send one message when you can install it.
The generators and the formulas stay free either way.
More KeepSorted tools and guides
- FILTER formula builder (text, number, date conditions)
- Unique values formula maker (UNIQUE, counts, duplicates)
- Sort formula generator (SORT, SORTN, QUERY order by)
- Auto sort script generator (Apps Script that sorts the original)
- StaySorted alternative: an honest comparison
Sources
- Google Docs Editors function list (RANK, RANK.AVG, COUNTIF, COUNTIFS, MAP, LAMBDA, UNIQUE, FILTER)