Excel Rank Ties Without Skipping Numbers

How-to basics · Updated November 19, 2026

To make tied values share a rank without leaving gaps, use dense ranking rather than Excel’s standard RANK.EQ result. With scores in B2:B10 and the value to rank in B2, enter this in C2 and fill down: =SUMPRODUCT(($B$2:$B$10>B2)/COUNTIF($B$2:$B$10,$B$2:$B$10))+1. It counts the distinct higher scores, then adds one.

What it means to rank ties without gaps

Excel’s usual ranking functions use competition ranking: tied values receive the same rank, and the next rank reflects how many values came before the tie. For scores of 98, 92, 92, 85, and 73, that produces ranks 1, 2, 2, 4, and 5. The missing 3 is intentional: two entries occupy positions at rank 2.

Dense ranking uses a different convention. It gives the same example ranks of 1, 2, 2, 3, and 4. Each distinct score gets the next consecutive rank, regardless of how many times a higher score occurs. This is often what people mean by “rank ties without skipping a number.”

Neither convention is universally correct. Competition ranking is common in sports tables and contests, where a tie means competitors share a place and later places account for the number of competitors ahead. Dense ranking is useful when you want a consecutive list of score levels, such as first-highest score, second-highest distinct score, and third-highest distinct score.

Use a dense-rank formula in Excel

The formula in the opening paragraph works by examining every score greater than the current score. Each higher score contributes one, divided by the number of times that score appears. If 92 appears twice, its two appearances contribute one-half each, for a total contribution of one distinct score. Adding 1 gives the current score’s dense rank.

For example, if the current score is 85, the scores above it are 98, 92, and 92. The formula counts 98 once and the two copies of 92 as one distinct value, so the result is rank 3. The comparison uses greater-than, which makes the highest score rank 1.

To use it:

  1. Put the numeric values to rank in one continuous range, such as B2:B10.
  2. Enter the dense-rank formula in the adjacent column, beginning on the same row as the first value.
  3. Fill the formula down with the fill handle, or copy and paste it into the remaining rank cells.

The dollar signs keep the full comparison range fixed as the formula is copied. The reference to B2 has no dollar signs, so it changes to B3, B4, and so on. That relative reference is what lets each row rank its own value against the same complete list.

Worked example: compare the two ranking systems

Suppose B2:B6 contains 98, 92, 92, 85, and 73. Here is how the results differ:

  • 98 receives dense rank 1 and competition rank 1.
  • Both 92s receive dense rank 2 and competition rank 2.
  • 85 receives dense rank 3, but competition rank 4.
  • 73 receives dense rank 4, but competition rank 5.

The distinction is the number of unique values above each score. There are two distinct scores above 85—98 and 92—so its dense rank is 3. There are three rows above it, so competition ranking assigns it rank 4. The duplicate 92 is counted once in dense ranking but twice in competition ranking.

This distinction also matters when reporting a cutoff. If the top three dense ranks are requested, the result includes the three highest distinct score levels, even if several people share one of those levels. If the requirement is the top three people or entries, a dense-rank cutoff may include more than three rows because of ties. Clarify whether the request concerns score levels or individual positions before choosing a method.

Rank in ascending order or handle blanks

The main formula assumes higher numbers should rank better, as with test scores or sales. For values where the lowest number should rank first, reverse the comparison. With values in B2:B10 and the value being ranked in B2, use this in C2 and fill down: =SUMPRODUCT(($B$2:$B$10<B2)/COUNTIF($B$2:$B$10,$B$2:$B$10))+1. This counts the distinct values below the current value, so the smallest value receives rank 1.

If the range includes unused rows, decide how those rows should appear. A blank current cell should usually produce a blank rank rather than a number. One way to do that is to add an IF check. With scores in B2:B10 and the output for B2 in C2, use: =IF(B2="","",SUMPRODUCT(($B$2:$B$10>B2)/COUNTIF($B$2:$B$10,$B$2:$B$10))+1). Copy it down as usual. The test leaves the output empty when the current score cell is blank; it does not validate the other cells in the range.

