How to Rank in Excel Highest to Lowest

“Ranking” in Excel can mean two things: giving each value a rank number (1st, 2nd, 3rd), or sorting the list so the highest value is at the top. This guide covers both. You’ll use the RANK.EQ function to assign ranks from highest to lowest, handle ties, rank within groups, and then sort the list so the ranks are in order.

For the examples, imagine a list of names in A2:A11 and sales totals in B2:B11, with the ranks going in column C.

Rank Values Highest to Lowest with RANK.EQ

RANK.EQ returns the rank of a number within a list (full details are in the RANK.EQ function reference). Its syntax is =RANK.EQ(number, ref, [order]):

• number: the value you want to rank, such as B2.
• ref: the full list of values, such as $B$2:$B$11.
• order: optional. Leave it out or use 0 to rank highest to lowest (the largest value gets rank 1). Use 1 to rank lowest to highest.

Click the First Rank Cell

Click C2, next to the first value you want to rank. Add a header such as “Rank” in C1 if you don’t have one.

Enter the Formula

Type =RANK.EQ(B2,$B$2:$B$11) and press Enter. The dollar signs lock the range so it doesn’t shift when you copy the formula. Leaving out the order argument ranks from highest to lowest.

Copy the Formula Down

Double-click the fill handle (the small square at the bottom-right of C2) to copy the formula to the rest of the list. Each value now shows its rank, with 1 for the highest.

Sort by Rank (Optional)

To put the list in order, click any cell in the sales column, then go to Data > Sort Largest to Smallest (the Z-to-A button). Excel sorts the whole table and keeps each name with its value and rank. For more control, select the data, click Data > Sort, choose the Sales column, and pick Largest to Smallest. To measure the gap between the top-ranked and bottom-ranked values, see how to find the range in Excel.

Example: Ranking a Sales Team

Say five salespeople had these totals: Ana 8,200, Ben 9,500, Cara 7,100, Dev 9,500, and Eli 6,400. With =RANK.EQ(B2,$B$2:$B$6) copied down, Ben and Dev both get 1 because they tied for the top spot, Ana gets 3, Cara gets 4, and Eli gets 5. Rank 2 is skipped because two people share first place. With RANK.AVG, Ben and Dev would each get 1.5 instead, and the rest wouldn’t change.

How RANK.EQ Handles Ties

If two values are equal, RANK.EQ gives them the same rank and skips the next number. For example, two values tied for 2nd both get 2, and the next value gets 4. There are a few other options depending on what you need:

• Average the tied ranks: =RANK.AVG(B2,$B$2:$B$11) gives two values tied for 2nd a rank of 2.5 each.
• Give every value a unique rank: =RANK.EQ(B2,$B$2:$B$11)+COUNTIF($B$2:B2,B2)-1 breaks ties by order in the list, so the first tied value gets 2 and the second gets 3.
• Dense rank (no gaps): =SUMPRODUCT(($B$2:$B$11>B2)/COUNTIF($B$2:$B$11,$B$2:$B$11))+1 gives tied values the same rank and the next value the next number, such as 1, 2, 2, 3.

Rank Within Groups

To rank values separately for each group, such as each region in column D, use COUNTIFS: =COUNTIFS($D$2:$D$11,D2,$B$2:$B$11,”>”&B2)+1. It counts how many values in the same group are larger, then adds 1, so the top value in each region is ranked 1.

Sort Highest to Lowest with a Formula

In Excel for Microsoft 365 and Excel 2021 or later, you can create a sorted copy of the list that updates automatically. In an empty cell, enter =SORT(A2:B11,2,-1). The 2 sorts by the second column (Sales), and -1 means descending. To show only the top 3, wrap it in TAKE: =TAKE(SORT(A2:B11,2,-1),3).

Show Ranks as 1st, 2nd, 3rd

To display ordinal ranks, combine the rank with a suffix: =C2&IF(AND(MOD(C2,100)>=11,MOD(C2,100)<=13),”th”,CHOOSE(MIN(MOD(C2,10)+1,5),”th”,”st”,”nd”,”rd”,”th”)). This handles 11th, 12th, and 13th correctly. The result is text, so keep the number column for sorting.

Rank Values from More Than One Range

RANK.EQ can rank against two separate ranges on the same sheet if you put them in parentheses as one reference: =RANK.EQ(B2,($B$2:$B$11,$E$2:$E$11)). This is handy when the same type of data is split into two blocks, such as two teams side by side.

Show Percentile Ranks

To see where a value falls as a percentage instead of a position, use =PERCENTRANK.INC($B$2:$B$11,B2) and format the result as a percentage. A result of 90% means the value is higher than 90% of the list, which is useful for test scores or large data sets where position numbers are less meaningful.

Highlight the Top Values

To spot the leaders without sorting, select B2:B11 and go to Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items. Change 10 to the number you want, such as 3, and choose a color. The highlight updates as the numbers change.

Tips for Ranking in Excel

• Always lock the ref range with dollar signs ($B$2:$B$11), or ranks will be wrong after you copy the formula.
• Use RANK.EQ instead of the older RANK function. RANK still works for compatibility, but RANK.EQ is the current version.
• Turn the data into an Excel table (Ctrl + T) so formulas extend automatically when you add rows.
• Sort a copy of your data if you need to keep the original order.

Troubleshooting

The Formula Returns #N/A

The number you’re ranking isn’t in the ref range, often because the range shifted when you copied the formula. Add dollar signs to the range and copy it again.

Some Values Aren’t Ranked or Ranks Look Wrong

RANK.EQ ignores text, so numbers stored as text are skipped. Look for green triangles in the cells, select them, and choose Convert to Number.

Sorting Mixed Up My Rows

This happens when you select only one column before sorting and choose Continue with the current selection. Press Ctrl + Z, click a single cell in the data, and sort again so Excel expands the selection to the whole table.

Ranks Show as 1 for Every Row

Check that the ref argument covers the whole list, not just the current cell, and that calculation is set to automatic under Formulas > Calculation Options.

Frequently Asked Questions

What’s the difference between RANK, RANK.EQ, and RANK.AVG?

RANK and RANK.EQ work the same way and give tied values the same top rank. RANK.AVG gives tied values the average of their ranks. RANK is kept for compatibility with older versions.

How do I rank from lowest to highest instead?

Add 1 as the third argument: =RANK.EQ(B2,$B$2:$B$11,1). The smallest value gets rank 1. This is useful for times or costs where lower is better.

Can I sort data without affecting other columns?

Yes, but it’s usually a mistake, because each row’s values get separated. If you need it, select only that column and choose Continue with the current selection when Excel asks.

Can I undo a sort in Excel?

Yes. Press Ctrl + Z right after sorting. To get the original order back later, add a column with numbers 1, 2, 3 before sorting, then sort by that column.

Is there a way to automate ranking and sorting?

Yes. RANK.EQ updates automatically when values change, and the SORT function keeps a sorted copy up to date without re-sorting.