FORMULAS & FUNCTIONS
RANK Formula in Excel (Without Duplicates, with Criteria, Top 5)
Ranking looks like a one-function job until two people have the same score, or you need a rank inside each region, or a Top 5 list that updates itself. This guide shows RANK.EQ and RANK.AVG, how to rank without duplicates, how to rank with criteria, and how to pull a Top 5 table that never drops a tied name. The free practice file checks its own ranks.

Quick answer
| You want | Formula |
|---|---|
| Rank, highest = 1 | =RANK.EQ(C5,$C$5:$C$16) |
| Rank, lowest = 1 | =RANK.EQ(C5,$C$5:$C$16,1) |
| Ties share the average rank | =RANK.AVG(C5,$C$5:$C$16) |
| Rank without duplicates | =RANK.EQ(C5,$C$5:$C$16)+COUNTIF($C$5:C5,C5)-1 |
| Rank within a group | =COUNTIFS($B$5:$B$16,B5,$C$5:$C$16,">"&C5)+1 |
RANK.EQ and RANK.AVG work in Excel 2010 and later, Microsoft 365 and Excel for the web. The older RANK function gives the same result as RANK.EQ and still works.
Step 1: RANK.EQ: highest value = rank 1
The Sales Rank sheet lists 12 reps with their quarterly sales in column C. In D5, rank each rep against the whole list:
=RANK.EQ(C5,$C$5:$C$16)
Lock the range with $ so it does not move when you copy the formula down. RANK.EQ counts how many values are higher and adds 1. Carla Diaz and Hugo Silva both have 61,750, so both are rank 1, and the next rep, Felix Ramos, is rank 3: no one is rank 2. That is standard competition ranking, the same way sports tables work.

Step 2: RANK.AVG: ties share the average rank
=RANK.AVG(C5,$C$5:$C$16)
| Rep | Sales | RANK.EQ | RANK.AVG |
|---|---|---|---|
| Carla Diaz | 61,750 | 1 | 1.5 |
| Hugo Silva | 61,750 | 1 | 1.5 |
| Ben Ortiz | 48,500 | 6 | 6.5 |
| David Lee | 48,500 | 6 | 6.5 |
The two reps tied at 48,500 cover ranks 6 and 7, so RANK.AVG gives both 6.5. This is the method used in statistics (for example rank correlation). For leaderboards and bonuses, RANK.EQ is easier to read.

Step 3: Rank from lowest to highest
Add 1 as the third argument (order). Omitting it or using 0 ranks in descending order:
=RANK.EQ(C5,$C$5:$C$16,1)
Iris Tan has the lowest sales, 36,800, so her ascending rank is 1. Use this for anything where lower is better: delivery times, costs, error counts or race times.

Step 4: Rank without duplicates
Lookups, Top 5 tables and charts need every rank exactly once. Add a COUNTIF that counts how many times the value has already appeared above the current row:
=RANK.EQ(C5,$C$5:$C$16)+COUNTIF($C$5:C5,C5)-1
The trick is the mixed reference $C$5:C5: it grows as you copy down. For the first occurrence of a value, COUNTIF returns 1, so nothing is added. For the second occurrence it returns 2, so the rank goes up by 1. Carla Diaz (listed first) is 1 and Hugo Silva 2; Ben Ortiz is 6 and David Lee 7.
The sheet checks the result: 12 unique ranks must add up to 12 x 13 / 2 = 78, the largest must be 12, and none may repeat. Row 19 shows Ties.

Step 5: Rank with criteria (rank within a group)
RANK.EQ has no criteria argument, so rank inside each region with COUNTIFS. Count how many reps in the same region sold more, then add 1:
=COUNTIFS($B$5:$B$16,B5,$C$5:$C$16,">"&C5)+1
| Region | Rank 1 | Rank 2 | Rank 3 |
|---|---|---|---|
| North | Ana Cruz (52,300) | David Lee (48,500) | Iris Tan (36,800) |
| South | Felix Ramos (55,200) | Ben Ortiz (48,500) | Jon Reyes (42,600) |
| East | Carla Diaz (61,750) | Kara Lim (50,400) | Grace Kim (44,100) |
| West | Hugo Silva (61,750) | Leo Wong (47,200) | Ella Park (39,900) |
Ties get the same rank, like RANK.EQ. For ascending order use “<” instead of “>”. You can add more conditions in pairs, such as a product or a month; see COUNTIFS with multiple criteria.

