Using RANK in Excel: Rank Numbers, Handle Ties, and More

How-to basics · Updated November 20, 2026

To rank a number in Excel, use RANK.EQ: with scores in B2:B6, enter =RANK.EQ(B2,$B$2:$B$6,0) in C2 and fill down. The final 0 ranks the largest value as 1; use 1 to rank the smallest value as 1. For ties that should receive an average rank, use RANK.AVG instead.

How RANK works in Excel

Excel’s ranking functions compare one number with the other numeric values in a reference range. The result is an ordinal position: first, second, third, and so on. For example, ranking exam results can identify the highest score, while ranking sales can show which product sold the most. A rank is not the same as a percentage or percentile; it tells you a value’s position in the group, not how far it is from the others.

In current Microsoft 365 Excel for Windows, the two main functions are RANK.EQ and RANK.AVG. Both take the value to rank, the range to compare it with, and an optional order argument. If you omit the order argument, Excel ranks in descending order, so the largest number is rank 1. Set the argument to 1 for ascending order, where the smallest number is rank 1. Any nonzero order value requests ascending ranking, but using 1 makes the intention clear.

The older RANK function remains available for compatibility with older workbooks. Microsoft keeps it so existing formulas continue to work, but RANK.EQ is the clearer choice for new formulas when you want the traditional treatment of ties.

Make a basic ranking formula

Suppose a worksheet lists five scores in B2:B6: 95, 88, 88, 76, and 60. Put the rank next to each score. With the score in B2 and the full comparison range in B2:B6, enter this in C2:

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

Copy or fill the formula down through C6. The dollar signs keep the comparison range fixed as the formula moves, while the reference to B2 changes to B3, B4, and so on. The results are 1, 2, 2, 4, and 5. The two 88s share rank 2, so rank 3 is skipped. This is often called competition ranking: if two people tie for second, the next person is fourth.

For an ascending rank, with the same scores in B2:B6 and the value being ranked in B2, use this formula in C2:

=RANK.EQ(B2,$B$2:$B$6,1)

Now 60 receives rank 1, 76 receives rank 2, both 88s receive rank 3, and 95 receives rank 5. The formula still compares each value against the complete set; only the direction changes.

Choose how ties should be ranked

Decide how to treat equal values before choosing a formula. RANK.EQ gives identical values the same rank and leaves gaps afterward. That is intuitive for contests in which tied participants share a position. If two entries are tied for second place, the next position is fourth.

RANK.AVG also gives tied values the same rank, but assigns them the average of the positions they occupy. With scores 95, 88, 88, 76, and 60 in B2:B6, and the value in B2, put this formula in C2 and fill it down:

=RANK.AVG(B2,$B$2:$B$6,0)

The ranks are 1, 2.5, 2.5, 4, and 5. The tied 88s would occupy positions 2 and 3, whose average is 2.5. This can be useful for statistical summaries or analyses where the average position is preferable to a skipped rank. It may be less suitable when a report expects whole-number places.

Neither function breaks a tie into separate positions by itself. If a report needs a unique sequence, decide on a secondary rule—such as earlier date, alphabetical name, or another score—and build that rule into the calculation or sort the data by both fields. Do not assume Excel will choose a meaningful winner between equal values.

Rank values within groups

A common question is how to rank each person only against others in the same category, such as sales representatives within their region. One option is COUNTIFS, which counts how many values in the matching group are greater than the current value, then adds 1. Suppose regions are in A2:A20 and sales are in B2:B20. To rank the sales in B2 among rows with the same region as A2, enter this formula in C2 and fill down:

=1+COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,">"&B2)

For descending ranks, every higher sale in the same region contributes one to the count. A top sale has no higher values and therefore receives rank 1. Equal sales receive the same rank, and subsequent positions have gaps, matching the usual competition-ranking approach. The formula compares the region in each row with A2, then checks sales against B2. Absolute references keep the full data ranges in place when the formula is copied down.

This grouped formula assumes the values being ranked are numeric and that the category labels match consistently. A trailing space in one region name, for instance, can make a row look like it belongs to a group while Excel treats it as a different label. Clean or standardize category data if results appear inconsistent.

Give tied values unique ranks

Sometimes a ranking list must contain 1, 2, 3, and so forth without duplicate rank numbers. One simple rule is to rank tied entries by the order in which they appear in the sheet: the first occurrence gets the better position. With scores in B2:B20 and the first score in B2, enter this formula in C2 and fill down:

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

The first part calculates the ordinary descending rank. The COUNTIF portion counts how many times the current score has appeared from the first data row through the current row, then subtracts 1. If a score appears for the first time, nothing is added; if it appears again, the later occurrence moves down one place. With scores 95, 88, 88, 76, 60, the results become 1, 2, 3, 4, 5.

