Introduction to the Excel RANK Function
The Excel RANK function is a valuable tool when you need to evaluate how values compare within a list. Whether it’s for ranking sales results, student scores, or athletic performance, this function gives you a clear hierarchy based on numerical data.
Excel supports this feature on Windows, macOS, and Ubuntu, making it versatile for every type of user.
What Does the RANK Function Do
The RANK() function tells you the position of a number in a list. It compares one value to the others and returns its rank. The rank is based on either descending (highest number gets rank 1) or ascending (lowest number gets rank 1) order.
For example, if a list contains:
{95, 87, 92}
Then:
=RANK(92, A1:A3)
Returns 2 because 92 is the second highest value.
Syntax of the RANK Function
=RANK(number, ref, [order])
- number: The value to rank
- ref: The range of numbers to compare
- order: Optional, use 0 for descending (default), 1 for ascending
Examples:
=RANK(A1, A1:A10)→ Rank in descending order=RANK(A1, A1:A10, 1)→ Rank in ascending order
Understanding the Ascending and Descending Orders
| Order | Description | Result Example |
|---|---|---|
| 0 | Descending (highest = 1) | 100 → Rank 1 |
| 1 | Ascending (lowest = 1) | 20 → Rank 1 |
🎯 Use ascending order for things like time, cost, or position
🎯 Use descending for scores, revenue, or ratings
Using RANK on Static Lists
You can directly enter values:
=RANK(87, {95,87,92})
Returns: 3
This is useful for quick checks or hard-coded analysis.
Using RANK with Cell References
For dynamic usage:
=RANK(B2, B$2:B$10)
As you drag this down, each cell in B2:B10 will be ranked against the full list. Use absolute references ($) to keep the range fixed.
Example: Ranking Exam Scores
| Name | Score | Rank Formula |
|---|---|---|
| Alice | 88 | =RANK(B2, B$2:B$6) |
| Bob | 75 | =RANK(B3, B$2:B$6) |
| Carol | 92 | =RANK(B4, B$2:B$6) |
Results:
- Carol → Rank 1
- Alice → Rank 2
- Bob → Rank 4
Example: Ranking Sales Data
| Salesperson | Revenue | Rank |
|---|---|---|
| John | $10,000 | =RANK(B2,B$2:B$6) |
| Emma | $8,000 | =RANK(B3,B$2:B$6) |
| Lucas | $12,000 | =RANK(B4,B$2:B$6) |
Used in performance dashboards and incentive programs 💼
Sorting Ties and Duplicate Values
If two values are the same, they will receive the same rank:
{90, 85, 90}
=RANK(90, A1:A3) → Both 90s return rank 1
The next unique value will have its rank adjusted. So 85 becomes rank 3
RANK vs RANK.EQ vs RANK.AVG
| Function | Description |
|---|---|
| RANK | Legacy function, same as RANK.EQ |
| RANK.EQ | Returns the top rank for ties |
| RANK.AVG | Returns the average rank for tied values |
For most purposes, RANK() or RANK.EQ() is enough
Use RANK.AVG() when ties should share average positions
Combining RANK with IF or COUNTIF
You can filter or conditionally rank:
=IF(B2>0, RANK(B2, B$2:B$10), "")
Use COUNTIF() to manage duplicates:
=RANK(B2,B$2:B$10)+COUNTIF(B$2:B2,B2)-1
This method ensures unique ranks even with duplicate scores.
Formatting Ranked Output
- Go to Home > Format Cells
- Choose Number for clear rank values
- Use Conditional Formatting to color-code top ranks:
- Green for top 3
- Red for bottom 3
📊 Helps visualize performance quickly
RANK Formula on Windows
- Use
F2to edit formulas - Press
Ctrl + Shift + Enterfor array-style entries if needed - Available since Excel 2003
💻 Smooth and stable on all modern Windows systems
RANK Formula on macOS
- Use
Control + Uto edit formula cells - All Excel for Mac versions from 2016 onward support
RANK() - Shortcuts and behavior mirror Windows
🍏 Mac users enjoy full compatibility
RANK Function on Ubuntu Using LibreOffice
In LibreOffice Calc, use:
=RANK(A1, A1:A10)
✅ Same syntax
✅ Works across all major Linux distros
✅ Good for open-source spreadsheet workflows
Creating Conditional Formatting for Ranks
Highlight top performers:
- Select your rank column
- Go to Home > Conditional Formatting
- Choose Top 10 items
- Customize style (e.g. gold fill for Top 3)
Great for competitions or leaderboard designs 🏆
Using RANK in Dynamic Dashboards
- Pair rank columns with
INDEX()andMATCH() - Create sortable leaderboards
- Refresh data using tables or slicers
Makes dashboards interactive and valuable for decision-making
Visualizing Rankings with Charts
- Use bar or column charts
- Label each column with rank value
- Sort by rank for best display
This helps stakeholders see high-performers at a glance 📈
Common Errors When Using RANK
| Error | Cause | Solution |
|---|---|---|
#VALUE! | Non-numeric data in range | Use VALUE() or clean data |
| Wrong ranks | Forgot absolute reference | Lock range with $ symbols |
| Misleading tie values | Use RANK.AVG or COUNTIF | Control tie behavior |
Double-check your inputs for clarity and correctness
FAQs About the RANK Function in Excel
What is the RANK function used for in Excel?
It determines the position of a number in a list compared to other numbers.
What’s the difference between RANK and RANK.EQ?
They are functionally the same. RANK.EQ is just the newer version.
How do I rank numbers in ascending order?
Use 1 as the third argument: =RANK(A1, A1:A10, 1)
Does RANK work in Excel on macOS and Linux?
Yes. It is supported on macOS and also in LibreOffice on Ubuntu.
Can I use RANK with percentages or decimals?
Yes. RANK works on any numerical values.
Final Thoughts on Excel RANK Function
The Excel RANK function is essential for anyone comparing values, scoring entries or analyzing performance. Whether you’re on Windows, macOS, or Ubuntu, it works seamlessly to provide accurate ranking data.
Use it in school, business, sports, or surveys to identify top performers, allocate resources or just keep track of progress.
Complete List of Windows Keyboard Shortcuts
If you need help for Windows, you can find a whole list of all keyboard shortcuts here.
https://keyboard-shortcuts.org/
Advertisment