Function Problems

How to Rank Duplicate Values Without Skipping Numbers in Excel

Chanuka Geekiyanage Oct 21, 2026 868 views

Question details

The user needs to assign the same rank to duplicate values (like academic years) while ensuring the subsequent ranks continue consecutively without leaving gaps.

How to Rank Duplicate Values Without Skipping Numbers in Excel

Product: Excel
Device & OS: not provided
Scenario: Assigning sequential ranks to items within a large dataset, where identical values must receive the same rank, and sometimes this ranking needs to be filtered by a secondary criteria such as Student ID.
Observed behavior: Standard ranking formulas like COUNTIFS or RANK count every matching row, which creates gaps in the ranking sequence when duplicates occur (e.g., returning 1, 1, 3 instead of 1, 1, 2).

Before you start

Ensure you are using a modern version of Excel (Microsoft 365, Excel 2021) or WPS Office that supports dynamic array functions such as UNIQUE, SORT, and FILTER.

Solution 1Recommended

Use MATCH, SORT, and UNIQUE Functions (Dense Ranking)

This modern formula combination creates a dense rank by extracting and sorting only the unique values before assigning a rank position.

By wrapping your data range in the UNIQUE and SORT functions, you create an ordered, distinct list internally. The MATCH function then finds the exact position of your target value within this unique list, ensuring ranks are perfectly sequential with no skipped numbers.

Select the target cell

Click on the cell in your ranking column where you want the first rank to appear (for example, cell R2).

Input the MATCH formula

Type the formula =MATCH(Q2, SORT(UNIQUE($Q$2:$Q$20409)), 0) into the formula bar and press Enter.

Apply to the entire column

Drag the fill handle (the small square at the bottom-right corner of the cell) down to apply this formula to the remaining rows in your dataset.

Use MATCH, SORT, and UNIQUE Functions (Dense Ranking)

Adjusting for Descending Order: If you need the highest value to be ranked as 1, adjust the SORT function to sort descending by using =MATCH(Q2, SORT(UNIQUE($Q$2:$Q$20409), 1, -1), 0).

Solution 2

Dense Ranking Based on Multiple Criteria (Filtered by Student ID)

Use the FILTER function alongside MATCH, SORT, and UNIQUE to rank duplicate values separately for each specific category, such as individual student IDs.

Advanced Formula Support

Execute Complex Array Formulas Easily with WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array formulas like UNIQUE, SORT, and FILTER, allowing you to quickly solve complex dense ranking scenarios just as you would in Microsoft Excel.

1. Download and Install: Download WPS Office for free from the official website and open your existing spreadsheet file.
2. Enter the Formula: Click on your target cell and use the same MATCH and UNIQUE combination to rank your data without skipped numbers.
3. Process Large Datasets: Apply the formula across thousands of rows and enjoy fast, reliable calculation speeds.

Full compatibility with Microsoft Excel formulas and .xlsx file formatsBuilt-in dynamic array functions to easily remove duplicates and sort data nativelyFree, lightweight, and user-friendly interface for seamless transitionCross-platform support across Windows, Mac, and mobile devices

microsoft office alternative - wps office

Frequently Asked Questions

Why does the standard RANK function skip numbers for duplicate values?

Standard functions like RANK or RANK.EQ evaluate a number's rank based on the total count of values greater than or less than the target in the list. If two values tie for 1st place, the next sequential value evaluates that there are two numbers greater than it, effectively making it 3rd place and skipping the 2nd rank.

Can I use these formulas in older versions of Excel?

Formulas relying on UNIQUE, SORT, and FILTER require modern dynamic array support available in Microsoft 365, Excel 2021, and modern versions of WPS Office. If you are using an older version, you would need to rely on more complex combinations like SUMPRODUCT and COUNTIF to achieve dense ranking.

How do I rank unique text values instead of numbers?

The same combination of MATCH, SORT, and UNIQUE works perfectly for text values. It will generate an alphabetical list internally and assign a sequential rank based on alphabetical order.

Excel & Data Problems