Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
RANK vs RANK.EQ: legacy and modern ranking in Excel and Sheets
Topic:RANK & statistical aggregatesHeads up: Excel and Google Sheets do this differently.
Excel
=RANK.EQ(A2,$A$2:$A$6,0)Google Sheets
=RANK(A2,$A$2:$A$6,0)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
Runs server-side · free · no signup
Step by step
- 1Place your numeric values in A2:A6.
- 2In B2, enter =RANK.EQ(A2,$A$2:$A$6,0) to rank A2 within the full list in descending order (0 = largest = rank 1).
- 3Copy B2 down to B6. Tied values will share the same rank, and the next rank is skipped (so two values tied at rank 2 means rank 3 is skipped).
- 4In Google Sheets, replace RANK.EQ with RANK — the syntax and behavior are identical: =RANK(A2,$A$2:$A$6,0).
- 5Use the third argument: 0 for descending (highest value = rank 1) or 1 for ascending (lowest value = rank 1).
Tips
- RANK.AVG (Excel only) assigns the average rank to ties — useful when you need a strict ordering with no repeated ranks (e.g. for statistical analysis). Google Sheets has no native RANK.AVG.
- Always lock the reference range with absolute references ($A$2:$A$6) before copying the formula down, or the range will shift and produce incorrect ranks.
- For a dense rank (no gaps after ties), combine SUMPRODUCT: =SUMPRODUCT((A2>$A$2:$A$6)/COUNTIF($A$2:$A$6,$A$2:$A$6))+1.
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.
Frequently asked
Is RANK deprecated in Excel?
RANK still works in all current Excel versions for backward compatibility, but Microsoft recommends RANK.EQ (or RANK.AVG when averaging ties) in new workbooks.
Why does Google Sheets not have RANK.EQ?
Google Sheets retained the original RANK name and did not adopt the .EQ suffix. The behavior is identical to Excel's RANK.EQ.
How do I break ties in ranking?
Add a tiebreaker column and use COUNTIFS to count how many values are strictly greater, then add 1: =COUNTIFS($A$2:$A$6,">"&A2)+1. This gives unique ranks without gaps.
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.