Excel Rank Formula With Ties: How to Rank Duplicate Values

Excel’s RANK.EQ function gives equal values the same rank, while the next rank number may be skipped.

Learn how to handle that behavior, choose a ranking method, and use formulas when you need a unique position for every row.

How does Excel rank tied values?

For a standard competition ranking, identical values share a rank.

If the values are 95, 87, 87, and 72, their ranks are 1, 2, 2, and 4.

The tied entries both occupy second place, so there is no third-place rank.

Excel’s RANK.EQ function follows this rule.

Its syntax is RANK.EQ(number, ref, [order]): number is the value to rank, ref is the full comparison range, and order determines the direction.

Omit order or use 0 for descending ranking, where the largest number is rank 1.

Use 1 for ascending ranking, where the smallest number is rank 1.

Use RANK.EQ to keep equal values tied

Suppose scores are in cells B2:B6, with these values:

• B2: 95
• B3: 87
• B4: 87
• B5: 72
• B6: 60

In C2, enter =RANK.EQ(B2,$B$2:$B$6,0), then fill the formula down.

The expected ranks are 1, 2, 2, 4, and 5.

The dollar signs keep the comparison range fixed as the formula is copied.

To rank lowest values first, use =RANK.EQ(B2,$B$2:$B$6,1).

With the same scores, the expected ranks are 5, 3, 3, 2, and 1.

Excel’s older RANK function also returns this style of ranking and is retained for compatibility; RANK.EQ makes the tie behavior explicit.

Choose the ranking style you need

“Ranking with ties” can mean different things.

Pick the rule that matches how you want the results presented.

• Competition ranking: Tied values share a rank, and the next rank is skipped.
  
  The sequence 1, 2, 2, 4 comes from RANK.EQ.
• Dense ranking: Tied values share a rank, but the next distinct value gets the next consecutive rank.
  
  The sequence is 1, 2, 2, 3.
• Unique ranking: Every row gets a different position, even when values tie.
  
  A secondary rule, such as original row order, decides which tied row comes first.

These systems answer different questions.

Use competition ranking for many sports standings, dense ranking when you need consecutive levels, and unique ranking when you need a complete sort order.

Calculate dense ranks while keeping ties equal

For numeric values in B2:B6, this formula calculates a dense rank in descending order:

=SUMPRODUCT(($B$2:$B$6>B2)/COUNTIF($B$2:$B$6,$B$2:$B$6))+1

Enter it in C2 and fill down.

For 95, 87, 87, 72, and 60, the expected dense ranks are 1, 2, 2, 3, and 4.

The formula counts the distinct higher values rather than counting every row above the current value.

COUNTIF adjusts for duplicates, so a repeated score does not add another rank level.

This formula is intended for numeric data and works in many desktop Excel versions, including Microsoft 365 and Excel 2019.

For spreadsheets with blanks, text, errors, or very large ranges, test the formula against representative data and consider using a helper column that lists distinct scores.

Assign a unique position to every row

If tied scores need separate positions, use the row’s order as a tie-breaker.

In C2, enter:

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

Fill the formula down.

The first occurrence of a tied value keeps its regular rank; later occurrences add one based on how many times that value has appeared so far.

For scores 95, 87, 87, 72, and 60, the expected positions are 1, 2, 3, 4, and 5.

This method breaks ties by worksheet order, not by another score or name.

To use a meaningful secondary rule, sort the data by that rule before applying the formula, or build a formula that explicitly compares both columns.

If the rows are reordered, the tie-break result may change.

What changes in Google Sheets?

Google Sheets supports RANK.EQ and the same basic syntax, so the standard formula works with a fixed range such as =RANK.EQ(B2,$B$2:$B$6,0).

It also supports COUNTIF and SUMPRODUCT, which are used in the unique and dense ranking examples.

Function names and comma separators may differ in localized spreadsheet settings.

In modern Excel, RANK.EQ is available in Microsoft 365, Excel for the web, and Excel 2010 and later.

Very old workbooks may use RANK.

If sharing with older software, check that recipients’ spreadsheet app recognizes the function name before relying on it.

Common problems and edge cases

• The range shifts when copying: Use absolute references such as $B$2:$B$6 for the full list.
  
  Keep the cell being ranked relative, as in B2.
• The wrong value gets rank 1: Check the third argument.
  
  Use 0 or omit it to rank largest to smallest; use 1 for smallest to largest.
• Blank cells or text are present: RANK.EQ ranks numeric values.
  
  Text and blanks are not ranked as numbers, but errors in the comparison range can disrupt results.
  
  Clean or filter the data, or use a numeric-only helper range.
• Numbers are stored as text: Values that look numeric may not compare as numbers.
  
  Convert them to numeric values before ranking, for example with Excel’s Convert to Number option or a helper formula such as =VALUE(B2).
• There are several tie-break criteria: The simple unique-position formula uses row order.
  
  For reproducible results, define the secondary sort rule clearly, such as score descending and name ascending, then rank the sorted rows.
• The data range grows: Convert the list to an Excel Table and use structured references, or use a sufficiently sized range.
  
  This reduces the chance that newly added rows are omitted from existing ranks.