To rank a value in Excel, use RANK.EQ. With scores in C2:C6 and the first score in C2, enter this in D2: =RANK.EQ(C2,$C$2:$C$6,0). The 0 ranks the largest score as 1; fill the formula down to rank the other scores. The guide below explains ties, ascending order, and what to check if the result looks wrong. These steps are for Microsoft Excel for Microsoft 365 on Windows.
What You’ll Need
- A worksheet with numeric values to rank, such as test scores, sales figures, times, or points.
- A blank column or range where you can display each value’s rank.
- A decision about ranking direction: use descending order to put the largest value first, or ascending order to put the smallest value first.
You do not need to sort the original data or install an add-in. The ranking formula compares each value with the full range you specify. The examples use scores in column C and rank results in column D, but you can substitute your own cells.
Step-by-Step
- Arrange the values and add a rank heading. Put one value per row in a single column, with a heading in the row above. For example, enter names in A1:A6 and scores in C1:C6, with the actual scores in C2:C6. In D1, type Rank. Keeping the values in one column makes it easier to select a consistent comparison range. The rank formula works on numbers; text labels can be next to the numbers but should not be included in the ranked range.
- Choose the ranking direction. Decide what rank 1 should mean before writing the formula. For scores, sales, or points, the largest value is usually rank 1. For completion times, costs, or error counts, the smallest value may be best, so rank the lowest number first. Excel’s order argument controls this: 0 means descending order (largest to smallest), while 1 means ascending order (smallest to largest). If you omit the argument, Excel uses descending order.
- Enter the first rank formula. With scores in C2:C6 and the first score in C2, select D2 and enter
=RANK.EQ(C2,$C$2:$C$6,0). Press Enter. The first argument is the value to rank, the second is the entire set of values being compared, and the final 0 specifies that the highest score is rank 1. The dollar signs lock the comparison range so it does not shift when you copy the formula. In an example list of 92, 85, 85, 71, and 60, the result for 92 is 1. - Check the result against the data. Confirm that the top value has rank 1 when you chose descending order. In the example list, the first 85 has rank 2, because only 92 is higher. The second 85 also has rank 2. The 71 receives rank 4, not 3, because the tied scores share rank 2 and Excel leaves a gap in the sequence. Checking one or two results before copying can reveal an incorrect range or order setting early.
- Copy the formula to the remaining rows. Select D2. Point to the small square at the bottom-right corner of the selected cell, called the fill handle, and drag it down to D6. You can also copy D2, select D3:D6, and paste. Excel adjusts the value reference from C2 to C3, C4, and so on, while the dollar signs keep the comparison range at C2:C6. The resulting ranks for 92, 85, 85, 71, and 60 are 1, 2, 2, 4, and 5.
- Use ascending order when the lowest value should rank first. With times in C2:C6 and the first time in C2, enter
=RANK.EQ(C2,$C$2:$C$6,1)in D2, then fill down. The 1 tells Excel to rank the smallest number as 1. For example, among times of 12, 15, 10, 18, and 13, the value 10 ranks first and 18 ranks fifth. Be consistent: changing the order argument changes the meaning of every rank in the list. - Choose how you want ties handled. The standard
RANK.EQfunction gives tied values the same rank and skips the following rank number. If you want tied values to receive the average of the positions they occupy, useRANK.AVGinstead. With scores in C2:C6 and the score to rank in C3, enter=RANK.AVG(C3,$C$2:$C$6,0)in the corresponding rank cell. For the example scores 92, 85, 85, 71, and 60, each 85 gets rank 2.5, the average of positions 2 and 3. Choose the method that fits your reporting rules.
Once the formula is in place, you can format the rank column as a number with zero decimal places. Ranks are numeric results, so they can be sorted, filtered, or used in other calculations. If you sort the whole table by rank, select the entire table before sorting so that names and their scores stay on the same rows.
Common Mistakes
- Using a moving comparison range. If the formula uses C2:C6 without dollar signs, copying it down can shift the range to C3:C7. That changes which values Excel compares and may produce inconsistent ranks. Lock the full range as $C$2:$C$6 before filling down.
- Ranking in the wrong direction. A lowest score ranked 1 can be correct if the formula uses ascending order, but wrong if you intended the highest score to win. Review the final argument: 0 or omitted means largest first; 1 means smallest first.
- Expecting ties to have unique ranks. RANK.EQ does not break ties. Equal values receive equal ranks, and later ranks may have gaps. If every row needs a unique position, decide on a clear tie-break rule, such as a secondary score, rather than assuming the rank formula will distinguish equal values.
- Including the heading or unrelated cells. The comparison range should contain the values being ranked, not a text heading or a different measure. Text is generally ignored, but an incorrect range can still leave out valid numbers or include numbers that do not belong in the comparison.
- Mixing numbers and number-like text. A cell containing text such as a score imported with a leading apostrophe may look numeric but not behave like a number. Check whether the values are stored as numbers before investigating the ranking formula.
Tips
- Use an Excel Table for a growing list. Select the data and choose Insert > Table, confirming that the table has headers if appropriate. A table can expand as you add rows. If you need a rank over the full changing dataset, make sure the formula’s comparison range includes new rows; a fixed range such as $C$2:$C$6 does not automatically include values added below row 6 unless you update it or use a table-based approach.
- Keep the rank next to the item it describes. Put names, products, or teams in a neighboring column, and retain the rank formula on the same row as its value. When sorting, select the whole dataset rather than just the rank column to prevent mismatched records.
- Make ties visible when the list is sorted. Sort the full table by the value column, largest to smallest or smallest to largest as appropriate. Sorting is for display; it is not required for RANK.EQ to calculate correctly. You can also add a note explaining whether ties share a rank or use average ranks.
- Use RANK.EQ for a clear, current formula. Excel also supports the older RANK function for compatibility with older workbooks. For new work, RANK.EQ makes the tie behavior explicit and is available in modern Excel versions, including Microsoft 365.
- Keep blanks and zeros distinct. A blank cell is not the same as a score of zero. Decide whether missing results should be excluded, filled in, or treated as zero before ranking. Otherwise, a legitimate zero may appear near the bottom of the ranking while blanks may be handled differently than you expect.
Troubleshooting
Every row shows rank 1: Check that the first argument changes as you fill down, while the comparison range stays fixed. If every formula refers to the same value cell, the results will repeat. Also confirm that the comparison range contains the whole list rather than only the current value.
The rank is too high, too low, or changes unexpectedly: Inspect the range in the formula bar and compare it with the actual data rows. A range that excludes the highest value can make other results look artificially strong. A range that includes an unrelated number can push the ranks down. Use dollar signs for a fixed range, and update its endpoints if you add or remove rows.
The formula displays an error or does not calculate: Confirm that the function name is spelled RANK.EQ and that each argument is separated by a comma in the settings assumed here. If your Excel regional settings use semicolons as argument separators, replace the commas with semicolons. Check that the value cell contains a number and that the comparison range is valid. If Excel displays the formula as text, change the cell format to General, then re-enter the formula by pressing F2 and Enter.
Some values appear to be ignored: Select a suspicious cell and check the formula bar or use Home > Number Format to inspect its format. Formatting alone does not normally turn a real number into text, but imported data sometimes stores digits as text. Convert those entries to numeric values, then recalculate. Also look for blanks or values in a different column that were accidentally omitted from the range.
The rank sequence has gaps: This is expected when RANK.EQ encounters ties. For example, two values tied at rank 2 are followed by rank 4. If your policy calls for average positions, use RANK.AVG. If you need unique ranks, establish an additional tie-breaker and calculate the ranking according to that rule.
On Mac or Excel for the web: The RANK.EQ formula and its arguments work the same way. In Excel for the web, select the cell and use the fill handle or copy and paste to extend the formula. On a Mac, the fill handle works similarly; the keyboard shortcut to fill down is Control+D in Windows and Command+D in many Mac Excel setups. If a shortcut differs in your installation, use the ribbon or the fill handle instead.
Frequently Asked Questions
What is the formula to find rank in Excel?
Use RANK.EQ with the value to rank, the full comparison range, and an order setting. Use 0 for largest-first ranking and 1 for smallest-first ranking. Lock the comparison range with dollar signs before copying the formula down.
Does rank 1 mean the highest or lowest value?
It depends on the order argument. RANK.EQ defaults to descending order, so the largest value is rank 1 when you omit the optional argument or use 0. Use 1 when the smallest value should be rank 1.
How does Excel rank duplicate values?
RANK.EQ assigns equal values the same rank and skips the next position or positions. RANK.AVG assigns tied values the average of the positions they would otherwise occupy. For example, two values tied for positions 2 and 3 each receive 2.5 with RANK.AVG.
Can I rank values without sorting the data?
Yes. The rank formula compares each value against the range whether or not the rows are sorted. Sorting only changes how the rows are displayed, so it is optional.
How do I rank only part of a column?
Use just the intended subset as the comparison range, and use each row’s value as the first argument. For instance, if you want to compare only C2:C10, do not include later rows in the range. Make sure the subset matches the group you mean to compare, such as one class or one month.
Why does a rank formula change when I copy it?
Excel adjusts relative cell references as a formula is copied. Keep the value reference relative, so it moves to the next row, and make the comparison range absolute with dollar signs, so the same complete set is used for every result.
Related
- Export an Excel Sheet to an Image: Methods and Tips
- How to Export an Excel Sheet to Word: Practical Methods
- How to Export an Excel Tab to a New Workbook
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Find and Replace: Steps, Options, and Common FixesTo find or replace content in Excel for Microsoft 365 on Windows, press Ctrl+H to open Find and…
- Adding a Checkbox in Google Sheets: Steps and TipsTo add a checkbox in Google Sheets in a web browser, select the cell or range where you…
- Excel List Template Free? Where to Find and Use OneYes—free Excel list templates are available from Microsoft and other providers, and you can also build a basic…
- Excel Min Sec Format: Display Minutes and SecondsTo display minutes and seconds in Excel, select the cells, press Ctrl+1, choose Number > Custom, and enter…
- Google Sheets COUNTIFS: Count Nonblank Cells CorrectlyTo count nonblank cells in Google Sheets, use =COUNTIFS(A2:A100,"<>"), replacing A2:A100 with your range. The criterion “<>” means…
- Subscript in Google Sheets: Methods That WorkTo show subscript in Google Sheets, paste subscript Unicode characters such as the ₂ in H₂O; Sheets does…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.