Excel Rank Formula Without Duplicate Ranks: Examples

To give tied values different, consecutive ranks in Excel, enter =RANK.EQ(B2,$B$2:$B$10,0)+COUNTIF($B$2:B2,B2)-1 beside the first value and fill down. This ranks the largest value first and breaks ties by the order of the rows; if you instead want a ranking of distinct values only, use a separate list of unique values.

What “rank without duplicates” can mean

The phrase can describe two different results. In a regular ranking, tied values share a rank. For example, scores of 92, 85, 85, and 78 receive ranks 1, 2, 2, and 4 with Excel’s competition ranking. The next rank is 4 because two entries occupy second place.

Some users want every row to have its own rank, even when scores are tied. In that case, the same scores could receive 1, 2, 3, and 4. The formula in the opening paragraph does this by adding a count of the current value’s occurrences up to the current row. The first 85 gets rank 2; the second 85 gets rank 3.

A third goal is to rank each different score once, without listing or ranking duplicate records. That is a distinct-values problem, not a tie-breaking problem. Choose the approach that matches the output you need before copying a formula down a column.

How the no-duplicate-rank formula works

Assume the scores are in B2:B10, with the first score in B2 and the ranking formula entered in C2. The dollar signs keep the complete comparison range fixed as you fill the formula down. In $B$2:B2, only the first cell is fixed; the bottom of the range expands to the current row.

RANK.EQ(B2,$B$2:$B$10,0) finds the score’s ordinary descending rank. The final argument, 0, means largest first. COUNTIF($B$2:B2,B2) counts how many times that score has appeared from the start of the list through the current row. Subtracting 1 leaves the first occurrence unchanged and adds 1 to each later occurrence.

For a concrete example, suppose B2:B7 contains 92, 85, 85, 78, 70, and 70. The formula returns 1, 2, 3, 4, 5, and 6. The first 70 has ordinary rank 5 and one occurrence so far, so its final rank stays 5. The second 70 has ordinary rank 5 and two occurrences so far, so its final rank becomes 6.

To apply it in Excel for Microsoft 365 on Windows:

Put the numeric values in one column, with a heading above them if useful.
Enter the formula in the adjacent column on the same row as the first value.
Press Enter, then use the fill handle to copy the formula down through the last data row.
Check a repeated value: the later occurrence should have a rank one greater than the earlier occurrence, unless other values between them change its ordinary rank.

The formula uses RANK.EQ and COUNTIF, which are available in current desktop Excel versions. Formula argument separators can depend on regional settings; this example uses commas, as in standard English-language Excel installations.

Rank distinct values only

If you want a compact leaderboard with each score shown once, generate a unique, sorted list first. For Excel 2021 or Microsoft 365, assume the source scores are in B2:B10 and the distinct list should begin in D2. Enter this formula in D2:

=SORT(UNIQUE(FILTER(B2:B10,B2:B10<>"")),1,-1)

The result spills downward, sorts from largest to smallest, and excludes blank cells. For the example values 92, 85, 85, 78, 70, and 70, it returns 92, 85, 78, and 70. The spill range can expand or contract as the source values change. Keep the cells below D2 clear so Excel has room to display the results.

To give that distinct list consecutive ranks, assume the formula is in D2 and its results spill from there. Enter this in E2:

