Rank Values (No Gaps)

All versionsRANK.EQCOUNTIF

To number a list from highest to lowest — leaderboard position, sales ranking — use RANK.EQ. And with a COUNTIF tweak you can break ties so the ranks run 1, 2, 3 with no gaps instead of 1, 2, 2, 4.

Quick formula: to rank the score in B2 against the whole list, highest = 1:

=RANK.EQ(B2, $B$2:$B$8)

The locked list $B$2:$B$8 is the pool; leave the 3rd argument off for descending (largest is rank 1).

Functions used (tap for the full reference guide):

RANK.EQ COUNTIF COUNTIFS

The example

Scores ranked highest-first. Note the tie on 88.

	A	B	C
1	Player	Score	Rank
2	Ana	95	1
3	Ben	88	2
4	Cy	88	3
5	Dot	74	4

The formula

A no-gaps rank that also breaks ties by row order:

=RANK.EQ(B2, $B$2:$B$5) + COUNTIF($B$2:B2, B2) - 1 // the two 88s become ranks 2 and 3

How it works

Plain RANK.EQ gives ties the same number and then skips one; the COUNTIF adds a tiebreaker:

RANK.EQ(B2, $B$2:$B$5) ranks B2 in the list, largest = 1. Both 88s get rank 2, and rank 3 is skipped.
COUNTIF($B$2:B2, B2) counts how many times this value has appeared so far (the range grows as you copy down): 1 for the first 88, 2 for the second.
Adding that count minus 1 nudges the second 88 from rank 2 to rank 3, closing the gap.
Result: a clean 1, 2, 3, 4 with no duplicate or skipped ranks.

Try it: interactive demo

Live demo

Type scores (commas); see plain vs no-gaps ranks side by side.

Scores

Variations

Ascending rank (smallest = 1)

Add 1 as the third argument:

=RANK.EQ(B2, $B$2:$B$8, 1)

Rank within a group

Rank each rep only against their own team with COUNTIFS:

=COUNTIFS($A$2:$A$8, A2, $B$2:$B$8, ">"&B2) + 1

Modern: SORT to a live leaderboard

In Excel 365, skip ranking and just sort:

=SORT(A2:B8, 2, -1)

Pitfalls & errors

Plain RANK leaves gaps on ties. Two 2nd places means no 3rd place. Add the COUNTIF tiebreaker if you need consecutive ranks.

Ranks change as you copy. Lock the pool with $B$2:$B$8. Without the $, the range drifts and ranks go wrong further down.

RANK vs RANK.EQ. The old RANK still works for back-compatibility; RANK.EQ is the current name and behaves identically.

Practice workbook

📊

Download the free Rank Values (No Gaps) practice workbook

The score list with live RANK.EQ, the no-gaps tiebreaker, ascending and per-group ranks, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I rank values in Excel?

Use =RANK.EQ(B2, $B$2:$B$8) for descending rank (largest = 1). Lock the list with $ so it doesn't shift as you fill down. Add 1 as the third argument for ascending.

How do I rank without skipping numbers on ties?

Add a COUNTIF tiebreaker: =RANK.EQ(B2, $B$2:$B$8) + COUNTIF($B$2:B2, B2) - 1. The growing COUNTIF gives tied values consecutive ranks instead of duplicates and gaps.

How do I rank within groups?

Use COUNTIFS to count higher values within the same group: =COUNTIFS($A$2:$A$8, A2, $B$2:$B$8, ">"&B2) + 1 ranks each row only against its own group.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: RANK.EQ · COUNTIF · COUNTIFS