How to Rank in Excel When There Is a Tie: Two Methods

How-to basics · Updated November 20, 2026

To give equal values the same rank in Excel, use RANK.EQ; to give every row a different rank, add a tie-breaker such as the order the values appear in. The right choice depends on whether tied results should share a place or be separated. The examples below use Microsoft Excel for Microsoft 365 on Windows.

What You’ll Need

  • An Excel worksheet with one numeric value per item, such as a score, sales total, or time.
  • A clear choice about how to handle ties: share a rank, show an average rank, or assign each row a unique rank.
  • Excel for Microsoft 365, Excel 2021, or another Excel version that supports RANK.EQ. The older RANK function is also available in many versions, but RANK.EQ makes the tie behavior explicit.

Step-by-Step

  1. Set up a simple list. Put each item in one column and its value in the next. For example, place names in A2:A7 and scores in B2:B7. Use these example scores, in order: 95, 88, 88, 76, 70, and 70. Keeping one row per item makes it straightforward to compare each result with its score.
  2. Decide what rank should mean. In a high-score-wins list, the largest value is rank 1. In a race where the lowest time wins, the smallest value is rank 1. Also decide whether the two scores of 88 should both be second, should receive an average of the occupied positions, or should be separated using another rule. Do this before choosing the formula; there is no single tie rule that fits every list.
  3. Choose a cell for the first result. With names in A2:A7 and scores in B2:B7, use C2 for the rank. Add a heading such as Rank in C1 so the results are easy to identify. Leave the original score column unchanged; the rank formula refers to it.
  4. Use RANK.EQ to give equal values the same rank. With the score for the current row in B2 and all scores in B2:B7, enter this in C2 for a highest-score-first ranking: =RANK.EQ(B2,$B$2:$B$7,0). The final 0 means rank from largest to smallest. Copy the formula down through C7. The results are 1, 2, 2, 4, 5, and 5. The tied 88s share rank 2, and the tied 70s share rank 5. Since each tie occupies more than one position, the next rank is skipped.
  5. Fill the formula down correctly. Select C2, then use the fill handle at the bottom-right corner of the cell to drag down to C7. You can also select C2:C7 and press Ctrl+D on Windows. In Excel for Mac, use Control+D for Fill Down. The dollar signs in $B$2:$B$7 lock the comparison range; without them, the range could shift as you fill down. The reference to B2 is intentionally relative so it changes to B3, B4, and so on.
  6. Use RANK.AVG if tied rows should show an average position. With the same layout—scores in B2:B7 and the first result in C2—enter =RANK.AVG(B2,$B$2:$B$7,0) and fill down. This returns 1, 2.5, 2.5, 4, 5.5, and 5.5. The two 88s occupy positions 2 and 3, so their average position is 2.5; the two 70s occupy positions 5 and 6, giving 5.5. Use this only when fractional ranks make sense for your report.
  7. Give every row a unique rank if required. If ties should be separated by their order in the worksheet, use this formula in C2 with scores in B2:B7: =RANK.EQ(B2,$B$2:$B$7,0)+COUNTIF($B$2:B2,B2)-1. Fill it through C7. The example returns 1, 2, 3, 4, 5, and 6. The first 88 stays ahead of the second 88 because it appears earlier in the list; the same applies to the 70s. This makes distinct ranks, but it does not mean the first tied item performed better. It is only a practical tie-break rule when row order is acceptable.
  8. Change the direction for lowest-value-first rankings. With scores in B2:B7 and the first result in C2, use =RANK.EQ(B2,$B$2:$B$7,1) when the smallest number should be rank 1. For the example values, the results are 6, 4, 4, 3, 1, and 1. The 70s are best and share first place. The argument is 1 for ascending order and 0 for descending order; leaving it out defaults to descending order.
  9. Check the results against the values. Confirm that the best value has the expected rank, each tie follows your chosen policy, and the result range includes every intended score. With RANK.EQ, repeated values create gaps in later ranks by design. With the unique-rank formula, ranks are consecutive, but ties are decided by their order in the source list.

Common Mistakes

Using the wrong ranking direction: Leaving the order argument blank ranks the largest value as 1. That is right for points or sales, but wrong for elapsed times or costs when smaller is better. Include 1 explicitly for lowest-first ranking so the rule is visible to anyone checking the formula.

Expecting RANK.EQ to make every rank unique: Its purpose is to give identical values an equal rank. If two rows are tied for second, the next rank is fourth, not third. That skipped number is not a formula error. Use the COUNTIF tie-break formula only if your ranking rules call for unique positions.

Letting the reference range move: If you write a comparison range without dollar signs and fill the formula down, it can shift from B2:B7 to B3:B8. That may exclude the first score and include an unrelated cell. Lock the full comparison range, while leaving the current-row reference unlocked.

