RANK.EQ returns the rank of a number within a list of numbers, giving equal ranks to ties. It is useful for comparing performance, scores, or sales figures quickly.
=RANK.EQ(number, ref, [order])What does RANK.EQ do?
RANK.EQ is a statistical function that tells you where a number stands compared to a group of numbers. You give it a value, a range to compare against, and optionally whether you want descending or ascending order. It returns a rank, where 1 means the highest (or lowest, depending on your choice).
Ranking is everywhere in office work and study: class positions, sales leaderboards, survey scores, and budget priorities. Doing it by eye is slow and error-prone, especially when lists change. RANK.EQ automates the comparison so you can sort, filter, or highlight top performers without manual counting.
Imagine you manage a sales team and need to identify the top three performers for a bonus. Instead of sorting the whole sheet and counting rows, you add a single RANK.EQ formula next to each sales figure. The formula instantly shows each person's position, and you can copy it down the column. If two people tie, they get the same rank, which is exactly what you want for fair recognition.
When to use RANK.EQ
- Ranking students by exam score to determine class position.
- Finding the top-selling product or salesperson in a monthly report.
- Comparing employee performance ratings across a department.
- Prioritising tasks or projects by urgency score.
- Ranking survey responses to identify most common answers.
- Assigning competition places where ties should share the same rank.
RANK.EQ syntax
=RANK.EQ(number, ref, [order])| Argument | Required? | What to enter |
|---|---|---|
number | Required | The value you want to rank. It can be a number, a cell reference, or a formula that returns a number. Example: B2 or 95. |
ref | Required | The range of numbers to compare against. It must contain the number you are ranking. Example: B2:B10. |
order | Optional | Optional. Use 0 or omit for descending order (largest number gets rank 1). Use 1 for ascending order (smallest gets rank 1). Example: 0 or 1. |
What it returns: RANK.EQ returns a number representing the rank of the specified value within the reference range. If there are ties, the same rank is given to each tied value, and subsequent ranks are skipped (e.g., two 2nd places mean no 3rd place).
RANK.EQ examples
1 Rank a single score in a small list
This example ranks the score in cell B2 against the other scores in B2:B6. The formula uses descending order (0), so the highest score gets rank 1. The score 85 is the second highest, so the result is 2.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | |||
| 2 | Alice | 85 | 4 | ||
| 3 | Bob | 92 | |||
| 4 | Carol | 78 | |||
| 5 | David | 88 | |||
| 6 | Eve | 95 | |||
| 7 |
2 Rank sales figures with ties
Here we rank the sales amount in B2 against all sales in B2:B7. Two people have 1200, so they both receive the same rank (3). The next value gets rank 5 because ranks 3 and 4 are shared. The formula uses descending order to put the highest sales first.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Salesperson | Sales | |||
| 2 | John | 1200 | 3 | ||
| 3 | Sarah | 1500 | |||
| 4 | Mike | 1200 | |||
| 5 | Emma | 900 | |||
| 6 | Tom | 1800 | |||
| 7 | Lisa | 1100 | |||
| 8 |
3 Rank in ascending order (lowest gets rank 1)
This example ranks the time in B2 against other times in B2:B6. Using order 1 means the smallest time gets rank 1. The time 12.5 is the second smallest, so it returns 2. This is useful for races or any metric where lower is better.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Runner | Time (sec) | |||
| 2 | Alex | 12.5 | 3 | ||
| 3 | Ben | 11.8 | |||
| 4 | Chris | 13.2 | |||
| 5 | Dan | 12 | |||
| 6 | Eli | 14.1 | |||
| 7 |
4 Rank products by revenue in a business report
We rank the revenue of product A (B2) against all product revenues in B2:B8. The highest revenue gets rank 1. Product A has 5000, which is the third highest, so the result is 3. This helps identify top-performing products.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Revenue | |||
| 2 | Product A | 5000 | 6 | ||
| 3 | Product B | 7500 | |||
| 4 | Product C | 6200 | |||
| 5 | Product D | 4800 | |||
| 6 | Product E | 9100 | |||
| 7 | Product F | 5300 | |||
| 8 | Product G | 6700 | |||
| 9 |
Common RANK.EQ errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #N/A | The number you are ranking is not found in the reference range. | Make sure the number appears in the ref range. Check for typos or hidden characters. |
| #VALUE! | The number argument is not numeric, or the ref range contains non-numeric values that cannot be ranked. | Ensure the number is a valid number. Remove or ignore text in the ref range, or use a range that contains only numbers. |
| #REF! | The reference range is invalid, often because rows or columns were deleted. | Adjust the range to a valid set of cells. Use absolute references (e.g., $B$2:$B$10) to prevent breakage when copying. |
| Wrong rank returned | The order argument is set incorrectly (0 vs 1) or omitted when you needed ascending order. | Decide whether largest (0) or smallest (1) should get rank 1, and set the order argument accordingly. |
Tips for using RANK.EQ
- Use absolute references for the ref range (e.g., $B$2:$B$10) so the formula can be copied down without the range shifting.
- If you want unique ranks even with ties, use RANK.EQ combined with COUNTIF to break ties.
- Remember that RANK.EQ gives equal ranks to duplicates, which may skip subsequent ranks.
- For ascending order (smallest = rank 1), set the order argument to 1.
- RANK.EQ is the modern replacement for the legacy RANK function; use it for new workbooks.
Frequently asked questions
What is the difference between RANK.EQ and RANK?
RANK.EQ is the newer function introduced in Excel 2010. It behaves like RANK but is more explicit about ties. RANK is still available for compatibility, but RANK.EQ is recommended for new formulas.
How does RANK.EQ handle ties?
RANK.EQ assigns the same rank to tied values. For example, if two values tie for 2nd place, both get rank 2, and the next value gets rank 4 (no rank 3).
Can I rank only visible cells or apply filters?
RANK.EQ includes all cells in the ref range, even hidden ones. To rank only visible cells, you need a more complex formula using SUBTOTAL or AGGREGATE.
Why does RANK.EQ return #N/A?
This happens when the number you are ranking is not found in the reference range. Double-check that the number exists in the range and that there are no typos.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. RANK.EQ reference