16 - Nov - 2026

Excel has a better way to rank your data without moving a single row

Picture a sales tracker with sales reps grouped alphabetically by region and deals arranged by their close dates. If someone asked which sales rep was leading in deals closed, the obvious move would be to sort the revenue column in Excel from largest to smallest. You’ll have your answer in seconds.

The problem is that the close dates would move too, and the regional groupings would scatter across the sheet. Just to answer one question, you could leave everyone else using the workbook wondering what happened to the layout. Thankfully, Excel has another way to rank the data while leaving every row where it belongs.

Ranking doesn’t have to mean rearranging

Let the formula keep score

A RANK.EQ formula in Excel's formula bar.
Screenshot by Ada

Sorting changes where values appear on your worksheet, while ranking tells you where each value would be if those records were ordered. If your project logs need to stay chronological, your classroom sheet needs to remain alphabetical, or your sales workbook needs to keep employees grouped by region, you can preserve all of that and add a rank column beside the data.

Here’s the formula you’ll need:

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

number is the value you want to rank, while ref is the range Excel should compare it against. The optional order argument controls which end gets first place. Leave it out or use 0 when the largest value should rank first. Use a non-zero value, such as 1, when smaller values should rank higher.

Now, imagine your revenue values sit in B2:B25. In C2, you could enter:

=RANK.EQ(B2, $B$2:$B$25, 0)

When you drag the formula down the column, each employee gets a position without a single row moving. Locking the comparison range with dollar signs turns it into an absolute cell reference, so the range stays fixed as the formula moves down the sheet.

If Sam is on row 14 with $45,000 in revenue and ranks fourth, then closes another $20,000 deal, he’ll still be on row 14. Only his rank will recalculate. Sam could jump to first place while the spreadsheet keeps its original structure.

RANK.EQ also handles ties predictably. If two people tie for second place, both receive rank 2, and the next person gets rank 4. Excel leaves third place vacant. If that numbering looks awkward in your analysis, you can use RANK.AVG instead. The two values occupying second and third place would each receive a rank of 2.5.

You may also run across the older RANK function in existing workbooks. It behaves much like RANK.EQ, including how it handles ties, but Excel now keeps it as a compatibility function. For new sheets, RANK.EQ is the better choice.

Take RANK.EQ beyond the basics

Break ties without breaking your ranking

A RANK.EQ formula with a secondary ranking value in Excel's formula bar.
Screenshot by Ada

Sometimes two identical rank numbers are perfectly fine. Other times, they’re a nuisance, especially when another formula needs every item to have a unique position. In those cases, you can break ties according to their order on the worksheet with a running COUNTIFS:

=RANK.EQ(B2, $B$2:$B$25, 0) + COUNTIFS($B$2:B2, B2) - 1

Because the range $B$2:B2 expands as you drag the formula down, the first instance of a duplicate keeps its original rank. The second matching value gets one added, the third gets two, and so on. You end up with unique integers while keeping the source rows untouched.

You can also use a secondary performance metric as a more meaningful tiebreaker. For instance, if column B contains points and column D contains the average close days, you could add a secondary ranking value to your formula:

=RANK.EQ(B2, $B$2:$B$25, 0) + (0.001 * RANK.EQ(D2, $D$2:$D$25, 1))

Here, column B determines the primary rank, while column D ranks average close days in ascending order (lower values perform better). The 0.001 multiplier keeps the secondary rank small enough that it does not overtake the primary one in a dataset this size. The main score therefore determines the overall order, while the decimal portion separates people with identical scores.

Don’t let blanks wreck your ranks

An IFERROR plus COUNTA and RANK.EQ formula in Excel's formula bar.
Screenshot by Ada

Spreadsheets rarely contain pristine columns of numbers from top to bottom. You’ll likely find blanks, dashes, placeholders, and notes mixed in with your values. One simple approach is to catch anything RANK.EQ cannot process and return a fallback value instead:

=IFERROR(RANK.EQ(B2,$B$2:$B$25,0), COUNTA($B$2:$B$25)) 

Numeric entries will still receive their normal ranks, while cells that cause an error receive the same fallback number based on the count of non-empty cells in the range. This can keep incomplete or unsuitable entries out of the meaningful rankings without forcing you to clean up your source data first.

Make the ranking useful outside the rank column

A LET plus SWITCH and RANK.EQ formula in Excel's formula bar.
Screenshot by Ada

A column of 1, 2, 3, and 4 works, but a dashboard often looks nicer with 1st, 2nd, and 3rd. You can build those suffixes with LET and SWITCH:

=LET(r, RANK.EQ(B2, $B$2:$B$25), r & SWITCH(MOD(r, 100), 11, "th", 12, "th", 13, "th", SWITCH(MOD(r, 10), 1, "st", 2, "nd", 3, "rd", "th"))) 

The extra logic handles the irregular 11th, 12th, and 13th cases, so you don’t get ‘11st’ popping up on your sheet. You can even keep your master table untouched while showing management a tidy top 10 elsewhere. In modern Excel, dynamic array functions can produce that secondary view:

=TAKE(SORT(FILTER(A2:C25, B2:B250),3,1),10) 

Assuming column C contains your calculated rank, this formula removes rows without a usable value in column B, sorts the remaining records by rank from lowest to highest, and returns the first ten. Your source table keeps its existing arrangement while another part of the worksheet displays the ten best-ranked records.

A TAKE, SORT, and FILTER formula that drops a top ten list on another part of the Excel spreadsheet.
Screenshot by Ada

RANK.EQ can even handle values that are scattered across separate cells rather than in one continuous column. If you were comparing points in D8, E8, F8, and G8, for example, you could use a reference union:

=RANK.EQ(D8,(D8,E8,F8,G8)) 

The outer parentheses allow Excel to treat those individual cells as one reference, so you can rank a value against a scattered group without rearranging your worksheet first.

Your rows can stay right where they are

A well-organized worksheet usually has an order for a reason. Every time you re-sort it, you disturb that structure, risk breaking row-based relationships, and create more work the next time the data changes.

By replacing manual sorting with a formula like RANK.EQ, you can keep the worksheet in its original order while still seeing who or what comes out on top. Your data stays put, and the rankings keep updating around it.

Leave a Reply

Your email address will not be published. Required fields are marked *