How to rank values without ties in Excel and Google Sheets

Excel & Google Sheets

=RANK(B2,B$2:B$6,0)+COUNTIF(B$2:B2,B2)-1

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Formula

Runs server-side · free · no signup

1In the first output cell next to your data (e.g. C2), type =RANK(B2,B$2:B$6,0)+COUNTIF(B$2:B2,B2)-1.
2The RANK part assigns the base rank; the COUNTIF tiebreaker adds an offset based on how many times the value has appeared so far.
3Use absolute references for the full range (B$2:B$6) but a mixed reference expanding from the top (B$2:B2) for the COUNTIF.
4Copy the formula down for all rows.
5Change the third argument of RANK from 0 (descending) to 1 (ascending) to reverse the ranking direction.

The expanding COUNTIF reference B$2:B2 is the key — as you copy down, it grows to check how many previous rows have the same value.
This tiebreaking method favors the value that appears first in the list.
To favor the last occurrence instead, reverse the sort or adjust the COUNTIF range.
RANK.EQ is the modern Excel equivalent of RANK; both behave identically.

Need it for your exact data?

Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.

Yes. Each tied value gets a different offset from COUNTIF, so no two rows share the same final rank — they are differentiated by list position.

0 means descending order (largest value = rank 1). Use 1 for ascending order (smallest value = rank 1).

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: May 28, 2026