Ranking text that looks numeric: Values imported from a file may be stored as text. They can look like numbers but fail to rank as expected or be ignored in calculations. Check whether the values are recognized as numbers, then convert them if needed using Excel’s number-conversion options or by re-entering the values in numeric form.

Including headers or unrelated cells: The range should contain only the values to compare. Including a heading, a subtotal, or a previous rank can distort the result. For a list that grows often, consider converting the data to an Excel table and checking that the formula refers to the intended score column.

Tips

Write the tie policy beside the results or in a note on the worksheet. A reader may otherwise assume that a rank of 2 means there is only one second-place item. A short label such as “equal values share rank” or “ties broken by row order” makes the meaning clear, especially when a report is printed or shared.

If you need a meaningful unique order, choose a real secondary criterion rather than relying on row position. For example, a contest might break equal scores by a separate accuracy measure, or a sales report might break equal totals by customer name. Sort using the agreed criteria or build a ranking process that explicitly applies both fields. The COUNTIF formula above is deliberately simpler: it only uses the first occurrence in the list as the tie-breaker.

Keep the source values and rank results in separate columns. This makes it easier to find accidental edits, verify calculations, and sort the table without overwriting the numbers being ranked. If you sort the rows, remember that the unique-rank formula uses the order of the source list as its tie-breaker, so the unique positions can change when tied rows move.

For a quick visual check, select the rank cells and use Home > Conditional Formatting to highlight duplicates or unusual values. Formatting does not change the ranking calculation, but it can help you spot shared ranks. Do not treat color as the tie rule itself; the formula and the stated policy determine what the results mean.

Troubleshooting

The formula displays an error: Check that the function name is spelled RANK.EQ or RANK.AVG, that each opening parenthesis has a closing one, and that the formula begins with an equals sign. If Excel rejects commas between arguments, your regional settings may require semicolons instead. Excel’s formula separator depends on those settings; use the separator Excel expects on your computer.

All results are the same or seem wrong: Select a rank cell and inspect the formula bar. Confirm that the first argument points to the score on that row and that the second argument covers the complete score list. Also check the order argument: 0 ranks high to low, while 1 ranks low to high. A formula copied from another worksheet may point to a different range than you intended.

Some rows show no useful rank: Look for blank cells, text values, errors, or numbers stored as text in the score column. Make sure every item you want ranked has a valid numeric value. If blank rows are part of the layout, avoid treating them as genuine scores; either fill only the rows containing data or add a blank check around the ranking formula. With names in A2:A7 and scores in B2:B7, this optional formula in C2 leaves the result blank when B2 is empty: =IF(B2="","",RANK.EQ(B2,$B$2:$B$7,0)). Fill it down only as far as the data area.

The unique ranks change after sorting: That is expected when the COUNTIF formula breaks ties by first appearance. If the order must stay stable, add a permanent secondary key, such as an entry number, and define the tie-break rule around that key. Do not assume the row-order method is a permanent or merit-based decision.

The values are correct but the displayed result looks odd: A rank is usually a whole number for RANK.EQ, but RANK.AVG can return decimal values such as 2.5. If the cell is formatted to show no decimal places, Excel may display a rounded-looking result. Select the rank cells and use Home > Number to choose a suitable number format, or increase the displayed decimal places when average ranks are intended.

Frequently Asked Questions

What is the difference between RANK.EQ and RANK.AVG?
RANK.EQ gives tied values the same rank and leaves gaps after ties. RANK.AVG also gives tied values the same result, but that result is the average of the positions they occupy, so it can be fractional.

Why does Excel skip a rank after a tie?
With RANK.EQ, two equal values at rank 2 occupy both second and third place. The following value is therefore rank 4. This is the standard competition-style ranking behavior, not a missing result.

How do I rank from lowest to highest?
Set the order argument to 1 in RANK.EQ or RANK.AVG. The smallest numeric value then receives rank 1. This is useful for measures such as completion time when a shorter time is better.

Can I make tied values have unique ranks?
Yes. Add a tie-break rule. The COUNTIF formula in the steps assigns consecutive ranks by the order in which equal values appear in the source list. For a meaningful ranking, use a clearly defined secondary measure instead of arbitrary row order.

Will the formula update when I change a score?
Yes. Excel recalculates the rank when a referenced score changes, provided calculation is not set to manual. Check Formulas > Calculation Options if results do not refresh. Adding new rows outside the formula’s fixed range will not automatically include them, so extend the range or use a table-based setup.

Does this work in Excel for Mac or Excel for the web?
The RANK.EQ and RANK.AVG functions work in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. The ribbon layout and some keyboard shortcuts can differ, but the formulas and tie behavior are the same. If your regional settings use a different argument separator, enter the separator Excel accepts.

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.