To give equal values the same rank in Excel, enter a RANK.EQ formula. With scores in B2:B6, put =RANK.EQ(B2,$B$2:$B$6,0) in C2 and fill it down; the zero ranks the highest score first, and tied scores receive the same rank. If you need every row to have a different position, use the tie-breaking formula later in this guide.
What You’ll Need
- A worksheet containing the values you want to rank. This guide uses scores in B2:B6 and puts the results in C2:C6; your data can use different cells.
- Microsoft Excel for Microsoft 365 on Windows. The formulas also work in Excel for Mac and Excel for the web. The ribbon and shortcut details below are for Windows unless noted.
- Numeric values if you want a numeric ranking. If the values came from another source, check that they are numbers rather than text that only looks like numbers.
Step-by-Step
- Decide what a tie should mean. Excel can give equal values equal ranks, which leaves gaps in the ranking after a tie, or you can assign each row a distinct position using a tie-break rule. For example, scores of 95, 88, 88, and 72 have competition ranks of 1, 2, 2, and 4. The next score after the two 88s is fourth because two entries are ahead of it. If your task requires a numbered list with no gaps, choose a unique-rank method instead. Decide before entering a formula so the result matches your report, contest, or analysis.
- Set up a clear data layout. Put one item on each row, with a label in column A and its numeric value in column B. For example, A2:A6 could contain five participant names and B2:B6 their scores. Add a heading such as Rank in C1, and leave C2:C6 for results. Keeping labels and values in separate columns makes it easier to verify who received each rank and to sort or filter the data later. Do not include the heading row in the ranking range.
- Enter the equal-rank formula. With the values in B2:B6 and the first result in C2, type
=RANK.EQ(B2,$B$2:$B$6,0)in C2 and press Enter. The first argument is the value to rank, the second is the full list being compared, and the final zero specifies descending order: the largest value receives rank 1. The dollar signs lock the comparison list so it does not shift when you copy the formula down. For values 95, 88, 88, 72, and 60, this returns 1, 2, 2, 4, and 5. - Copy the formula to the other rows. Select C2, point to the small square at the lower-right corner of the selected cell, and drag it down to C6. You can also copy C2, select C3:C6, and paste. Excel changes the individual reference from B2 to B3, B4, and so on, while the dollar signs keep the comparison range at B2:B6. Check both the first and last result formulas if you want to confirm the references adjusted as intended.
- Choose ascending order if a low value should rank first. With the same values in B2:B6, use
=RANK.EQ(B2,$B$2:$B$6,1)in C2 and fill down. The final argument of 1 ranks from smallest to largest, so 60 receives rank 1 in the example and 95 receives rank 5. If you omit that argument, Excel treats it as zero and ranks the largest value first. Be explicit about the order when the result will be shared, because a valid formula can still answer the opposite question from the one you intended. - Use a distinct rank when every row needs its own place. If the same five scores are in B2:B6, enter
=RANK.EQ(B2,$B$2:$B$6,0)+COUNTIF($B$2:B2,B2)-1in C2 and fill down. The RANK.EQ part gives the usual descending rank. COUNTIF counts how many times the current value has appeared from the top of the list through the current row; subtracting one adds zero to the first occurrence, one to the second, and so on. The example returns 1, 2, 3, 4, and 5. For the tied 88s, the one appearing earlier in the worksheet gets rank 2 and the next gets rank 3. This is a row-order tie-break, not a claim that one tied score is actually higher. - Review the tie-break rule before using unique ranks. The distinct-rank formula uses the order of rows to decide which tied entry comes first. If you need ties resolved by a meaningful second measure, such as a time, date, or another score, decide how that measure should affect the result and use an appropriate sort or a formula designed for that rule. One practical option is to sort by the main score from largest to smallest and then by the second measure in the desired direction, and use row order as the tie-break. Keep a copy of the original order if it matters to your records.
- Check the result against a small example. Confirm that the largest value has rank 1 for descending order, that equal values behave as intended, and that a lower value has a lower or later rank. For equal-rank results, gaps are expected: 1, 2, 2, 4 is not an error. For unique ranks, check that each number appears once and that the tie-break order is acceptable. If you update the source values, Excel recalculates the formulas automatically unless calculation has been set to manual.
- Optionally display the ranks clearly. Select the result cells and use Home > Number Format to choose a suitable display format, or leave the ranks as General. Ranking formulas return numbers, so number formatting should not be needed to make the calculation work. If you sort the table, select the entire data range rather than only the rank column so names, values, and ranks stay together. On Mac and in Excel for the web, the formula behavior is the same; ribbon placement can vary slightly by version.
Common Mistakes
Using the wrong order argument: In RANK.EQ, zero or an omitted final argument means largest-first; one means smallest-first. If a ranking looks reversed, check that argument before changing the data. A descending score ranking and an ascending position such as race time are different tasks.
Forgetting to lock the comparison range: If the formula uses B2:B6 without dollar signs, copying it down may change the range to B3:B7. That can make ranks inconsistent or compare each value against the wrong list. Use $B$2:$B$6 for a fixed list. The first argument should remain relative so it adjusts to each row.
Thinking gaps after ties are mistakes: RANK.EQ uses competition ranking. When two values share second place, the next value is fourth. If your rules require consecutive positions instead, use the unique-rank formula and make its row-order tie-break clear to readers.
Ranking numbers stored as text: Imported data can look numeric but be stored as text, which can cause a value to be skipped or produce unexpected results. Check alignment and cell contents, or test a cell with Excel’s number conversion tools. Convert the source data to actual numbers before ranking rather than trying to repair each rank individually.
Including headings, totals, or unrelated values: A heading or subtotal in the reference range is not part of the set you intend to rank. Select only the data rows, and update the range if rows are added. Also avoid ranking a total alongside the individual entries unless that is deliberately part of the comparison.
Tips
Use RANK.EQ when equal values should share a standing, such as identical test scores or sales totals. It is usually the clearest choice because a tie remains visible as a tie. RANK.AVG is an alternative when tied values should receive the average of the rank positions they occupy. With values 95, 88, 88, 72, and 60 in B2:B6, the formula =RANK.AVG(B2,$B$2:$B$6,0) in C2, filled down, gives the two 88s rank 2.5 each: they occupy second and third place, whose average is 2.5. Use this only if average ranks suit the rules of your analysis.
For regularly updated lists, an Excel Table can make ranges easier to manage as new rows are added. Select the data and choose Insert > Table, making sure the table has headers. You can enter the ranking formula in the table’s rank column and Excel typically fills it through that column. Check the resulting reference and output after adding rows, especially if your workbook has custom table formulas or filters. A filtered view hides rows but does not by itself change the underlying set being ranked.
If a reader needs to understand a result quickly, label the ranking direction in the heading, for example Descending rank or Lowest time first. For a unique ranking, document the tie-break too, such as Earlier worksheet row wins equal scores. These notes prevent someone from interpreting a row-order decision as a difference in the underlying values.
For an existing workbook, keep the source values unchanged and put ranks in a new column. This lets you compare the result with the input and makes mistakes easier to trace. If you want to reorder the visible list, sort the whole table by the value or rank column, not just one column. Sorting a single column can separate each label from its associated value and rank.
Troubleshooting
The rank seems wrong or reversed: Inspect the last argument in RANK.EQ. Set it to 0 for largest-first or 1 for smallest-first, then verify the expected top value. Also confirm that the formula’s reference range contains exactly the values you intend to compare.
Every row shows the same rank: Check whether the first argument changed as the formula was filled down. The formulas should refer to B2, B3, B4, and so on, while each should compare against the same absolute range, $B$2:$B$6. If the first argument is locked as $B$2, every row ranks the same value.
A value is missing from the ranking: Check for text-formatted numbers, blank cells, errors, or a range that stops too early. RANK.EQ ranks the numeric values in its reference; it does not treat a text label as a score. Convert intended numeric text to numbers and expand the reference to include all the data rows.
The unique formula gives unexpected order to ties: That formula breaks ties by the order in which equal values appear in the worksheet. If a sort has changed that order, the tie-break result can change too. Decide the desired secondary rule, arrange or calculate that rule explicitly, and keep the data order stable if row order is intended to decide ties.
The result does not update after editing a score: Check Formulas > Calculation Options and select Automatic if calculation is set to Manual. Press F9 on Windows to recalculate; on Mac, use Fn+F9 on keyboards where the function keys require Fn. Excel for the web normally recalculates formulas automatically.
Frequently Asked Questions
Does RANK.EQ give tied values the same rank?
Yes. Equal numeric values receive the same rank. In descending competition ranking, a tie for second is followed by fourth place.
What is the difference between RANK.EQ and RANK.AVG?
RANK.EQ gives every tied value the same rank number. RANK.AVG gives each tied value the average of the rank positions in that tie, which can produce a decimal such as 2.5.
How do I rank from lowest to highest?
Set RANK.EQ’s third argument to 1. The smallest number then receives rank 1. Use zero, or omit the argument, to rank the largest number first.
Can I make all ranks unique when values tie?
Yes. Add a tie-break rule, such as worksheet row order. The COUNTIF formula in this guide assigns consecutive ranks and gives the earlier occurrence of a tied value the higher position. State that rule so the result is not mistaken for a difference in score.
Can I rank names alphabetically when scores are tied?
RANK.EQ alone only compares the numeric values; it does not use names to resolve a tie. Sort by score and then by name, or build a formula that explicitly incorporates both criteria. Decide whether A–Z should come before or after the other tied entries.
Does this work in Excel for Mac or Excel for the web?
Yes. RANK.EQ, RANK.AVG, and COUNTIF work in current Excel for Microsoft 365 on Windows, Mac, and the web. Formula separators can depend on regional settings, but the formulas shown use the standard comma separators.
Related
- Open a JSON File in Excel and Convert It to XLSX
- How to Open a Linked File in Excel from a Cell Link
- Open in Excel from the Web: Files and Links
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIF for Numbers in Cells: Examples and FixesTo count cells that equal a number in Excel, use =COUNTIF(B2:B20,25), replacing the range and 25 with your…
- Excel Filter Multiple Criteria in the Same Column?To filter one Excel column by several values, open its filter dropdown and select each value you want…
- Excel Sum by Date: Formulas for Daily and Monthly TotalsTo sum values for a date in Excel, use SUMIF with the date column as the criteria range…
- Excel Named Ranges in Formulas: How to Use ThemYes. Excel lets you use a named range in a formula just like a cell reference: if the…
- How to Sort Dates Correctly in Excel Without ErrorsTo sort dates correctly in Excel, make sure Excel recognizes the entries as real dates, then select a…
- VLOOKUP Between Two Sheets in Excel: How It WorksTo use VLOOKUP between two sheets in Excel, put the lookup value in one sheet and refer to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.