In Microsoft Excel for Microsoft 365, use RANK.EQ to rank a number against a list: with scores in B2:B10 and the first rank in C2, enter =RANK.EQ(B2,$B$2:$B$10,0). The final 0 ranks from largest to smallest, so the highest score is rank 1; use 1 instead to rank from smallest to largest.
The basic Excel RANK formula
RANK.EQ is the usual choice when you want a position in a list and equal values to share the same rank. Its arguments are the number being ranked, the reference list, and an optional order setting. The number can be a cell reference, while the reference is normally an absolute range so it stays fixed as you copy the formula.
For a concrete example, suppose the values in B2:B6 are 92, 85, 92, 70, and 85, with each value in its own row. In C2, enter the formula below, then fill it down through C6.
With the scores in B2:B6 and the rank for B2 in C2, enter =RANK.EQ(B2,$B$2:$B$6,0). The result for B2 is 1 because 92 is the largest value. Filled down, the ranks are 1, 3, 1, 5, and 3.
The dollar signs in $B$2:$B$6 lock both ends of the comparison range. Without them, copying the formula down would shift the range, so each row could be ranked against a different set of values. The first reference, B2, is intentionally relative: when copied to C3, it changes to B3.
How order and ties work
The optional third argument controls ranking direction. Use 0, or leave the argument out, for descending order: the largest number receives 1. Use 1 for ascending order: the smallest number receives 1. The word “order” can be easy to misread, so check which direction makes sense for the data before filling a column with ranks.
With the same values in B2:B6—92, 85, 92, 70, and 85—and the rank for B2 in C2, enter =RANK.EQ(B2,$B$2:$B$6,1). The result for 92 is 4. In ascending order, 70 is rank 1, both 85s are rank 2, and both 92s are rank 4.
RANK.EQ gives matching values the same rank, then skips the next rank or ranks. In descending order, the two 92s are both rank 1; the two 85s are both rank 3, not rank 2. This is sometimes called competition ranking, as in “first, first, third.” Excel is not making a mistake: two items occupy the top two positions, leaving the next available position at 3.
RANK.EQ, RANK.AVG, and the older RANK function
Microsoft 365 includes RANK.EQ and RANK.AVG. Both compare a number with a reference list and accept the same optional order argument. Choose between them based on how you want ties represented:
RANK.EQassigns tied values the same rank and leaves gaps after ties.RANK.AVGassigns tied values the average of the ranks they would otherwise occupy.
With values 92, 85, 92, 70, and 85 in B2:B6, and the rank for B3 (an 85) in C3, enter =RANK.AVG(B3,$B$2:$B$6,0). The result is 3.5: the two 85s would occupy positions 3 and 4, whose average is 3.5. The two 92s would each receive 1.5 because they occupy positions 1 and 2.
The older RANK function remains available for compatibility with older workbooks. In current Excel, Microsoft recommends using RANK.EQ or RANK.AVG to make the intended treatment of ties clear. If a workbook already uses RANK, it will generally continue to calculate; replacing it is not necessary just to get a descending rank.
Rank values within a group
A common follow-up is ranking each person or product only against others in the same category. For example, a sales table might use column A for region and column B for sales, with data in rows 2 through 10. A simple RANK.EQ over all of B2:B10 compares every region together, so it does not produce a within-region ranking.
With regions in A2:A10, sales in B2:B10, and the within-region rank for row 2 in C2, enter =1+COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,">"&B2). For example, if row 2 is North with sales of 500 and the only North sales above 500 are 700 and 600, the formula returns 3. Copy it down to rank each row within its own region.
This method counts how many records in the same region have a strictly higher value, then adds 1. Equal sales receive the same rank, and the next rank is skipped, matching the tie behavior of RANK.EQ. It ranks in descending order; to rank low-to-high within groups, change the comparison from greater than to less than. If group names may contain extra spaces or inconsistent spelling, clean or standardize those labels first, or records that appear to belong together may be treated as different groups.
Dense ranking and other ranking choices
Some lists should not skip numbers after a tie. Dense ranking gives tied values the same rank but continues with the next integer: 1, 1, 2, 3 rather than 1, 1, 3, 4. Excel does not provide a separate built-in dense-rank function, but a formula can count distinct values above the current value.
With numeric values in B2:B10 and the dense rank for B2 in C2, enter =SUMPRODUCT(($B$2:$B$10>B2)/COUNTIF($B$2:$B$10,$B$2:$B$10))+1. If the values in the list are 92, 92, 85, 70, and 70, the dense ranks are 1, 1, 2, 3, and 3. This version is intended for a range of numbers without blanks or errors; a more complex data layout may call for a helper column or a different approach.
Ranking is also different from sorting. A rank formula calculates a position while leaving the source rows where they are. Sorting rearranges rows into a new order, which can make a report easier to read but changes the displayed order of the records. Use a rank column when you need to preserve row order, refer to results elsewhere, or show ties explicitly; sort when the goal is simply to display the highest or lowest values first.
Common problems and checks
If a result looks wrong, check the direction first. A formula ending in 0 ranks the largest value as 1; a formula ending in 1 ranks the smallest value as 1. Next, confirm the comparison range covers the intended records and is locked with dollar signs when the formula is copied. A range that accidentally leaves out a row can change every rank.
- Unexpected gaps: These are normal with
RANK.EQwhen values tie. UseRANK.AVGfor average tie positions or a dense-rank method if the sequence should not skip numbers. - A value is not included as expected: Check whether it is stored as text rather than as a number. Text values are not ranked like numeric values. Convert or clean the source data as appropriate.
- Blank or error cells: Keep the ranking range focused on the relevant numeric data and resolve error values. An error in the value being ranked or in the reference data can prevent a useful result.
- Every row shows the same rank: Check whether the first argument changes as you fill down. It should normally be a relative reference such as B2, not a locked reference such as $B$2.
- A formula is displayed instead of calculated: Confirm the cell is not formatted as Text, and that the formula begins with an equals sign. If necessary, change the format to General and re-enter the formula.
In the English-language examples here, arguments are separated with commas. Excel may use semicolons instead, depending on regional settings. If Excel rejects a formula that otherwise looks correct, check the argument separator used by your installation. Excel for Mac and Excel for the web support the same basic ranking functions; the interface and regional formula separators can vary, but the formulas themselves work the same way.
Frequently Asked Questions
What is the formula to rank numbers in Excel?
For a value in B2 ranked against B2:B10 from largest to smallest, use =RANK.EQ(B2,$B$2:$B$10,0). Replace the value reference and range with the cells in your worksheet.
How do I rank from lowest to highest?
Set the third argument to 1. For example, with the value in B2 and the comparison list in B2:B10, use =RANK.EQ(B2,$B$2:$B$10,1); the smallest number will be rank 1.
Why does Excel skip a rank after a tie?RANK.EQ gives tied numbers equal ranks and counts all the occupied positions. If two values share rank 1, the next value is rank 3. Use RANK.AVG if you want the tied positions averaged instead.
Can I rank values without sorting the data?
Yes. A rank formula calculates a result in another cell and does not reorder the source rows. This is useful when the original row sequence must stay intact.
What is the difference between RANK and RANK.EQ?RANK is the older function name, while RANK.EQ makes the equal-rank treatment explicit. Both can calculate a rank; RANK.AVG is the alternative when tied values should receive an average position.
Related
- Leave Calendar Template in Excel: Build or Download
- Laundry List Template in Excel: Build and Use One
- Kids Chore List Excel Template: Build a Simple Tracker
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- ROUNDUP Excel Cell Reference: 10 Useful Formula PatternsWith 12.31 in A2, enter =ROUNDUP(A2,0) to return 13; the formula reads the number from a cell and…
- Sort Excel by Column for the Entire WorksheetTo sort a column without separating its rows, click a cell in your data, choose Data > Sort,…
- Excel VLOOKUP Shortcut Keys: What Actually WorksExcel has no dedicated keyboard shortcut that inserts or runs VLOOKUP by itself. The quickest keyboard approach is…
- VLOOKUP to Compare Two Columns on Different SheetsYes. In Excel for Microsoft 365, use an exact-match VLOOKUP to check whether each value in one sheet’s…
- How to Use the PMT Function in Excel for PaymentsUse Excel’s PMT function to calculate a fixed payment for a loan or other payment plan with a…
- Cash Flow Statement in Excel: Formulas and SetupYes. In Excel, record cash receipts and payments in a transaction log, use SUMIFS to total them by…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.