To rank numeric values without ties in Excel, combine RANK.EQ with COUNTIF. With values in B2:B10 and the result in C2, enter =RANK.EQ(B2,$B$2:$B$10,0)+COUNTIF($B$2:B2,B2)-1 and fill down; equal values receive consecutive ranks in their current row order.
The formula for unique ranks
The formula has two parts. RANK.EQ calculates the ordinary descending rank, so the largest value is first. COUNTIF counts how many times the current value has appeared from the top of the list through the current row. Subtracting one means the first occurrence keeps its ordinary rank, while each later occurrence gets the next number.
For example, suppose B2:B6 contains 98, 95, 95, 90, and 87. The ordinary ranks are 1, 2, 2, 4, and 5 because Excel gives equal scores the same rank and leaves a gap after the tie. The combined formula returns 1, 2, 3, 4, and 5 instead. The second 95 is assigned rank 3 because it is the second occurrence of that score.
The dollar signs keep the full comparison range fixed as you copy the formula, while the first part of the COUNTIF range expands from B2 to the current row. That expanding range is what makes the formula count tied values in list order. Put the formula in C2, use the fill handle or copy it down, and check that every relevant row has a result.
What “without ties” means
Excel’s usual ranking functions do not decide which equal value deserves a better position. RANK.EQ gives tied values the same rank; RANK.AVG gives them the average of the positions they occupy. Neither function, by itself, creates a unique order. The extra COUNTIF term supplies a tie-breaking rule: the first matching value in the rows wins the earlier position.
This rule is practical, but it is not a statement that one tied result is objectively better. It simply uses row order as the tiebreaker. If rows are sorted or rearranged, the rank numbers among equal values can change. Decide what “without ties” should mean for your data before choosing a formula: first entered, lowest time, earliest date, alphabetical name, or another meaningful criterion.
There is a related distinction between unique rank numbers and dense rank. Ordinary competition ranking may show 1, 2, 2, 4, leaving a gap after a tie. Dense ranking would show 1, 2, 2, 3. The formula above instead assigns every numeric record its own position, such as 1, 2, 3, 4. It is an ordinal ranking, not a dense ranking with shared places.
Set up and fill the formula
For a simple list, keep the values together in one column and put the rank in an adjacent column. In this example, the score is in column B, the first data row is row 2, and the last included score is in row 10. Enter the formula in C2, then copy it down through C10. The last row in the fixed range must match the full list; adjust B2:B10 if your data has a different size.
- Choose a column for the ranking results and label it, such as Rank.
- Enter the formula in the first data row, using the correct score range and row number.
- Copy the formula down alongside every score you want ranked.
- Review a repeated score to confirm that its later occurrence receives the next rank.
- If you add rows later, extend the formula’s fixed comparison range or use an Excel Table so the data and formulas can expand together.
The formula ranks larger values first because its final argument is 0. To put the smallest value first, change that argument to 1. With values in B2:B10 and the result in C2, the ascending formula is =RANK.EQ(B2,$B$2:$B$10,1)+COUNTIF($B$2:B2,B2)-1. For example, times of 42, 45, and 51 would rank 1, 2, and 3 in ascending order, so the fastest time gets the top rank.
Choose a meaningful tiebreaker
Row order is a convenient fallback when the order of the tied records does not matter. If the ranking will be used to award places, select participants, or report a performance result, a stated secondary measure is usually clearer and easier to defend. A tiebreaker should be relevant to the task, applied consistently, and recorded in the workbook so readers can understand why tied primary values received different positions.
- First occurrence: use the COUNTIF formula above when the current list order is the agreed rule.
- Lowest secondary value: for example, prefer the shorter completion time among equal scores.
- Earliest date: among equal totals, prefer the earlier qualifying date.
- Unique identifier: use an ID or another stable field as a final tiebreaker if all meaningful measures can still match.
Be cautious about using names as a tiebreaker: alphabetical order can produce a unique sequence, but it may have no bearing on performance. Also make sure the chosen secondary field really separates records. If two records share both the score and the tiebreaker, the ranking rule still needs a final criterion or an explicit policy for that remaining tie.
Rank by a second numeric measure
When a higher score is better but a lower time breaks equal scores, COUNTIF can be replaced or supplemented with COUNTIFS. This example assumes scores are in B2:B10, times are in C2:C10, and the rank formula goes in D2. It ranks higher scores first and, when scores match, lower times first: =1+COUNTIF($B$2:$B$10,">"&B2)+COUNTIFS($B$2:$B$10,B2,$C$2:$C$10,"<"&C2).
The first count is the number of records with a higher score, so those records must rank ahead. The COUNTIFS term counts records with the same score and a lower time, which also puts them ahead. If a player has 95 points and a time of 40 seconds, and another has 95 points and a time of 43 seconds, the first player ranks above the second. The calculation returns unique positions only if the combined score-and-time criteria distinguish the records; otherwise, tied records can still receive the same rank.
For a larger number of criteria, write down the order of priority first, then build a comparison rule that counts records ahead on the first criterion or, when that criterion matches, ahead on the next one. A unique ID can settle any final equality. If the logic becomes difficult to audit, separate the calculation into helper columns or sort the records by the agreed criteria and use row positions. A transparent workbook is preferable to a compact but obscure formula.
Blanks, text, and changing data
If the score column contains unused rows, avoid displaying a rank for a blank score. With scores in B2:B10 and ranks in C2:C10, use this guarded version in C2 and copy it down: =IF(B2="","",RANK.EQ(B2,$B$2:$B$10,0)+COUNTIF($B$2:B2,B2)-1). It returns a blank when the current score cell is blank, rather than showing a rank for an empty row. The comparison range can still include blank cells; for a numeric list, RANK.EQ ranks the numeric values in that range.
Check that the scores are actual numbers, not numbers stored as text. A value that looks like 95 but is stored as text may not participate in the ranking as expected. Mixed units and inconsistent data also cause misleading results: for example, comparing seconds in one row with minutes in another does not become meaningful just because the formula returns a number. Clean and standardize the values before ranking.
When records are added, the formula’s absolute range does not automatically grow if it was entered as a fixed reference such as B2:B10. You can edit the range, or convert the dataset to a Table using Insert > Table and use table columns in formulas. In Microsoft 365, Table formulas generally fill down the calculated column as rows are added. Verify the table formula and included rows after importing or appending data, particularly if the workbook has other data immediately below the table.
Common mistakes and checks
A frequent mistake is using only RANK.EQ and expecting unique positions. That function intentionally assigns the same rank to equal values. Another is forgetting the final minus one in the COUNTIF adjustment: without it, even the first occurrence of a value is moved down by one position. Check the top value, a repeated value, and the bottom value to catch these errors quickly.
Also confirm that the sort direction matches the goal. A final argument of 0 ranks high-to-low; 1 ranks low-to-high. Check the range boundaries as well: excluding a row can change every rank below it, while accidentally including a total or an unrelated number can distort the results. If the worksheet is filtered, the formula still evaluates its referenced range; hiding rows does not automatically mean they are excluded from the ranking.
For a quick audit, compare the number of nonblank numeric records with the number of distinct rank positions. If each record is meant to receive one unique rank, the output should run from 1 through the record count without repeats or gaps. If it does not, look for duplicate secondary criteria, blanks, text-formatted numbers, or a range that does not include all the data.
Frequently Asked Questions
What formula ranks without ties in Excel?
For descending numeric values in B2:B10, use =RANK.EQ(B2,$B$2:$B$10,0)+COUNTIF($B$2:B2,B2)-1 in C2 and fill down. It breaks equal-value ties by the order in which they appear in the rows.
How do I rank the lowest number as 1?
Change the final argument of RANK.EQ from 0 to 1. The COUNTIF part still assigns later occurrences of equal numbers the next rank, so each numeric row can have a different position.
Will the formula keep the same tied order if I sort the rows?
No. The simple formula uses row order as its tiebreaker, so sorting or rearranging records can change which matching value receives the earlier rank. Use a stable secondary field if the order must not depend on where a row happens to sit.
Can I rank records by score and then time?
Yes. Count records with a higher score, then count records with the same score and a lower time, as in the COUNTIF and COUNTIFS example above. Make sure the combined criteria distinguish every record if every rank must be unique.
Why do I see an unexpected rank or blank result?
Check for values stored as text, blank score cells, an incorrect range, or a direction setting that does not match your goal. A filtered-out row may still be included in the calculation because hiding a row does not remove it from the referenced range.
Related
- Open Point List Template for Excel: Setup and Use Guide
- Is Microsoft Excel Open Source? Free Alternatives Explained
- Open Notepad with Excel: Use Both or Import Text Files
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Scenario Manager Tutorial: Compare What-If PlansTo use Excel Scenario Manager, build a worksheet model, then select Data > What-If Analysis > Scenario Manager…
- Excel Day Timeline: Build a Daily Schedule or Date FilterAn Excel day timeline can mean either a daily schedule laid out across worksheet columns or a PivotTable…
- How to Filter Multiple Dates in Excel by Range or ListTo filter multiple dates in Excel, turn on the column filters, open the arrow on your date column,…
- Excel Filter: Exclude Specific Text from ResultsIn Excel for Microsoft 365 on Windows, open the filter arrow on the column you want to check,…
- Excel Simple Timesheet Template: Setup and FormulasTo make a simple timesheet in Excel, enter Date, Start, End, Break (minutes), and Hours in columns A:E,…
- Excel Date Count Formula: Count Days and DatesIf your dates are real Excel date values in A2:A100, use =COUNT(A2:A100) to count the cells containing dates.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.