Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to rank values without ties in Excel and Google Sheets
Topic:RANK & statistical aggregatesExcel & Google Sheets
=RANK(B2,B$2:B$6,0)+COUNTIF(B$2:B2,B2)-1Try 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
Runs server-side · free · no signup
Step by step
- 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.
Tips
- 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.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Does this formula guarantee unique ranks?
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.
What does the 0 in RANK mean?
0 means descending order (largest value = rank 1). Use 1 for ascending order (smallest value = rank 1).
More on RANK & statistical aggregates
See all →Reference
RANK.EQ
Returns the rank of a number in a list, assigning the same rank to ties (same as RANK). Essential for leaderboards.
How-toHow to rank values
Rank a number against a list with RANK — highest first or lowest first. Works the same in Excel and Google Sheets.
ReferenceRANK.AVG
Returns the rank of a number, averaging ranks for ties. Useful in statistical analysis where tied ranks should not cluster.
How-toHow to calculate standard deviation
Measure how spread out your numbers are with STDEV for a sample or STDEVP for a whole population. Works in Excel and Google Sheets.
ReferencePERCENTILE
Returns the k-th percentile value from a dataset. Useful for performance benchmarking and threshold analysis.
How-toHow to calculate a weighted average
Combine values and their weights with SUMPRODUCT divided by the total weight. Works the same in Excel and Google Sheets.