Dense Rank in Excel: Formulas and Examples

Formulas & functions · Updated November 22, 2026

To calculate a dense rank in Excel, give equal values the same rank and do not leave gaps after ties. With scores in B2:B10, enter this in C2 and fill down: =SUMPRODUCT(($B$2:$B$10>B2)/COUNTIF($B$2:$B$10,$B$2:$B$10))+1. The largest score receives rank 1; tied scores share a rank, and the next distinct score gets the next number.

What dense rank means

A rank tells you where a value sits relative to other values. Dense ranking is a particular way to number those positions: equal values are ties, and the following distinct value advances by one. It is useful when you want a compact sequence of ranks rather than a sequence with missing numbers.

Suppose a list of scores is 100, 90, 90, and 70. Under dense ranking, those values receive ranks 1, 2, 2, and 3. The two 90s occupy the same rank; 70 is the third distinct score. “Dense” describes the sequence of rank numbers, not the spacing between the actual scores.

Dense rank is not a separate built-in Excel function. Excel has functions such as RANK.EQ and RANK.AVG, but they do not directly produce dense ranks for tied values. A formula can count how many distinct values are above the current value and add one, which gives the desired result.

Dense rank compared with other ranking methods

It helps to choose the ranking rule before building a formula, because different rules give different answers after a tie. For the example scores 100, 90, 90, and 70, the common methods are:

  • Dense ranking: 1, 2, 2, 3. The next rank follows the next distinct value.
  • Competition ranking: 1, 2, 2, 4. The tied entries take up two places, so the next rank skips to 4.
  • Sequential ranking: 1, 2, 3, 4. Every row gets its own position, even when values tie; a secondary sort rule is needed to decide which tied row comes first.

Excel’s RANK.EQ function uses competition ranking: tied values get the same rank, and subsequent ranks can have gaps. With scores in B2:B10, this formula in C2 ranks the largest value as 1, but it is not dense ranking: =RANK.EQ(B2,$B$2:$B$10,0). The final argument 0 specifies descending order. Use RANK.EQ when gaps are appropriate, such as reporting a shared second place followed by fourth place. Use a dense-rank formula when you want first, second, and third distinct score levels.

How the dense-rank formula works

In the main formula, the comparison $B$2:$B$10>B2 identifies all the scores greater than the current row’s score. COUNTIF counts how often each value appears in the full range. Dividing the comparison results by those counts means duplicate higher scores contribute a combined total of one, rather than being counted repeatedly. SUMPRODUCT adds those contributions, and adding 1 converts the count of higher distinct values into the current value’s rank.

For example, if the current score is 70 and the range contains 100, 90, 90, and 70, the two 90s together count as one distinct higher score. There are two distinct values above 70: 100 and 90. The formula therefore returns 2 + 1, or rank 3. If the current score is 90, only 100 is higher, so the result is rank 2. Both rows with 90 return that same result.

The dollar signs lock the comparison range so it remains B2:B10 as you copy the formula down. B2 has no dollar signs, so it changes to B3, B4, and so on. This combination of absolute and relative references is important: if the range shifts while filling the formula, rows may be ranked against different sets of scores.

Set up and fill the formula

For a straightforward worksheet, place a heading such as Score in B1 and Dense rank in C1, then put numeric scores in B2:B10. Select C2, enter the formula from the opening paragraph, and press Enter. Check the result against a few known values before copying it to the rest of the rows.

  1. Keep the source values in one column and leave the rank formula in a separate column. This prevents the formula from replacing its own input.
  2. Use the same fixed source range in every formula. If the list will grow, consider converting it to an Excel table or updating the range when new rows are added.
  3. Fill the formula down with the fill handle, or select the cells to fill and use Ctrl+D on Windows. On a Mac, use Command+D for Fill Down.
  4. Sort the results if you want to view the highest values together. Ranking assigns a number; it does not rearrange the rows.

If the formula is in a table, Excel may automatically copy it to the other rows in that calculated column. Confirm that its reference range covers all records, including any new rows. For a quick manual check, sort a copy of the data by score from largest to smallest and confirm that equal scores have the same dense rank.

Rank in ascending order or use a dynamic-array formula

The main formula ranks larger numbers first. To rank smaller numbers first, count distinct values below the current value instead. With values in B2:B10 and the ascending rank formula in C2, use: =SUMPRODUCT(($B$2:$B$10<B2)/COUNTIF($B$2:$B$10,$B$2:$B$10))+1. The lowest distinct value then receives rank 1. For example, with values 5, 8, 8, and 12, ascending dense ranks are 1, 2, 2, and 3.

