SheetSense

Rank values with RANK and ties

RANK returns the position of a number within a list, and when two values tie they share the same rank and the next rank is skipped.

fx
=RANK(B2,$B$2:$B$100,0)

The sheet: Reps sheet: column A is Rep name and column B is Quarterly sales. You want each rep's position, with the highest sales ranked 1.

How it works, step by step

  1. 01B2 is the sales figure to rank.
  2. 02$B$2:$B$100 is the full list to compare it against. The $ signs keep it fixed when you fill down.
  3. 03The final 0 ranks in descending order, so the largest value gets rank 1. Use 1 to rank the smallest value first.
  4. 04With sales of 100, 90, 90, and 80 the ranks are 1, 2, 2, and 4. Rank 3 is skipped because two reps tied for second.

The same logic in four tools

Excel
=RANK(B2,$B$2:$B$100,0)
Google Sheets
=RANK(B2,$B$2:$B$100,FALSE)
SQL
SELECT rep, sales,
       RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM reps;
-- Use DENSE_RANK() to avoid skipped numbers after ties.
Python (pandas)
import pandas as pd

reps["sales_rank"] = reps["sales"].rank(method="min", ascending=False)
# method="dense" avoids skipped numbers after ties.

Mistakes that break it

  • !Forgetting the $ signs lets the comparison range shift as you fill down, so each row is ranked against a different list.
  • !Ties share a rank and leave a gap, which surprises people who expect 1, 2, 3, 4. Use DENSE_RANK in SQL or method="dense" in pandas if you do not want gaps.
  • !RANK ignores text and blank cells in the list, but a blank in the cell you rank itself returns #N/A.
  • !In Google Sheets the last argument is TRUE or FALSE for ascending. A 0 also works, but FALSE is clearer.

Common questions

How do I rank without ties in Excel?
Break the tie with a second condition: =RANK(B2,$B$2:$B$100,0)+COUNTIF($B$2:B2,B2)-1. The first of the tied rows keeps the rank and later rows get the next numbers.
What is the difference between RANK, RANK.EQ and RANK.AVG?
RANK and RANK.EQ behave the same, giving tied values the lowest rank. RANK.AVG gives tied values the average of the ranks they cover, such as 2.5 for a tie for second and third.
How do I rank within a group?
In SQL add PARTITION BY region inside OVER. In pandas use df.groupby("region")["sales"].rank(method="min", ascending=False).

Got a version of this with your own ranges?

Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.