To rank values in Excel, use RANK.EQ: with scores in B2:B6, enter =RANK.EQ(B2,$B$2:$B$6,0) in C2 and fill down. The formula ranks the highest score as 1; change the final 0 to 1 to rank the lowest value as 1.
What ranking means in Excel
A rank expresses where a number sits relative to the other numbers in a group. It does not rearrange the data: it returns a position number that you can display beside names, scores, sales, times, or other numeric values. For example, rank can show which salesperson has the highest total while keeping the worksheet in its original order.
Excel’s RANK.EQ function is the usual choice when you want conventional competition ranking. It assigns identical values the same rank and leaves a gap after a tie. RANK.AVG is an alternative that gives tied values the average of the positions they occupy. These functions are available in current Microsoft 365 Excel for Windows.
RANK.EQ syntax and sort order
The syntax is RANK.EQ(number,ref,[order]). number is the value to rank, ref is the range being compared, and the optional order argument determines which end is ranked first. A zero, or an omitted order argument, ranks numbers from largest to smallest. A nonzero order, usually 1, ranks from smallest to largest.
- Descending: the largest value is rank 1. Use this for high scores, revenue, or test results where bigger is better.
- Ascending: the smallest value is rank 1. Use this for elapsed times, costs, or error counts where smaller is better.
- Ties: equal values receive equal ranks with
RANK.EQ; the next rank can be skipped.
The dollar signs in $B$2:$B$6 make the comparison range absolute. When you copy the formula down, the value reference changes from B2 to B3 and onward, while the full set of scores remains fixed. Without those dollar signs, the range can shift as you fill down, changing the comparison set and producing misleading results.
A worked example with scores
Suppose B2:B6 contains 92, 85, 92, 76, and 88, in that order. In the descending ranking, the 92s tie for first, 88 is third, 85 is fourth, and 76 is fifth. The two first-place scores occupy positions one and two, so there is no rank 2 in this competition-ranking result.
With scores in B2:B6 and the result intended for C2, enter this formula and fill it through C6: =RANK.EQ(B2,$B$2:$B$6,0). The results are 1, 4, 1, 5, and 3, respectively. The formula compares each row’s score with the entire fixed range, not just the rows above it.
To average the positions of ties instead, with the same scores in B2:B6 and the result in C2, use =RANK.AVG(B2,$B$2:$B$6,0) and fill down. The results are 1.5, 4, 1.5, 5, and 3. The tied 92s would occupy positions 1 and 2, whose average is 1.5. This does not mean either score is below first place in value; it is a different way of reporting a tie.
How to enter and copy a rank formula
- Put the values to compare in one column, with a heading in the row above if useful. Keep the values in a consistent numeric format.
- Click the first cell beside the data where you want a rank to appear.
- Type the appropriate
RANK.EQformula, checking that the first argument refers to the value on that row and the second argument covers the full comparison range. - Press Enter. Select the formula cell and drag its fill handle down the column, or copy and paste it into the remaining result cells.
- Check at least one result manually: for descending rank, count how many values are greater than the chosen value, taking ties into account.
On Windows, Ctrl+C and Ctrl+V copy and paste; Ctrl+D fills the selected cells down from the top cell. On Mac, use Command+C and Command+V for copy and paste; the fill-down shortcut is Control+D in Excel for Mac. Menu and ribbon locations can differ slightly between Mac and Windows, but the formulas themselves work in current Excel versions on both. In Excel for the web, enter and fill the formula in the same way; the interface is browser-based.
Ranking from lowest to highest
For values where a smaller number should receive the better rank, set the order argument to 1. With completion times in B2:B6 and the result in C2, enter =RANK.EQ(B2,$B$2:$B$6,1) and fill down. If the times are 42, 38, 51, 38, and 45 seconds, the ranks are 3, 1, 5, 1, and 4. The two fastest times tie at rank 1, so rank 2 is skipped.
Remember that the formula only reports order; it does not decide what counts as “best.” A low number may be best for time or cost, while a high number may be best for points or sales. Choose the order based on the meaning of the data, not on whether the column happens to be sorted upward or downward.
Understanding ties and alternative ranking styles
There are several legitimate ways to show tied values, so decide what the rank is meant to communicate. RANK.EQ uses competition ranking: 100, 90, 90, 80 becomes 1, 2, 2, 4. RANK.AVG gives 1, 2.5, 2.5, 4 for those values. In dense ranking, the same values become 1, 2, 2, 3: rank numbers do not have gaps.
If B2:B6 contains the scores and you want a dense, highest-first rank in C2, Excel 2021 or Microsoft 365 can use =MATCH(B2,SORT(UNIQUE($B$2:$B$6),1,-1),0). The formula makes a descending list of distinct scores and finds the current score’s exact position in that list. For the example scores, the dense ranks are 1, 3, 1, 4, and 2. This approach uses UNIQUE and SORT, which are available in Excel 2021 and Microsoft 365; older Excel versions may need a different method.
Sometimes a report needs every row to have a distinct rank even when values tie. You can use the order of appearance as a tie-breaker. With scores in B2:B6, the result in C2, and the desired tie-break order being top-to-bottom row order, enter =RANK.EQ(B2,$B$2:$B$6,0)+COUNTIF($B$2:B2,B2)-1 and fill down. In the worked example, the two 92s receive ranks 1 and 2, while 88 remains rank 3, 85 rank 4, and 76 rank 5. This creates unique ranks but makes the first tied row win; it is not a neutral tie rule. Use a meaningful secondary measure if row order should not decide the result.
Ranking values within categories
A common follow-up is ranking each person only against others in the same team, region, or product category. Suppose category names are in A2:A10, numeric scores are in B2:B10, and you want each score’s descending competition rank in C2. Use =1+COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,">"&B2) and fill down. It counts rows with the same category and a strictly higher score, then adds one. Equal scores in one category receive the same rank, with the usual gaps after ties.
This conditional-count approach is useful when the ranking condition is simple. Check that category labels are consistent: “North” and “North ” (with a trailing space) may be treated as different text. If you want ties broken within each category, add a clearly chosen tie-break rule rather than assuming the formula can infer which tied row should come first.
Common errors and checks
- The comparison range moved: lock the full range with dollar signs, as in
$B$2:$B$20, before filling the formula. - The result is unexpectedly 1: check whether the order argument is 0 or 1, and confirm that the comparison range contains all intended values.
- A value seems ignored: numbers stored as text may not behave like numeric values in calculations. Convert or clean the source data, and check for spaces or imported text.
- There are ties: identical numeric values normally receive the same rank. Use
RANK.AVGfor average positions or a deliberate tie-break method if unique ranks are required. - Some rows return errors or odd results: inspect the range for error values, blanks, or unexpected entries. The rank range should cover the intended data and should not accidentally include a heading or unrelated number.
A useful check is to sort a copy of the values from largest to smallest and compare the visible order with the formula results. Sorting is not required for ranking, and it can change the layout of related rows if only one column is selected. If you sort, select the complete table so names and other row details remain attached to their values.
Ranking versus sorting
Ranking and sorting answer related but different questions. A rank formula tells you the relative position of each value while leaving rows in place. Sorting physically reorders rows according to a column. Use a formula when you need to preserve the original order, compare values in a report, or keep ranks updated as the data changes. Sort when the purpose is to view the records from highest to lowest or lowest to highest.
For a simple table sort in Microsoft 365 for Windows, select a cell in the data and use Data > Sort, then choose the ranking column and order. Make sure the full table is included, and use headers if the dialog identifies them. Sorting does not create a permanent rank calculation: if values change later, the row order may no longer represent the new ranking. A formula recalculates when its referenced values change.
Frequently Asked Questions
How do I rank numbers in Excel from highest to lowest?
With a value in B2 and the full list in B2:B20, enter =RANK.EQ(B2,$B$2:$B$20,0) beside the first value and fill down. The largest number receives rank 1.
How do I rank numbers from lowest to highest?
Use 1 as the order argument. For a value in B2 compared with B2:B20, use =RANK.EQ(B2,$B$2:$B$20,1); the smallest number receives rank 1.
Why does Excel skip a rank after a tie?RANK.EQ gives equal values the same position and preserves their occupied places. If two values tie for first, the next value is third. Use dense ranking if you want the next distinct value to be rank 2.
What is the difference between RANK.EQ and RANK.AVG?RANK.EQ returns the same rank for tied numbers and leaves gaps afterward. RANK.AVG returns the average of the positions the tied numbers would occupy.
Can I rank each group separately?
Yes. Use a conditional-count formula such as the COUNTIFS example above to count higher values only among rows with the same category. Confirm that the group labels match consistently.
Related
- Excel Jigsaw Solver: What Works and How to Build One
- Excel Solver: Add Decision Variables and Set Constraints
- Excel Solver Binary Constraint: How to Set It Up
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Counting Unique ValuesIn Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) to count distinct nonblank values in A2:A100. It returns 0 if…
- Excel Formula for the Lower of Two Values in Two CellsTo return the lower of two values in Excel, use =MIN(A2,B2). If the values are in cells A2…
- Excel NOW Time: Current Time, Formulas, and ShortcutsTo display the current date and time in Excel, enter =NOW() in a cell; to show only the…
- INDEX and MATCH in Excel: How to Find Values FlexiblyYes. In Excel, INDEX and MATCH work together to find a value by its position: MATCH locates the…
- Excel Formula for Splitting Text in a Cell by DelimiterIn Microsoft 365 or Excel 2024, use TEXTSPLIT to divide text from one cell into separate cells. If…
- Excel Text Right of Character: Formulas and ExamplesTo return the text to the right of a character in Excel, use TEXTAFTER in Microsoft 365 or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.