How Ranking Works in Excel: RANK, RANK.EQ, RANK.AVG, and Dense Rank Explained

When you work with numbers in Excel, sometimes you don’t just want to know the highest or lowest value.

You want to know who comes first, second, third and so on. This is called ranking.

Instead of sorting the list manually again and again, Excel gives us the RANK functions.

What Does the RANK Function Do?

The RANK function tells you the position of a number in a list compared to other numbers.

In simple words:

RANK = position of a value in a list

By default:

•  The largest number gets Rank 1
•  The next largest gets Rank 2
•  And so on…

Excel provides three versions:

•  RANK → older version
•  RANK.EQ → newer version, gives equal rank for ties
•  RANK.AVG → gives average rank when values are tied

They work with numbers and ignore text automatically.

Let’s understand them step by step.

Syntax:

=RANK(number, range)

Arguments:

• number → the value you want to rank
• range → the list of numbers

When there are no duplicate values, all three functions behave the same.

Example: Sales Ranking (No Duplicate Values):

Let’s say you have sales data like this:

S.NO	Salesperson	Sales
1	John	1200
2	Meera	950
3	Ali	1800
4	Karan	700
5	Riya	1500
6	David	400
7	Neha	2000
8	Aman	1100
9	Priya	600
10	Rohit	300

Ranking the Sales:

Sales range: C2:C11

Formula in D2:

=RANK(C2, $C$2:$C$11)

OR

=RANK.EQ(C2, $C$2:$C$11)

Copy the formula down for all rows.

Result:

Important Things to Notice:

• Every value is unique
• No ties exist
• Ranks are continuous: 1, 2, 3, 4…
• This is the simplest ranking case

When there are no ties, ranking is straightforward and easy.

When every value was different, Ranks were clean and continuous: 1, 2, 3, 4…

But real-life data is rarely that perfect.

Sometimes:

• Two students score the same marks
• Two salespeople make the same sales
• Two players finish with the same score

This situation is called a tie.

Now the big question is:

How should Excel handle ranks when values are the same?

That’s exactly where things start to look confusing — but don’t worry, it’s actually very logical once you see it step by step.

Excel has two different ways to handle these ties:

1. RANK.EQ (equal rank)

2. RANK.AVG (average rank)

Let’s understand both using one simple example.

Example: Sales Data With Ties:

S.NO	Salesperson	Sales
1	John	1200
2	Meera	1200
3	Ali	1800
4	Karan	700
5	Riya	400
6	David	400

Range used: C2:C7

Here:

•  1200 appears twice
•  400 appears twice

Method 1: RANK.EQ (Equal Rank)

What RANK.EQ Does:

• Gives the same rank to equal values
• Skips the next rank number

This method is also called competition ranking.

 Formula (in D2):

=RANK.EQ(C2, $C$2:$C$7)

Copy it down.

Result (RANK.EQ):

 Why are ranks missing?

• Two people share Rank 2
• So Rank 3 is skipped
• Two people share Rank 5
• So Rank 6 is skipped

This is normal behavior for RANK.EQ.

Method 2: RANK.AVG (Average Rank)

What RANK.AVG Does:

• Gives the average of the tied positions
• Results may include decimals

Formula:

=RANK.AVG(C2, $C$2:$C$7)

 Result (RANK.AVG):

Why decimals?

• 1200 takes positions 2 and 3
• Average = (2 +3) ÷ 2 = 2.5
• 400 takes positions 5 and 6
• Average = (5 + 6) ÷ 2 = 5.5

This method is often used in statistics and sports scoring.

Why Are Ranks Skipped or Decimals Used?

Because Excel is being mathematically fair.

When values are equal:

• RANK.EQ keeps ranks equal and skips numbers
• RANK.AVG spreads the rank evenly using averages

Neither is wrong — they are just different rules.

But many people ask a very natural question:

> “If two people get Rank 1, why does the next person become Rank 3?”

> “Why can’t the next rank just be Rank 2?”

That idea is called dense ranking.

What Is Dense Ranking?

In dense ranking:

• Equal values share the same rank
• The next rank is the immediate next number
• No ranks are skipped

Example: 1, 1, 2, 2, 3, 4 …

This feels more natural for many real-life situations.

Excel does not have a built-in Dense Rank function, but there are two reliable formulas that do the job.

Both give the same dense ranking result, but they work in slightly different ways.

Let’s understand them in a simple way.

Dense Rank Formula 1 (Modern Excel – Easy to Read):

=MATCH(C2, SORT(UNIQUE($C$2:$C$7),, -1), 0)

How this formula works:

1. UNIQUE($C$2:$C$7)

   → Removes duplicate values

   → Example: `1200, 1800, 700, 400`

2. SORT(… , , -1)

   → Sorts the unique values from highest to lowest

3. MATCH(C2, …, 0)

   → Finds the position of the current value in that sorted list

That position is the dense rank.

 Why this formula is good:

• Very clean and readable
• Easy to explain
• Perfect for Excel 365 / Excel 2021
• Best for tutorials and dashboards

Note: Works only in newer Excel versions.

Dense Rank Formula 2 (Classic Excel – Works Everywhere):

=SUMPRODUCT((C2<=$C$2:$C$7)/COUNTIF($C$2:$C$7, $C$2:$C$7))

How this formula works?

This formula counts how many unique values are greater than or equal to the current value.

Here’s the idea:

 `(C2 <= $C$2:$C$7)`

  → Checks which values are greater than or equal to the current value

 `COUNTIF($C$2:$C$7, $C$2:$C$7)`

  → Counts how many times each value appears

 Dividing by COUNTIF ensures that duplicate values are counted only once

 `SUMPRODUCT` adds everything together to give the dense rank

 Why this formula is good:

• Works in all Excel versions
• No need for UNIQUE or SORT
• Very powerful for older Excel users

Note: Slightly harder to understand at first, but very reliable.

Result (Both Formulas Give the Same Output):

• No skipped ranks.
• No decimals.
• Clean and continuous.

Which Ranking Should You Use?

Use RANK OR RANK.EQ when:

• You want simple ranking
• Ties can share the same rank
• Skipped numbers are acceptable

Use RANK.AVG when:

• You want statistical fairness
• Decimal ranks are acceptable

Use Dense Ranking when:

• You want continuous ranks
• You’re building leaderboards or dashboards
• Clean presentation matters

Ranking in Excel may look confusing at first, but once you understand the logic, it becomes very simple.

• RANK / RANK.EQ help you find the position of a value in a list.
• RANK.AVG handles ties by giving fair, average positions.
• Dense ranking keeps ranks clean and continuous without gaps.
• LARGE and SMALL help you find top or bottom values, but RANK tells you where each value stands.

Each method has its own purpose. There is no “one best” ranking style — the right choice depends on what you want to show in your report.

Once you know which ranking method to use and when, Excel becomes much more powerful for analysis, reporting, and decision-making.

Master these ranking tools, and you’ll never need to sort and guess positions manually again.