Step 6: A Top 5 list that updates itself
On the Top 5 sheet, column A holds the numbers 1 to 5. Find the rep with each unique rank:
=INDEX('Sales Rank'!$A$5:$A$16,MATCH(A5,'Sales Rank'!$G$5:$G$16,0))
and return the matching sales with LARGE:
=LARGE('Sales Rank'!$C$5:$C$16,A5)
| Rank | Rep | Region | Sales | Share |
|---|---|---|---|---|
| 1 | Carla Diaz | East | 61,750 | 10.5% |
| 2 | Hugo Silva | West | 61,750 | 10.5% |
| 3 | Felix Ramos | South | 55,200 | 9.4% |
| 4 | Ana Cruz | North | 52,300 | 8.9% |
| 5 | Kara Lim | East | 50,400 | 8.6% |
| Top 5 | 281,400 | 47.8% |
Because MATCH looks up the unique rank, both tied reps appear; with plain RANK.EQ, MATCH would find rank 1 twice and never find rank 2, returning #N/A. A check confirms that each listed rep’s own sales match the LARGE values. For the lookup itself, see INDEX MATCH vs XLOOKUP.

In Microsoft 365 you can get a sorted Top 5 in one formula: =TAKE(SORTBY(A5:C16,C5:C16,-1),5). See sort data with a formula using SORTBY. To highlight the top 5 on the main list, use a conditional formatting rule such as =$G5<=5 (see conditional formatting based on another cell), and to show how much the top few contribute, try a Pareto chart.
Common RANK problems
- Ranks change when copied: the range is not locked. Use $C$5:$C$16.
- #N/A: the number is not in the range, or a Top 5 lookup uses RANK.EQ with ties instead of a unique rank.
- A value is skipped: it is stored as text. RANK.EQ ignores text in the range.
- Rank 1 is the lowest: the order argument is 1. Remove it or use 0.
- Blank rows get a rank: wrap the formula, for example =IF(C5=””,””,RANK.EQ(C5,$C$5:$C$16)).
Frequently asked questions
What is the difference between RANK, RANK.EQ and RANK.AVG?
RANK and RANK.EQ give tied values the same, highest rank. RANK.AVG gives them the average of the ranks they cover, such as 6.5 for a tie at ranks 6 and 7. RANK is kept for compatibility.
How do I rank in Excel without duplicates?
Use =RANK.EQ(C5,$C$5:$C$16)+COUNTIF($C$5:C5,C5)-1. The first of a tie keeps the rank and each later one gets the next number.
How do I rank with a condition, such as by region?
Use =COUNTIFS(region_range,B5,value_range,”>”&C5)+1. It counts the higher values in the same group and adds 1.
How do I rank from smallest to largest?
Add 1 as the third argument: =RANK.EQ(C5,$C$5:$C$16,1).
How do I list the top 5 names in Excel?
Create a unique rank, then use INDEX and MATCH with the numbers 1 to 5. In Microsoft 365, =TAKE(SORTBY(data,values,-1),5) also works.
Final checklist
- Lock the range with $ in every RANK formula.
- RANK.EQ for leaderboards, RANK.AVG for statistics.
- Add COUNTIF($C$5:C5,C5)-1 when every rank must be unique.
- COUNTIFS for ranks within a group.
- Check that unique ranks add up to n x (n + 1) / 2.
Turn rankings into reports
Browse the OneXcel Tutorial Hub for more formulas, explore ready-to-use Excel templates, or turn your ranking into a dashboard with the OneXcel Dashboard Builder.
