Rank Values from Highest to Lowest

Excel & Google Sheets

How it works

RANK compares the value in B2 against every value in $B$2:$B$20 and returns its position. The third argument sets the direction: 0 (or leaving it out) means descending — the largest value gets rank 1 — while 1 means ascending, where the smallest wins. The $ signs anchor the range so it stays put as you fill the formula down; only B2 moves. Ties share a rank and skip the following ones: two values tied for 2nd both show 2, and the next value shows 4. In current Excel you'll also see RANK.EQ — it behaves identically, and plain RANK still works everywhere including Google Sheets.

B2

The value to rank.

$B$2:$B$20

The full list to rank against — anchored with $ so it doesn't shift when filled down.

0

Descending order: the largest value gets rank 1. Use 1 to rank smallest first.

When to use it

Use this for sales leaderboards, test-score standings, and "top 10" reports — anywhere you want positions visible next to the data while keeping the sheet in its original order.

Common mistakes

• ✕Forgetting the $ anchors, so ranks go wrong further down.

  ✓=RANK(B2,B2:B20,0) shifts the range one row per fill — by row 10 you're ranking against B10:B28. Anchor it: =RANK(B2,$B$2:$B$20,0).

• ✕Ties skip ranks — two people show 2 and nobody shows 3.

  ✓That's how RANK works by design. If you need unique ranks, break ties by position: =RANK(B2,$B$2:$B$20,0)+COUNTIF($B$2:B2,B2)-1.

• ✕The best performer shows the highest rank number instead of 1.

  ✓The direction argument is set to 1 (ascending), which rewards the smallest value. Use 0 for largest-first: =RANK(B2,$B$2:$B$20,0).