How to Use RANK, RANK.EQ, and RANK.AVG Functions in Excel + Tips

Hello everyone! Today i will show you how to use RANK, RANK.EQ, and RANK.AVG functions in Microsoft Excel step by step.

1. What are RANK, RANK.EQ, and RANK.AVG Functions?

Ranking functions in Excel help you find the rank position of a number inside a list (for example: ranking student test scores, sales rep performance, or race completion times).

• RANK: The classic ranking function available in all Excel versions (kept for backwards compatibility).
• RANK.EQ: The modern replacement for RANK (Excel 2010+). Returns the same top rank if numbers are tied (e.g., two top scores of 95 both get rank 1).
• RANK.AVG: Modern average ranking function (Excel 2010+). Returns the average rank if numbers are tied (e.g., two tied scores for 1st and 2nd place both get rank 1.5).

2. Formula Syntax & Parameters

=RANK.EQ(number, ref, [order])
=RANK.AVG(number, ref, [order])
=RANK(number, ref, [order])

• number: Required. The specific cell value you want to rank (e.g., student score in cell B2).
• ref: Required. The range or list of numbers to compare against (e.g., $B$2:$B$6).
• [order]: Optional. Defines ranking direction:
  • 0 (or omitted): Descending order (highest number gets Rank 1, best for test scores & sales).
  • 1: Ascending order (lowest number gets Rank 1, best for running times & golf scores).

3. 📋 Comparison & Examples: RANK.EQ vs RANK.AVG

Suppose we have student scores in range B2:B6:

4. 🎥 Video Tutorials (Khmer)

🎥 Tutorial 1: How to Use RANK Function in Excel

🎥 Tutorial 2: Practical RANK & RANK.EQ Examples

💡 Quick Tips for Using Excel Rank Functions

1. Always Lock Reference Range ($): Always use absolute cell references (like $B$2:$B$6 by pressing F4 key) for the ref parameter. If you don't lock the range, it will shift down when you copy the formula!
2. Choose Correct Order Parameter: Use 0 (or leave empty) for highest = 1 (test scores, revenue). Use 1 for lowest = 1 (running time, golf scores).
3. Use RANK.EQ over RANK: RANK is an old legacy function kept for compatibility with older Excel files. RANK.EQ is faster and standard for Excel 2010 and newer.
4. Fix Duplicate Ranks without Skipping Numbers: If you want unique ranks without ties (e.g., 1, 2, 3, 4 instead of 1, 2, 2, 4), combine RANK.EQ with COUNTIF: =RANK.EQ(B2, $B$2:$B$6) + COUNTIF($B$2:B2, B2) - 1.

📚 Related Resources & Tutorials

• How to Use MIN & MAX Function in Excel
• How to Use COUNTIF & COUNTIFS Function in Excel
• How to Use SORT Function in Excel
• How to Use AVERAGE Function in Excel
• Top 15 Basic Excel Formulas for Beginners ↗