For the clearest results, rank a range containing numeric values of the same kind. Mixed text, numbers stored as text, error values, and formulas returning empty text can complicate comparisons. If imported scores behave unexpectedly, check whether they are genuinely numeric: select a cell and inspect its value or use Excel’s number-format and data-cleaning tools before relying on the ranking output.

How RANK.EQ and RANK.AVG compare

RANK.EQ is Excel’s standard function for competition ranking. It assigns equal ranks to ties and skips later rank numbers. With numbers in B2:B10, the value in B2, and a descending ranking in C2, enter =RANK.EQ(B2,$B$2:$B$10,0). The final argument, 0, means the largest number ranks first. If that argument is omitted or set to 0, Excel ranks in descending order; use 1 for ascending order.

RANK.AVG also skips to the next occupied position, but gives tied values the average of the positions they occupy. In the example where two values tie for second and third place, each receives 2.5. With numbers in B2:B10, the value in B2, and the output in C2, enter =RANK.AVG(B2,$B$2:$B$10,0). This can be useful for statistical summaries, but it does not create consecutive integer ranks.

Excel does not provide a separate built-in dense-rank function among these ranking functions. The SUMPRODUCT and COUNTIF approach supplies that behavior for numeric data. Choose the function or formula based on the ranking rule rather than trying to remove gaps from a standard rank result afterward.

Decide whether tied entries need separate positions

Dense ranking keeps tied values equal. If two employees have the same sales total, both receive the same dense rank; the next distinct sales total gets the next rank number. That is different from breaking the tie so every row gets a unique position.

If each entry must have a unique order, choose and document a tie-breaker, such as an earlier completion time or a second score. You can also sort by the primary value and then by a secondary column using Data > Sort. Sorting changes the displayed row order, though; it does not by itself create a dense-rank formula. A tie-break rule should reflect the actual reporting requirement, not an arbitrary row order.

Conditional formatting is another separate feature: it can highlight repeated values or rank categories, but it does not change the rank calculation. For example, highlight ties if readers need to see which entries share a rank, while keeping the rank column’s values determined by the chosen formula.

Common problems and quick checks

  • The ranks skip numbers. Check whether the column uses RANK.EQ or RANK.AVG. Those functions use competition-style ranking, not dense ranking.
  • Every row shows the same result. Check that the current-cell reference is relative (B2, not $B$2) and that the comparison range is fixed with dollar signs.
  • The output is unexpected for a tie. Verify that the values are exactly equal. Values that look the same may differ because of extra decimal places or inconsistent imported data.
  • Some ranks are wrong after adding data. Expand the range in the formula to include the new rows. A formula comparing B2:B10 will not account for values entered in B11.
  • The formula returns an error. Look for errors in the ranked range and confirm that the data is numeric. Also check that the formula was copied without changing the fixed range.

When values are displayed with rounded formatting, two cells can appear identical but still have different stored values. Increase the displayed decimal places temporarily to inspect them. If the business rule says values should tie at a set precision, create or use a consistently rounded helper column for ranking, rather than assuming the display format changes the underlying numbers.

Frequently Asked Questions

Does RANK.EQ skip a number when values tie?
Yes. It gives tied values the same rank and skips the positions occupied by the tie. For example, two values tied at rank 2 are followed by rank 4. Use a dense-rank formula when the following distinct value should receive rank 3.

What is dense ranking in Excel?
Dense ranking assigns one rank to each distinct value, with ties sharing that rank and no gaps between distinct ranks. Excel does not have a separate function named DENSE.RANK; a SUMPRODUCT and COUNTIF formula can calculate it for numeric data.

Can I rank the lowest value as 1 without gaps?
Yes. Use the ascending version of the dense-rank formula, which counts distinct values lower than the current value. The smallest value then receives rank 1, and ties still share ranks.

Why does my dense-rank formula give a different result for numbers that look tied?
The stored values may differ beyond the displayed decimal places, or one value may be text rather than a number. Inspect the underlying precision and data type, then standardize the values if the ranking rule treats them as equal.

Can tied rows have different ranks?
Only if you add a tie-breaker. Dense ranking deliberately gives equal values equal ranks. If every row needs a unique position, define a second criterion—such as time or another score—and order the results by both criteria.

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.