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.

Excel RANK.EQ formula ranking sales reps, with Carla Diaz and Hugo Silva tied at rank 1 and Felix Ramos at rank 3.
RANK.EQ ranks the highest value as 1.

Quick answer

You wantFormula
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.

Free download: Get the RANK practice file (.xlsx). It ranks 12 sales reps five ways, builds a Top 5 table and checks that every rank from 1 to 12 is used once.

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.

Excel RANK.EQ ranking 12 sales reps by sales, with two reps tied at 61,750 both ranked 1 and no rank 2.
Ties share a rank and the next rank is skipped.

Step 2: RANK.AVG: ties share the average rank

=RANK.AVG(C5,$C$5:$C$16)
RepSalesRANK.EQRANK.AVG
Carla Diaz61,75011.5
Hugo Silva61,75011.5
Ben Ortiz48,50066.5
David Lee48,50066.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.

Excel RANK.AVG next to RANK.EQ, with the tie at 48,500 shown as rank 6 by RANK.EQ and 6.5 by RANK.AVG.
RANK.AVG averages the tied ranks.

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.

Excel RANK.EQ with the ascending option, giving the lowest sales of 36,800 rank 1.
Order 1: the smallest value is rank 1.

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.

Excel unique rank without duplicates using RANK.EQ plus COUNTIF, with tied reps ranked 1 and 2 and a check that the ranks sum to 78.
COUNTIF breaks ties in list order.
Tie order: This method breaks ties by position in the list. If the tie should go to, say, the rep with more deals, add that column as a second condition, or sort the list first so the right person appears first.

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
RegionRank 1Rank 2Rank 3
NorthAna Cruz (52,300)David Lee (48,500)Iris Tan (36,800)
SouthFelix Ramos (55,200)Ben Ortiz (48,500)Jon Reyes (42,600)
EastCarla Diaz (61,750)Kara Lim (50,400)Grace Kim (44,100)
WestHugo 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.

Excel COUNTIFS rank within each region, with North reps Ana Cruz, David Lee and Iris Tan ranked 1, 2 and 3.
COUNTIFS gives a rank per region.

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)
RankRepRegionSalesShare
1Carla DiazEast61,75010.5%
2Hugo SilvaWest61,75010.5%
3Felix RamosSouth55,2009.4%
4Ana CruzNorth52,3008.9%
5Kara LimEast50,4008.6%
Top 5281,40047.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.

Excel Top 5 sales reps list built with INDEX, MATCH and LARGE, totaling 281,400 or 47.8 percent of sales, with a check that ties.
INDEX, MATCH and LARGE build the Top 5.
Try it: Change Leo Wong’s sales (Sales Rank!C16) from 47,200 to 62,000. He becomes rank 1, the Top 5 becomes Leo Wong, Carla Diaz, Hugo Silva, Felix Ramos, Ana Cruz, and in the West region Leo is now 1 and Hugo 2. Both checks still show Ties.

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.
Practice file: Download the RANK practice file. Change the yellow sales values and watch every rank and the Top 5 update.

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.