Yes. To give every numeric value its own rank, including tied values, add a tie-break count to Excel’s ranking function. With scores in A2:A10 and the first result in B2, enter =RANK.EQ(A2,$A$2:$A$10,0)+COUNTIF($A$2:A2,A2)-1 in B2 and fill it down. The 0 ranks the largest value first; repeated scores receive consecutive ranks according to their order in the rows.
The Formula and What It Returns
Suppose A2:A6 contains 95, 95, 90, 80, and 80. The formula returns 1, 2, 3, 4, and 5. Without the COUNTIF adjustment, RANK.EQ would return 1, 1, 3, 4, and 4: tied values share a rank, and the next rank is skipped. The adjusted formula turns each tied occurrence into a separate rank.
In this example, the first 95 is ranked first and the second 95 second because the first occurrence appears earlier in the list. The two 80s receive ranks four and five for the same reason. The formula does not change the scores or remove records; it calculates a ranking beside each value.
- Use 0 as the final RANK.EQ argument to rank from largest to smallest.
- Use 1 to rank from smallest to largest.
- Keep the dollar signs around the full data range so it stays fixed when you fill the formula down.
- Leave the first endpoint of the COUNTIF range fixed and let the second endpoint move with each row.
Why Excel Normally Repeats Ranks
Excel’s RANK.EQ function gives equal numbers the same rank. That is often the expected result in a competition table: if two athletes tie for first, both are first, and the next athlete is third. This is sometimes called competition ranking. It reflects the fact that two records occupy the first position, so the next position is number three.
A no-duplicate rank is a different rule. It assigns a unique position to every row, even when two rows have the same value. The formula above preserves the ordinary ranking order between different values, then uses row order to settle ties. It creates consecutive positions such as 1, 2, 3, 4 rather than skipping a position after a tie.
Be clear about which meaning you need before choosing a formula. A leaderboard may need shared ranks for ties, while a numbered list may require every row to have its own position. A third possibility is ranking only distinct values, where equal values are treated as one value rather than ranked as separate records.
How the Tie-Break Formula Works
RANK.EQ(A2,$A$2:$A$10,0) calculates the usual descending rank for the value in A2. COUNTIF($A$2:A2,A2) counts how many times that value has appeared from the first data row through the current row. Subtracting one means the first occurrence gets no extra rank, the second occurrence gets one extra rank, and the third gets two.
For example, if 95 appears in A2 and A3, the usual rank of each is 1. In row 2, the running count is 1, so the adjustment is zero and the result remains 1. In row 3, the count is 2, so one is added and the result becomes 2. A value of 90 has a normal rank of 3, and if it appears once its result stays 3.
The method works even if the values are not sorted. Excel calculates each value’s rank against the entire fixed range; the row position is used only to decide which tied occurrence comes first. If the data range includes values that should not participate in the ranking, adjust the range to include only the intended records.
Choose the Tie-Break Rule You Want
The basic formula uses worksheet order as its tie-break. That is simple, but it may not represent a meaningful business rule. If two salespeople have the same sales total, for example, you might want the person with more transactions or an earlier completion date to rank ahead. A row-based tie-break gives the earlier row the better rank regardless of those details.
For predictable results, decide what should happen when values tie. Common choices include:
- Worksheet order: the first tied value in the current range gets the higher position. Use the formula in the opening example.
- A second measure: compare another column, such as units sold, after comparing the main score. You can sort by both columns or build a formula that explicitly evaluates the secondary measure.
- A fixed identifier: use a stable employee ID, product code, or other unique field to decide the order. This avoids changing the tie-break merely because someone rearranged rows.
Whatever rule you choose, document it near the results if other people will use the workbook. A unique rank implies that ties have been resolved somehow; readers should be able to tell whether the resolution came from row order, a secondary score, or another rule.
Ascending Ranks and Blank Cells
For ascending ranks, where the smallest value should be rank 1, change the final RANK.EQ argument from 0 to 1. With values in A2:A10 and the rank in B2, use =RANK.EQ(A2,$A$2:$A$10,1)+COUNTIF($A$2:A2,A2)-1 and fill down. For values 10, 10, and 15, the results are 1, 2, and 3.
Blank cells deserve attention. A blank referenced as the number being ranked can be treated like zero by ranking calculations, which may produce an unintended position. If empty rows are possible, with values in A2:A10 and ranks in B2, use =IF(A2="","",RANK.EQ(A2,$A$2:$A$10,0)+COUNTIF($A$2:A2,A2)-1). This leaves the corresponding result blank when A2 is empty; fill the formula down the rank column. The formula assumes the input cells are numeric or blank. Text labels in the score range should be cleaned up or excluded rather than treated as scores.
Alternatives for Distinct Values and Sorted Lists
If by no duplicates you mean you want a list of distinct scores, not a separate rank for every record, a ranking formula is not the first step. In Microsoft 365 or Excel 2021, with source values in A2:A10, =SORT(UNIQUE(A2:A10),1,-1) returns each distinct value once, sorted largest to smallest. That result is a list of values, not ranks attached to the original rows. Excel 2019 and earlier do not include the UNIQUE and SORT worksheet functions.
You can also sort the original table to make the order easy to inspect. Select the data and use the Data tab’s Sort command, sorting the score column from largest to smallest. If the ranking formula uses row order to break ties, sorting changes which tied record receives the earlier rank. That is not an error: the tie-break rule is based on current row order. If the order must remain stable, use a fixed secondary criterion instead.
Do not use Remove Duplicates just to create unique ranks. In Excel, Data > Remove Duplicates deletes duplicate records from the selected data; it does not assign distinct ranks while retaining every row. It can be appropriate when you intentionally want to remove repeated records, but make a copy first if the source data must be preserved.
Common Problems to Check
- Ranks unexpectedly repeat: check that the COUNTIF part is present and that its range starts at the first data row and ends at the current row. A fixed range such as $A$2:$A$10 counts all matching values and does not provide a running tie-break.
- Ranks skip numbers: confirm that you are using the tie-adjusted formula rather than RANK.EQ alone. Skipped positions are normal for shared competition ranks.
- Every result is wrong after filling down: check that the overall ranking range is absolute, as in $A$2:$A$10. Without dollar signs, the comparison range shifts as the formula is copied.
- A blank row gets a rank: use the IF version or remove blank rows from the ranked range.
- Two records swap positions: if their scores are tied, the formula intentionally uses row order. Sorting or inserting rows can change their relative ranks.
RANK.EQ and COUNTIF are available in current desktop Excel versions, including Microsoft 365. The formulas work in Excel for Windows and Mac; the formula syntax shown uses commas as argument separators. Depending on regional settings, Excel may display or require semicolons instead. The calculation is the same, but use the separator your installation expects.
Frequently Asked Questions
How do I rank numbers in Excel without ties?
With values in A2:A10, put =RANK.EQ(A2,$A$2:$A$10,0)+COUNTIF($A$2:A2,A2)-1 in B2 and fill down. It assigns each row a unique descending rank, using row order to resolve equal values.
Does RANK.EQ give duplicate ranks?
Yes. By design, RANK.EQ gives equal values the same rank. Add a running COUNTIF adjustment when you want each occurrence to have its own consecutive position.
How do I rank from lowest to highest?
Set RANK.EQ’s third argument to 1. Add the same COUNTIF tie-break adjustment if tied values must receive separate ranks.
Can I rank duplicates by a second column?
Yes, but the basic formula breaks ties by row order, not by another field. Sort by the main score and then the secondary column, or use a formula designed around both criteria so the intended tie-break is explicit.
Does Remove Duplicates help make ranks unique?
No. It removes duplicate records from the data. Use a ranking formula when you need to keep every record and give each one a distinct position.
Related
- Excel Current Year: How to Get It with NOW and TODAY
- Excel NOW Format: Show the Current Date and Time Correctly
- Excel NOW Date: How the Current Date and Time Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- INDIRECT Excel Function: Examples, Uses, and LimitsIn Excel, INDIRECT converts text into a live cell or range reference; if A2 contains B5, =INDIRECT(A2) returns…
- Excel Formula for First Day of Month: ExamplesIf the date you want to use is in cell A2, enter =DATE(YEAR(A2),MONTH(A2),1) to return the first day…
- Excel Not Equal Symbol: How to Use <> in FormulasIn Microsoft Excel, the not-equal operator is <>—a less-than sign followed by a greater-than sign. Use it in…
- Excel LAMBDA Function: Create and Reuse Custom FormulasTo use Excel’s LAMBDA function, define a calculation with named inputs, save it as a workbook name, and…
- Excel Date Format Formulas: TEXT, DATEVALUE, and MoreTo display a date as text with an Excel formula, use TEXT; with a date in A2, =TEXT(A2,"mmmm…
- Excel LAMBDA Formula Examples for Reusable FunctionsExcel LAMBDA lets you turn a calculation into a reusable function, so you can name it once and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.