Rank without ties in Excel: use row order to break a tie

Two people have 90 points each. Excel gives both first place and gives the next person third place. For sporting results, that tie may be exactly what you need. For assigning consecutive positions in a table, you may want separate numbers: 1, 2, 3 and so on.

I will show a simple ranking rule: when scores are equal, the earlier row gets the better position. This is an explicit tie breaker. A formula cannot infer from the scores alone who earned them first or who deserves the higher place.

Decide how a tie should be resolved

A ranking needs a score and an ordering rule. If more points are better, I rank in descending order. For completion time, a smaller number might be better. I establish the direction before writing the formula.

Then I decide what happens when scores are equal. In this guide, the row order from top to bottom decides. An earlier row receives the better position within a tie. This is simple and suitable for creating an ordered list when everyone using it accepts the rule.

If the rules specify earlier submission time, I need a column containing that time. A row’s position does not establish chronology. I could order the entire dataset by time first, but identical timestamps still require another rule, such as a unique identifier. Unique rank numbers alone cannot establish that a decision is fair.

What the ordinary ranking function does

In English Excel, I use RANK.EQ, called POZYCJA.NAJW in Polish. According to the Microsoft documentation, equal values receive the same rank, and the following ranks account for the number of preceding items. Two equal best scores therefore produce 1, 1, 3.

Another function, RANK.AVG, assigns the average of the positions occupied by a tie. For positions 1 and 2, that would be 1.5. The RANK.AVG documentation explains this rule. It also does not assign a separate whole-number position to each person.

A tie is not an Excel error. The function correctly describes the score’s position in the set. An additional rule is needed to produce a list in which no two rows share a rank number.

Five synthetic scores

Put names in column A and points in column B. The headings go in row 1, with data in A2:B6. These names and scores are made up for the example.

Ada and Celina have equal scores. Ada appears earlier, so she receives 1 and Celina receives 2. Ben and Ewa also tie. They follow the two people with 90 points, so they share positions 3 and 4 between them. Dan remains fifth.

The source table does not have to be sorted by descending points. Ben appears above Celina despite having fewer points, yet his rank is still 3. Row order breaks ties only; it does not decide the entire classification.

A formula to copy down

In C2, I enter this formula and copy it through C6:

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

In Polish Excel, the equivalent is:

=POZYCJA.NAJW(B2;$B$2:$B$6;0)+LICZ.JEŻELI($B$2:B2;B2)-1

The English examples use commas and the Polish examples use semicolons. Argument separators depend on regional settings. Microsoft documents these functions for versions including Microsoft 365, Excel 2024 and Excel 2021. This construction does not require a spilled array.

The first part returns the score’s ordinary rank. The second counts occurrences of the current score from the beginning of the data through the current row. Microsoft explains conditional counting in its COUNTIF guide. I subtract one because the first occurrence should retain its ordinary rank.

Why one range stays fixed and another expands

Inside RANK.EQ, the full score range is fixed: $B$2:$B$6. Every copied formula should compare its score against the same five values. If that range moved with the formula, different people would be ranked against different sets.

Inside COUNTIF, the start is fixed while the end expands: $B$2:B2, then $B$2:B3, then $B$2:B4. This growing range assigns successive equal scores adjustments of 0, 1, 2 and so on.

Microsoft’s guide to relative and absolute references explains which addresses change when copied. I inspect the first, middle and final formulas in the formula bar. One missing dollar sign can change the meaning of the ranking.

Work through Celina’s result

Celina has 90 points in B4. The ordinary rank of 90 in the full set is 1. Between B2 and B4, the value 90 occurs twice, for Ada and Celina. The adjustment is 2 - 1 = 1. Adding that to the base rank gives 2.

For Ewa, 80 has an ordinary rank of 3. There are two occurrences of 80 in B2:B6. I add 2 - 1, or 1, and obtain 4. For Ben, the same score appears for the first time, so the adjustment is zero.

This also explains why the adjusted ranks do not collide with the next score group. The two scores of 90 occupy positions 1 and 2, while the next score’s ordinary rank starts at 3. Three equal best scores would occupy three consecutive positions.

What can change the ranking

Reordering equal scores can change their final positions because row order is the secondary criterion. If the result must be independent of worksheet layout, choose a persistent criterion, such as an identifier, and build a solution around that rule.

A filter hiding rows does not turn the formula’s range into a list of visible rows only. This example ranks the full B2:B6 range. If you want ranks within one category or only among active participants, first define that dataset. Hiding the other rows is insufficient.

Adding a person below row 6 also requires updating the end of the range. A formula fixed at $B$6 does not automatically know that another row should join the comparison. I pay particular attention to that after importing a new file.

Numeric data and apparent ties

The example assumes complete numeric scores without empty cells or errors. If an import produces text, I check data types first. I avoid mixing valid scores with “not measured” labels or automatically treating missing measurements as zero.

Precision matters too. Two cells can both display 90 while one stores 89.999 and the other stores 90. Display formatting does not make the underlying values equal. If the rules require ranking rounded values, I document that and prepare a score column specifically for the ranking.

When smaller values should be better, I change the final RANK.EQ argument from 0 to 1. The expanding count of identical values still breaks ties by row order. I make the direction clear so that first place means what the reader expects.

Exercise and handover

In a copy of the data, exchange Ada’s and Celina’s complete rows, then rebuild the formulas for their current positions. Consider who should receive 1 under the rule “the earlier row wins a tie.” Then change Ben’s score to 90 and work through the three occurrences from top to bottom.

In the report, I add a sentence: “Ranked by descending points; ties are resolved by row order.” That is more useful than claiming the formula always chooses fairly. If you need to combine scores with different weights instead of ordering them, see the weighted average guide.