What is the difference between RANK, RANK.EQ, and RANK.AVG?

RANK is an older compatibility function. In current versions, RANK.EQ assigns the same rank to ties, while RANK.AVG assigns the average rank for ties. Choose the one that fits your reporting policy.

Can I create unique ranks when there are ties?

Yes. Add a secondary criterion, such as submission time or employee ID, with COUNTIFS to create unique ranks. For example, use the RANK.EQ + COUNTIFS pattern.

How do I calculate ranks by group, department, or category?

Use FILTER to extract only the values in the same group, then apply RANK.EQ (dynamic arrays). In older versions, you can use SUMPRODUCT instead.

Can I exclude zero scores and blank cells from rankings?

Yes. Use FILTER to create a list that excludes zeros and blank cells, then pass that range to RANK.

How can I rank only the rows visible after filtering?

Combine SUBTOTAL(103) and OFFSET to filter only visible rows, then pass the result to RANK.EQ.

CONTENTS

Excel RANK Function: A Practical Guide to Ties, Groups, and Visible Rows

The Excel RANK function quickly ranks numbers such as scores or sales figures. This article provides step-by-step, practical formulas for basic usage as well as handling ties, ranking by group, excluding zeros and blank cells, and calculating ranks for visible rows only when filters are applied. It concludes with ready-to-use recipes for extracting and sorting the top N and converting ranges to Excel Tables (structured references).

Quick Fix

Descending order (largest value ranked 1): =RANK.EQ(B2,$B$2:$B$11,0)
Ascending order (smallest value ranked 1): =RANK.EQ(B2,$B$2:$B$11,1)
Average rank for ties: =RANK.AVG(B2,$B$2:$B$11,0)
Unique rank for ties (secondary criterion): =RANK.EQ(B2,$B$2:$B$11,0)+COUNTIFS($B$2:$B$11,B2,$C$2:$C$11,"<"&C2)
Rank by group (dynamic array): =RANK.EQ(B2,FILTER($B$2:$B$11,$C$2:$C$11=C2),0)
Exclude zeros and blank cells: =IF(OR(B2=0,B2=""),"",RANK.EQ(B2,FILTER($B$2:$B$11,($B$2:$B$11>0)*($B$2:$B$11<>"")),0))
Visible rows only (accounts for filters): =RANK.EQ(B2,FILTER($B$2:$B$11,SUBTOTAL(103,OFFSET($B$2,ROW($B$2:$B$11)-ROW($B$2),0))),0)
Extract the top N (including sorting): =TAKE(SORTBY(A2:B11,B2:B11,-1),N)

Why These Issues Occur (Concepts and Principles)

RANK vs. RANK.EQ vs. RANK.AVG

RANK is an older function retained for compatibility. In current versions, use RANK.EQ (same rank for ties) or RANK.AVG (average rank for ties).
Choose based on whether gaps in rankings are acceptable for ties (such as 1, 2, 2, 4) or whether you want to use average ranks (such as 1, 2.5, 2.5, 4).

The Third Argument and Locking the Reference Range

0 ranks in descending order (largest value ranked 1), while 1 ranks in ascending order (smallest value ranked 1).
Lock the comparison range with $ before filling the formula down. For example: $B$2:$B$11

Practical Examples

Sample Data

Use the following table for the examples.

Name	Score	Submission Time (Secondary Criterion)	Team
Kim	95	09:05	A
Lee	90	09:10	B
Park	90	09:03	A
Choi	88	09:20	B
Jang	84	09:01	A
Song	84	09:11	A
Han	80	09:15	B
Yoon	0	—	B
Seo		—	A
Baek	76	09:25	B

1) Basic Ranking (Descending/Ascending)

Descending (largest value ranked 1): =RANK.EQ(B2,$B$2:$B$11,0)
Ascending (smallest value ranked 1): =RANK.EQ(B2,$B$2:$B$11,1)

2) Stabilizing Ties (Unique Rank Using a Secondary Criterion)

To give an earlier submission time a higher rank when scores are tied, creating unique ranks:

=RANK.EQ(B2,$B$2:$B$11,0)
+COUNTIFS($B$2:$B$11,B2,$C$2:$C$11,"<"&C2)

How it works: The formula adds the number of people with the same score who submitted earlier, moving your rank down accordingly to create a unique rank. You can use employee ID, hire date, alphabetical name, or another value as the secondary criterion.

3) Rank by Group (Team/Category)

Using a dynamic array (FILTER):

=RANK.EQ(B2, FILTER($B$2:$B$11, $D$2:$D$11=C2), 0)

This filters the score list to rows in the same team and calculates the rank within that list.

Alternative for older versions:

=SUMPRODUCT(($D$2:$D$11=D2)*($B$2:$B$11>B2))+1

The rank equals the number of higher scores in the same team plus 1.

4) Rank While Excluding Zeros and Blank Cells

=IF(OR(B2=0,B2=""),"",
RANK.EQ(B2, FILTER($B$2:$B$11, ($B$2:$B$11>0)*($B$2:$B$11<>"")), 0))

Zeros and blank cells display as "" and are excluded from the ranking.

5) Rank Visible Rows Only When Filters Are Applied

Exclude rows hidden by AutoFilter and rank only the visible values.

=RANK.EQ(B2,
  FILTER($B$2:$B$11,
    SUBTOTAL(103, OFFSET($B$2, ROW($B$2:$B$11)-ROW($B$2), 0))
  ),
0)

SUBTOTAL(103, ...) returns TRUE only for visible rows.

6) Extracting and Sorting the Top N

In Microsoft 365 or Excel 2021 and later, sort scores in descending order and return only the top N rows:

=TAKE(SORTBY(A2:D11, B2:B11, -1), N)

If you want to add a rank column to this table, add the RANK.EQ formula from the Quick Fix section as a column and then sort the table.

7) Convert to an Excel Table (Structured References)

Convert the range to a table (keyboard shortcut Ctrl+T). If the table is named tblScore and its columns are [Name], [Score], [Time], and [Team]:

=RANK.EQ([@Score], tblScore[Score], 0)

Tables automatically expand as data is added, making them easier to manage.

Alternatives, Notes, and Checklist

If you want consecutive ranks without gaps for ties, use a secondary criterion such as time or employee ID to create unique ranks.
Exclude zeros or nonparticipants to prevent distorted statistics.
Ranking by team or category is easy with the FILTER or SUMPRODUCT pattern.
If you frequently use filters, use a visible-rows-only ranking formula to keep reports consistent.
For the top N, the SORTBY+TAKE combination is the simplest option.

Troubleshooting

Issue	Cause	Solution
Ranks are incorrect	The comparison range uses relative references and shifts when filled down	Lock it with absolute references, such as `$B$2:$B$11`
There are many ties, but the report requires unique ranks	No secondary criterion for ties	Add a secondary criterion such as time or employee ID with `COUNTIFS` to create unique ranks
Zero scores are included in the ranking	No exclusion condition was applied	Filter the target values with `FILTER(range,range>0)`
Hidden rows are still calculated after filtering	The range does not account for visible rows	Filter visible rows only with the `SUBTOTAL(103,OFFSET(...))` pattern
Updating ranges is tedious whenever data grows	A regular range is being managed manually	Convert it to a table with `Ctrl+T` and use structured references

Excel RANK Function Complete Guide: Ties, Groups, Visible Rows, and Top N