This is not a neutral tie-break: it favors the row that appears first. If the order should depend on another field, such as a test’s second section or a timestamp, use that criterion explicitly rather than relying on row position. In some workbooks it is clearer to sort by the main score and then the tie-break field, and label the resulting sequence as a position rather than a pure rank.

Rank numbers versus sorting a list

A rank formula adds a calculated result beside the original data; it does not rearrange the records. That distinction matters when names, dates, or other details must stay attached to each score. If you simply sort one column on its own, values can become separated from the people or products they describe.

To reorder complete records in Excel for Microsoft 365 on Windows, select a cell in the data and use Data > Sort. Choose the score column and select Largest to Smallest or Smallest to Largest. If the data has headers, confirm that Excel recognizes them in the Sort dialog. Sorting is useful for a top-to-bottom presentation; formulas are more useful when the source order should remain unchanged or ranks need to update as values change. You can also rank first and sort the whole table by the rank column.

With an Excel table, a rank formula can be filled into the calculated column as new rows are added, provided the formula uses the intended comparison range. Check that the range expands to include new records; a fixed range such as B2:B20 will not automatically include a new value in row 21. Structured table references or an expanded range can help, but verify the result after adding data.

Check common RANK formula problems

  • The largest value gets rank 1 unexpectedly: The order argument is 0 or omitted, which means descending rank. Use 1 when the smallest should be rank 1.
  • Ranks skip a number: This is normal for RANK.EQ when there is a tie. Use RANK.AVG for average positions, or use a deliberate tie-break method if every row needs a unique rank.
  • The result is #N/A: The number being ranked may not be present among the numeric values in the reference range. Check that the cell is included and that it contains a number rather than numeric-looking text.
  • Results change after filling down: The comparison range may be relative. Lock its endpoints with dollar signs, as in $B$2:$B$20, while leaving the current-row reference relative.
  • A value seems ignored: RANK functions compare numbers. Text entries in the reference are not ranked as numeric scores. Imported values that look like numbers may be stored as text; convert them to numbers before ranking.
  • Group ranks are unexpectedly different: Check for inconsistent category labels, stray spaces, or a formula range that omits some records. Confirm that each row is compared only with the intended group.

When a result looks wrong, inspect one row by hand: list the numeric values that should be in its comparison group, count how many are above or below the target, and compare that position with the formula’s answer. This quickly distinguishes a direction problem from a range, data-type, or tie issue.

Display and use rank results

Ranks are usually whole numbers, except when using RANK.AVG with ties. Format the rank column as General or Number, and use decimal places if average ranks should display values such as 2.5. Number formatting changes how a result appears, not the underlying ranking calculation.

To emphasize high performers, you can apply conditional formatting to the rank results. Select the rank cells, then use Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items. Adjust the rule’s number if you want a different cutoff. Remember that this rule highlights the largest rank numbers, which are the lowest positions in a descending ranking. To highlight the best ranks instead, a simple alternative is to apply a rule that highlights values less than or equal to a chosen rank, or use a suitable formula-based conditional-formatting rule.

A rank alone can hide how close the values are. If one score is 99 and the next is 98, their positions are adjacent; if one is 99 and the next is 40, the ranks are still adjacent despite the large gap. For decisions, show the original value alongside the rank and consider adding a difference or other relevant measure. A ranking summarizes order; it does not explain performance or establish why one item is better.

Frequently Asked Questions

What is the difference between RANK and RANK.EQ in Excel?

RANK is the older function name, retained for compatibility. RANK.EQ makes the equal-rank behavior explicit: tied values receive the same rank, with a gap after the tie. For new workbooks, use RANK.EQ when that is the behavior you want.

How do I rank from lowest to highest in Excel?

Set the order argument to 1. For example, with the value in B2 and the complete comparison range in B2:B20, enter =RANK.EQ(B2,$B$2:$B$20,1) in the rank column. The smallest numeric value receives rank 1.

How do I rank without skipping numbers after a tie?

Choose an explicit tie-break rule and add it to the calculation. The example using RANK.EQ plus COUNTIF assigns unique positions based on first appearance in the list. That method is appropriate only if row order is an acceptable tie-break; otherwise use a meaningful secondary field.

Can I rank text alphabetically with RANK?

No. RANK.EQ and RANK.AVG rank numeric values, not words. To order names, use Excel’s sort tools. For a ranked list based on a numeric measure, rank that measure and keep the names in adjacent rows so each result remains associated with the correct record.

Does RANK update when the numbers change?

Yes. The rank recalculates when values in its referenced cells change, as long as Excel calculation is enabled and the formula’s comparison range includes those cells. If you add rows beyond a fixed range, expand the range or use a suitable table-based setup so the new records are considered.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.