=SEQUENCE(ROWS(D2#))

It returns 1, 2, 3, and 4 next to the four unique scores. The # after D2 refers to the entire spill range. This method ranks distinct values by their position in the sorted list; it does not assign a separate rank to every original row. UNIQUE, SORT, FILTER, and SEQUENCE require Excel 2021 or Microsoft 365. They are not available in some older perpetual editions.

Choose a tie-breaking order

The COUNTIF method breaks ties according to the order of the source rows. This is predictable, but row order may not be a meaningful way to decide who ranks higher. If a leaderboard needs a deliberate tie-break, arrange the rows by the secondary rule first, then calculate the ranks. For example, sort by score from largest to smallest and, within equal scores, by the chosen secondary measure.

In Excel for Microsoft 365 on Windows, select the data and use Data > Sort to set multiple sort levels. If the rows should remain in their original order, add a separate helper column containing the tie-break order and use a formula designed around both criteria; do not assume the basic COUNTIF formula evaluates another column. Sorting changes row order, so consider adding an original-order column if you may need to restore it.

The formula’s row-based tie-break is not random. It simply awards the earlier row the earlier rank. If the source list is refreshed or sorted differently, tied records can swap ranks. That is usually acceptable for a worksheet display, but it can be unsuitable when ranks need to stay attached to records regardless of their order.

Ascending rankings and blank cells

For a ranking where the smallest value should be first, change the order argument in RANK.EQ from 0 to 1. Assume values are in B2:B10 and the formula starts in C2:

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

This still breaks ties by row order, but the lowest value receives rank 1. For example, the values 4, 7, 7, and 12 receive ranks 1, 2, 3, and 4 when ranked ascending.

If the source range contains blank rows and you want the output to remain blank on those rows, assume the values are in B2:B10 and enter this variation in C2:

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

The IF test prevents a blank row from displaying a rank. It also works when a formula in B returns an empty string. The ranking range can still include blank cells; RANK.EQ ranks numeric values in its reference range and ignores text and empty cells there. If your data contains numeric values stored as text, convert them to numbers before relying on ranking results.

Common mistakes and checks

Forgetting the expanding COUNTIF range: If both ends are written as $B$2:$B$10, every duplicate gets the same occurrence count. Use $B$2:B2 in the first formula, then fill down.
Leaving the rank range relative: Without dollar signs around B2:B10, the comparison range shifts as the formula is copied. Lock it as $B$2:$B$10.
Expecting competition ranking: The tie-breaking formula intentionally produces consecutive ranks. Use =RANK.EQ(B2,$B$2:$B$10,0) when tied values should share a rank and subsequent ranks should have gaps.
Including headings or unrelated numbers: The rank range should contain the values being compared, not a heading or totals that should not compete.
Assuming ranks identify unique records: A rank is a calculated position, not a permanent identifier. Ties can change when values or row order change.

After filling down, check the smallest and largest values, at least one duplicate, and any blank rows. Also confirm whether the results should change when the source list is sorted. These checks catch most reference and tie-break misunderstandings before the ranks are used in a chart or report.

Which method should you use?

Use the COUNTIF tie-break formula when every source row needs a distinct, consecutive position and ties can be decided by row order. Use RANK.EQ alone when equal values should share a rank. Use UNIQUE with SORT when the output should contain one row per different value, then use SEQUENCE if those distinct values need simple consecutive positions.

These methods answer different questions, so their numbers will not always match. With 92, 85, 85, 78, 70, and 70, RANK.EQ alone returns 1, 2, 2, 4, 5, and 5. The no-duplicate-rank formula returns 1 through 6. A sorted unique list contains four values, with positions 1 through 4. None is universally correct; choose based on whether your report represents records, shared standings, or distinct scores.

Frequently Asked Questions

How do I rank numbers in Excel without ties?

Use RANK.EQ plus COUNTIF over an expanding range: =RANK.EQ(B2,$B$2:$B$10,0)+COUNTIF($B$2:B2,B2)-1. It gives each repeated value a different rank based on its row order.

How do I make duplicate values have the same rank?

Use =RANK.EQ(B2,$B$2:$B$10,0) with values in B2:B10. Equal values receive equal ranks, and the next rank skips positions occupied by the tie.

How can I rank unique values only?

In Excel 2021 or Microsoft 365, use =SORT(UNIQUE(FILTER(B2:B10,B2:B10<>"")),1,-1) to create a descending list without duplicates or blanks. Add a SEQUENCE formula beside the spill range for consecutive positions.

Why does my formula give repeated ranks?

If you used RANK.EQ by itself, repeated ranks are the expected result for tied values. To separate those ties, add the COUNTIF occurrence adjustment and make sure its range starts at the first data row and ends at the current row.

Can I rank the lowest number as first?

Yes. Set RANK.EQ’s final argument to 1 instead of 0. The formula then ranks in ascending order, while the COUNTIF part still breaks ties in row order.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.