Ranking puts values in order and tells you the position of each one, such as first, second and third. People rank students by score, salespeople by revenue, products by sales and teams by points. Excel can do this with the RANK.EQ and RANK.AVG functions, and with a few formulas for special cases such as ties and ranking within groups. This guide shows the syntax, a worked example with five scores that you can check by hand, how to rank from highest to lowest or the reverse, how ties are handled and how to break them, how to rank within each group, how to pull out the top three values, and how to calculate a percent rank. It also lists the common mistakes.
Updated November 2026
In this guide
- Example data
- Method 1: RANK.EQ
- Rank from lowest to highest
- Method 2: RANK.AVG for ties
- Break ties with a second rule
- Tie-break with a second column
- Rank within a group
- Find the top N values
- Percent rank
- Sort the list by rank
- Highlight the top ranks
- Example: a sales leaderboard
- Ranking in a table
- Common mistakes
- Practice problems
- Quick recap
Example data
Use five students and their scores.
| Student | Score |
|---|---|
| Ali | 90 |
| Sara | 85 |
| Omar | 85 |
| Hina | 70 |
| Zain | 60 |
Ali has the highest score. Sara and Omar have the same score of 85, so they are tied. Hina has 70, and Zain has the lowest score of 60. By hand, the ranks from the highest are 1 for Ali, 2 for both Sara and Omar, 4 for Hina and 5 for Zain. Notice that rank 3 is skipped, because two people share rank 2.
Method 1: RANK.EQ
RANK.EQ(number, ref, order) returns the rank of a number in a list. Put the scores in B2:B6. In C2 type =RANK.EQ(B2,$B$2:$B$6) and fill it down. The result is 1 for 90, 2 for 85, 2 for the other 85, 4 for 70 and 5 for 60. The dollar signs lock the list, so the range does not move when you copy the formula. The older function RANK works in the same way, and it is kept for compatibility. RANK.EQ is available in Excel 2010 and later.
Rank from lowest to highest
The third argument, order, decides the direction. If you leave it out or use 0, the largest value gets rank 1. If you use 1, the smallest value gets rank 1. For golf scores, race times or costs, where a lower value is better, use =RANK.EQ(B2,$B$2:$B$6,1). With our scores, 60 becomes rank 1, 70 becomes rank 2, the two 85 scores become rank 3 and 90 becomes rank 5. Choose the order that matches the meaning of your data.
Method 2: RANK.AVG for ties
RANK.AVG gives tied values the average of the ranks they would take. The syntax is the same: =RANK.AVG(B2,$B$2:$B$6). For our scores, Sara and Omar would take ranks 2 and 3, so each gets 2.5. The full result is 1, 2.5, 2.5, 4 and 5. This method is used in statistics, since the ranks still add up to the same total of 15 as without ties. Use RANK.EQ when you want tied people to share the same position, and RANK.AVG when you need the average.
Break ties with a second rule
Sometimes you need a unique rank for each row. A simple tie-breaker is to add a COUNTIF for the rows above: =RANK.EQ(B2,$B$2:$B$6)+COUNTIF($B$2:B2,B2)-1. For the first 85, the rank is 2 plus 1 minus 1, which is 2. For the second 85, it is 2 plus 2 minus 1, which is 3. The ranks become 1, 2, 3, 4 and 5. The person listed first wins the tie, so sort the list by the tie-breaking rule that you want, such as the earlier date or the higher secondary score, before using this formula.
Tie-break with a second column
If you have a second score, such as an attendance score in column C, use COUNTIFS to count the people who are better on the first score, or equal on the first and better on the second: =COUNTIFS($B$2:$B$6,">"&B2)+COUNTIFS($B$2:$B$6,B2,$C$2:$C$6,">"&C2)+1. This gives a fair order without relying on the list position. It is a good method for competitions, where the rules say which criterion decides a tie. Test the formula with a few cases, including a complete tie, to see what it does.
Rank within a group
To rank each person within a department or a class, use COUNTIFS. With groups in column A and scores in column B, the formula =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,">"&B2)+1 counts the people in the same group with a higher score and adds 1. If a person has two higher scores in the same group, the rank is 3. Our COUNTIFS guide explains the function. In Excel 365, you can also use SORTBY and other dynamic array functions to build ranked lists.
Find the top N values
To list the top three scores, use LARGE: =LARGE($B$2:$B$6,1) returns 90, with 2 it returns 85, and with 3 it returns 85 again. To show the names, combine INDEX and MATCH, as in our XLOOKUP guide, with =XLOOKUP(LARGE($B$2:$B$6,1),$B$2:$B$6,$A$2:$A$6), which returns Ali. For ties, the lookup returns the first match, so use the unique rank from the tie-break formula to avoid repeats. SMALL works in the same way for the lowest values.
Percent rank
PERCENTRANK.INC tells you the relative position of a value, from 0 to 1. =PERCENTRANK.INC($B$2:$B$6,B2) returns 1 for 90, 0.5 for each 85, 0.25 for 70 and 0 for 60. It is calculated as the number of values below, divided by the number of values minus one. Multiply by 100 to get a percentile score, so 85 is at the 50th percentile in this small group. Percent rank is useful when you want to compare positions in lists of different sizes.
Sort the list by rank
After ranking, you may want to display the results from first to last. Select the data and use Data, Sort, with the rank column from smallest to largest, as in our sorting guide. If you sort a list that has formulas that refer to a range, make sure that the formulas use absolute references for the range, as in $B$2:$B$6, so the result stays correct after the sort. In Excel 365, =SORT(A2:B6,2,-1) returns a sorted copy without changing the original list.
Highlight the top ranks
Use conditional formatting to color the top three. Select the rank column, choose Conditional Formatting, Highlight Cells Rules, Less Than, and type 4. The ranks 1, 2 and 2 are highlighted. A formula rule can color the whole row, such as =$C2<=3. See our conditional formatting guide. Gold, silver and bronze fills can help in a leaderboard, but keep the colors simple, so the table is easy to read.
Example: a sales leaderboard
Imagine five salespeople with monthly sales of 12,000, 9,500, 9,500, 7,200 and 15,000. Using RANK.EQ from the highest, the person with 15,000 is first, the person with 12,000 is second, the two with 9,500 share third place, and the person with 7,200 is fifth. A manager may give a prize to the top three, so both people with 9,500 qualify. Add a column that shows =IF(C2<=3,"Prize","") to mark them. If the budget allows only three prizes, use the tie-break formula and a second criterion, such as the number of new customers.
Ranking in a table
If your data is an Excel table, use structured references, such as =RANK.EQ([@Score],[Score]). The ranks update automatically when you add a new row or change a score, and the formula needs no dollar signs. Our table guide explains how tables work. After changing scores, the table does not re-sort by itself, so sort it again or use a dynamic array formula such as SORT to keep a ranked view that always stays in order.
Common mistakes
The most common mistake is to forget the dollar signs in the range, so the rank list moves when you copy the formula. Another is to use the wrong order argument. A third is to include the header in the range. A fourth is to expect rank 3 after two people tie for rank 2. A fifth is to rank text values, which RANK ignores. A sixth is to forget that a rank by group needs COUNTIFS, not RANK. Check the result with a small list, as in this guide.
Practice problems
Try these. Problem 1: the rank of 70 in the list 90, 85, 85, 70 and 60 is 4. Problem 2: with RANK.AVG the two scores of 85 get 2.5. Problem 3: with the ascending order the score of 60 gets rank 1. Problem 4: write a formula for a unique rank that gives the ranks 1 to 5. Problem 5: find the third largest value, which is 85, with LARGE.
Quick recap
Use RANK.EQ for the standard rank and RANK.AVG when ties should share the average rank. Set the order argument to 1 for the smallest first. Lock the list with dollar signs. Break ties with COUNTIF or COUNTIFS, rank within groups with COUNTIFS and list the top values with LARGE and a lookup. Use PERCENTRANK.INC for the relative position. Remember that tied values skip the next rank, and check a small example before you apply the formula to a large list.
Frequently asked questions
What is the RANK formula in Excel?
=RANK.EQ(number, range, order).
What is the difference between RANK.EQ and RANK.AVG?
RANK.EQ gives tied values the same rank, and RANK.AVG gives them the average rank.
How do I rank from lowest to highest?
Set the order argument to 1.
How do I rank within a group?
Use COUNTIFS to count the higher values in the same group and add 1.
How do I break a tie?
Add a COUNTIF of the same values above, or use a second criterion.