In Microsoft 365 or Excel 2021 and later, a dynamic-array approach can make the idea easier to see. With numeric scores and no blank cells in B2:B10, enter this in C2 and fill down: =XMATCH(B2,SORT(UNIQUE($B$2:$B$10),1,-1)). UNIQUE creates the list of distinct scores, SORT orders that list from largest to smallest, and XMATCH returns the position of the current score in that ordered list. The result is its dense rank. For ascending ranks, change the final SORT argument from -1 to 1.

This approach depends on newer functions. If a workbook must be used in an older Excel version, the SUMPRODUCT and COUNTIF formula is generally the more compatible option. A dynamic formula can also be helpful when you want to inspect or reuse the distinct sorted list, not just return ranks.

Blanks, duplicate values, and common issues

For the core formula, the source range should contain numeric values to rank. If cells contain text labels, dates stored as text, errors, or formulas returning empty strings, results may not match your expectations. Clean or standardize the source data first, and make sure values intended to tie are truly equal as Excel stores them.

Blank rows are a frequent source of confusion. If B2 is blank, the formula will try to rank a blank rather than an actual score. You can suppress the result for a blank current row. With scores in B2:B10 and the guarded formula in C2, use: =IF(B2="","",SUMPRODUCT(($B$2:$B$10>B2)/COUNTIF($B$2:$B$10,$B$2:$B$10))+1). This leaves the rank cell empty when its corresponding input is empty. It does not remove blank cells from the range for every possible data-cleaning purpose, so a range with mixed data still deserves review.

Values that look identical may not be equal. For instance, 10 and 10.0001 can display as 10 if the cells are formatted with no decimal places, but Excel ranks the underlying values separately. If the business rule is to rank rounded scores, create a separate rounded-value column and rank that column. Decide the rounding precision deliberately; changing cell display formatting alone does not change the values used by the ranking formula.

Check these issues if you see unexpected results:

  • Gaps in the ranks: You may be using RANK.EQ, which uses competition ranking, rather than the dense-rank formula.
  • Repeated values ranked differently: Verify that the values are exactly equal, and check for hidden precision or inconsistent text and numbers.
  • Formula errors: Confirm that the source range and current-row reference are correct, and that the range does not include unexpected error values.
  • New records are missing: Extend the fixed range or use a properly configured table-based range so the added rows are included.
  • Unexpected ranks for blanks: Use the blank guard where needed and keep the ranked data consistently numeric.

When dense ranking is useful

Dense rank is useful when the question is “Which distinct level is this value?” rather than “What place does this individual row occupy?” It can label score bands by order, rank product prices by distinct price, or show the order of unique sales totals while preserving ties. A rank of 3 means the value is the third distinct value in the chosen direction, not necessarily the third row after sorting.

That distinction matters in reports. If five people share the highest score, dense ranking assigns all five rank 1 and the next score rank 2. Competition ranking assigns that next score rank 6. Neither convention is inherently more correct; choose the one that matches the reporting rule and state it clearly for readers. When unique row positions are required, add a tie-breaker such as date or name and use a separate sorting or ranking method rather than treating dense ranks as unique identifiers.

Frequently Asked Questions

Does Excel have a DENSE.RANK function?
No. Excel does not provide a built-in DENSE.RANK function like some other spreadsheet or database tools. Use a formula based on SUMPRODUCT and COUNTIF, or use UNIQUE, SORT, and XMATCH in supported Excel versions.

Why does RANK.EQ skip numbers?
RANK.EQ gives tied values the same rank and follows competition ranking. If two items tie for second, the next item is fourth. Dense ranking instead gives the next distinct value rank 3.

Can dense rank work when the values are text?
The formulas here are intended for numeric values. Ranking text requires defining a specific sort order and handling blanks or inconsistent text explicitly; do not assume a numeric dense-rank formula will give a meaningful business ranking for labels.

Does sorting the data change a dense rank?
Sorting does not change the rank rule. The formula calculates each row’s position among the values in its source range, while sorting only changes row order. Make sure the formula still references the complete intended range after edits.

How do I make the highest value rank 1?
Use the descending version, which counts distinct values greater than the current value. That is the formula in the opening paragraph. For lowest-value-first ranking, use the ascending version with the less-than comparison.

Related

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.