Ravindra BagaleCourses & study guides Track your progress

Guides

RANK, LARGE and SMALL: Rank Sales Reps and List the Top 3

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Ravindra Bagale wants a leaderboard of ten reps for the monthly meeting: each rep's rank, and the top three names on a slide. Two reps have the same sales of ₹3,92,000, so the ranks must handle a tie too.

RANK.EQ gives each number its position in a list (1 = biggest). LARGE(range, k) returns the k-th biggest number and SMALL(range, k) the k-th smallest. Combine LARGE or a no-tie rank with INDEX and MATCH to get the names.

Download the practice file (rank-large-small-top-n.xlsx)

Excel file, 7 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Quick answer

  1. In C2 type =RANK.EQ(B2,$B$2:$B$11) and copy down.
  2. For ranks without ties, in D2 type =RANK.EQ(B2,$B$2:$B$11)+COUNTIF($B$2:B2,B2)-1.
  3. Top values: =LARGE($B$2:$B$11,1), then 2 and 3.
  4. Top names: =INDEX($A$2:$A$11,MATCH(1,$D$2:$D$11,0)), then 2 and 3.
  5. Lowest value: =SMALL($B$2:$B$11,1).
=RANK.EQ(B2,$B$2:$B$11)
=RANK.EQ(B2,$B$2:$B$11)+COUNTIF($B$2:B2,B2)-1
=INDEX($A$2:$A$11,MATCH(1,$D$2:$D$11,0))

The sales list and its ranks

RepSalesRANK.EQRank (no ties)
Ravindra Bagale₹4,18,00044
Shraddha Bagale₹3,92,00055
Ruhi Bagale₹4,55,00022
Shahrukh₹3,92,00056
Amir₹3,01,00099
Salman₹4,76,00011
Zoya₹3,50,00088
Ravina₹2,87,0001010
Raja₹4,33,00033
Rani₹3,65,00077

Shraddha Bagale and Shahrukh both have ₹3,92,000, so RANK.EQ gives both rank 5 and no one gets rank 6. The no-tie column adds COUNTIF($B$2:B2,B2)-1: the first of the two keeps 5, the second becomes 6.

How the formulas work

  1. RANK.EQ(B2,$B$2:$B$11) counts how many values are bigger than B2 and adds 1. The $ signs keep the list fixed when you copy down.
  2. Add a third argument 1 for ascending order (smallest = 1), for example when lower delivery time is better.
  3. COUNTIF($B$2:B2,B2) counts how many times this value has appeared so far. The range grows as you copy down because only the start is locked.
  4. LARGE($B$2:$B$11,2) returns the second biggest value even if it is a tie.

Top 3 table

PositionValue: LARGEName: INDEX + MATCH
1₹4,76,000Salman
2₹4,55,000Ruhi Bagale
3₹4,33,000Raja

With Microsoft 365 you can also write =TAKE(SORTBY(A2:B11,B2:B11,-1),3) to spill the top 3 rows in one go.

RANK.EQ, RANK.AVG and RANK

  • RANK.EQ: ties share the best rank (5, 5). Use this one.
  • RANK.AVG: ties share the average rank (5.5, 5.5).
  • RANK: the old name of RANK.EQ, kept for older files.

Ravindra Bagale's Tip

For a leaderboard, decide the tie rule before the meeting. If two reps share a rank, say so on the slide; quietly breaking the tie by row order makes one person unhappy for no reason.

Practice task

  1. Open the Leaderboard sheet. Check C2:C11 hold RANK.EQ.
  2. Find the two reps who share rank 5.
  3. Check D2:D11 give 1 to 10 with no repeats.
  4. Change Amir's sales to 500000 and watch the ranks and the top 3 table update.
  5. Add =SMALL($B$2:$B$11,1) to find the lowest sales.

Learn it properly

Course lessons:

Related guides: INDEX and MATCH · UNIQUE, SORT and SORTBY: Lists That Update Themselves · COUNTIF and COUNTIFS · $ Signs in Excel: Relative, Absolute and Mixed References (F4)

Frequently asked questions

How do I rank from smallest to largest?

Add 1 as the third argument: =RANK.EQ(B2,$B$2:$B$11,1).

Why do my ranks skip a number?

Two values are equal, so they share a rank and the next rank is skipped. Use the COUNTIF tie-break if you need 1 to 10 without gaps.

How do I rank within each city?

Use COUNTIFS: =COUNTIFS($C$2:$C$11,C2,$B$2:$B$11,">"&B2)+1 counts bigger values in the same city.

Can a PivotTable show the top 3?

Yes. Click the Rep filter arrow > Value Filters > Top 10 and change 10 to 3.