With scores in A2:A6 and the first rank in B2, enter =RANK.EQ(A2,$A$2:$A$6,0) to rank the largest number first, then fill the formula down. Excel returns 1 for the highest value; equal values share a rank. Change the last argument to 1 to rank the smallest value first. This guide shows how to set up the data, copy the formula correctly, choose how ties work, and diagnose unexpected results.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The core ranking functions also work in Excel for Mac and Excel for the web, though some keyboard shortcuts differ.
- A column or row of numeric values to rank, such as scores, sales totals, times, or quantities. The examples use cells A2:A6.
- An empty neighboring column for the ranking results. The examples put ranks in column B.
- A decision about ranking direction: highest value first, or lowest value first. For most scores and totals, highest first is the usual choice; for race times or costs, lowest first may be appropriate.
You do not need to sort the data before ranking it. The rank formula compares each value with the full range, so the original row order can stay intact.
Step-by-Step
- Set up a clear list. Put a descriptive heading such as Score in A1, then enter one numeric value in each cell from A2 downward. For this worked example, enter 92, 85, 85, 73, and 60 in A2:A6. Put Rank in B1. The repeated 85 is intentional: it demonstrates how Excel handles ties. Keep headings outside the range of values you plan to rank.
- Choose the ranking direction. Decide what rank 1 should mean before entering the formula. A descending ranking gives the largest value rank 1, which is useful when a higher score or total is better. An ascending ranking gives the smallest value rank 1, which is useful when a lower time or cost is better. The optional order argument controls this choice: 0, or an omitted order, means descending; 1 means ascending.
- Enter the descending formula. With the example values in A2:A6 and the first result in B2, enter
=RANK.EQ(A2,$A$2:$A$6,0)in B2, then press Enter. The formula ranks the value in A2 against every value in A2:A6. The dollar signs lock the comparison range so it does not shift when you copy the formula. For the example, B2 displays 1 because 92 is the largest value. - Fill the formula down the list. Select B2 and drag the small fill handle at the lower-right corner of the cell down to B6. You can also select B2:B6 and use Fill Down: press Ctrl+D on Windows, or Command+D on a Mac. The reference to the value being ranked should change by row, while the comparison range remains fixed. The resulting ranks for 92, 85, 85, 73, and 60 are 1, 2, 2, 4, and 5. The next rank after the tied values is 4, not 3.
- Use ascending order when the lowest value should rank first. With values still in A2:A6 and the first ascending result in C2, enter
=RANK.EQ(A2,$A$2:$A$6,1)in C2 and fill it down to C6. In this example, the ranks are 5, 3, 3, 2, and 1, respectively. The 60 ranks first, while 92 ranks fifth. You can put these results in B instead if you do not need to keep the descending ranks. - Choose how tied values should be displayed. RANK.EQ gives equal values the same rank and leaves a gap after the tie. If you want tied values to receive the average of the rank positions they occupy, use RANK.AVG instead. With values in A2:A6 and the first average rank in D2, enter
=RANK.AVG(A2,$A$2:$A$6,0)in D2 and fill it down. The two 85 values occupy positions 2 and 3, so each receives rank 2.5; the full results are 1, 2.5, 2.5, 4, and 5. RANK.AVG is available in current Excel versions, including Microsoft 365. - Check and extend the results. Compare a few ranks with the values: under descending order, the largest number should be 1, and under ascending order, the smallest should be 1. If you add more numbers later, update the formula’s comparison range or use a larger range from the start. For example, if the list may grow to row 100, a reference that covers A2:A100 will include future entries, although blank cells do not receive a displayed rank by themselves. Avoid including unrelated numbers elsewhere in column A.
RANK.EQ and RANK.AVG both use the form function(value, reference, [order]). The value is the item whose position you want, and reference is the complete set being compared. The square brackets in function descriptions mean the order argument is optional; do not type those brackets into an Excel formula. If you leave order out, Excel ranks in descending order.
Common Mistakes
- Locking the wrong reference. If you write the comparison range without dollar signs, filling down changes it from A2:A6 to A3:A7, then A4:A8. Those shifting ranges produce inconsistent ranks. Lock both ends of the comparison range as in
$A$2:$A$6, while leaving the individual value reference such as A2 relative so it adjusts on each row. - Choosing the wrong order argument. A formula that returns 1 for the largest value is not broken; it is using descending order. For times, where a smaller number is better, select ascending order instead. Confirm the intended meaning of rank 1 before copying the formula through a report.
- Expecting unique ranks when values tie. RANK.EQ deliberately assigns the same rank to equal numbers. It also skips the following rank position. If you need a distinct sequence for sorting or assigning places, ranking alone does not decide how tied items should be separated. Choose an explicit tie-break rule, such as a secondary score, rather than assuming Excel will invent one.
- Ranking text that looks like a number. A value imported as text may not be treated as a numeric score. A green error indicator, a left-aligned value, or inconsistent calculations can be a clue. Convert the entries to numbers before ranking, and check that decimal and thousands separators match the regional settings used by the workbook.
- Including headings or unrelated cells. The comparison reference should contain the values in the ranking group only. Including a header, another measure, or a stray numeric note can change the result or cause an error. Check the range shown in the formula bar if a result seems out of place.
Tips
- Make the ranking range easy to maintain. If you expect new records, reserve enough rows in the reference range, or convert the data into an Excel table using Insert > Table. A table can expand as records are added, but formulas that refer to table columns use structured references and are written differently from the A2:A6 examples here. Verify that a newly added record is included in the rank comparison.
- Keep the rank beside its source value. Placing the result in an adjacent column makes it easier to see why each item received its rank. If you later sort the records, select the entire data block, not just the value or rank column, so the ranks remain aligned with their original records.
- Format ranks as whole numbers where appropriate. RANK.EQ returns integer positions. RANK.AVG can return a decimal for ties, such as 2.5. If decimals are meaningful, show them; if the report needs another presentation, choose a clear tie policy rather than merely hiding decimal places and suggesting a different result.
- Use the formula bar to inspect copied formulas. Select a few cells near the top and bottom of the ranking column. Confirm that the first reference changes from A2 to A3 and the locked comparison range remains $A$2:$A$6. This quick check catches many copy and paste mistakes.
Troubleshooting
- The formula shows an error. Confirm that the first argument refers to a value and the second argument is a valid range. Check for a missing parenthesis or comma. In Microsoft 365 on Windows, Excel formulas normally use commas between arguments, as shown here; some regional settings use semicolons instead. If Excel rejects a valid comma-separated formula, check your system’s list-separator settings rather than changing the function name.
- Several values show the same rank. Check whether the source numbers are actually equal, including decimal places that are hidden by number formatting. Two values that both display as 10 might really be 10.04 and 10.39, while values that look different may have been rounded for display. Increase the displayed decimal places temporarily to inspect them. If the underlying numbers are equal, a shared RANK.EQ result is expected.
- The ranks change unexpectedly down the column. Click a lower result and inspect its formula. The value reference should point to that row, but the comparison range should still begin at A2 and end at A6. If the range has shifted, restore the dollar signs in the first formula and fill down again.
- A number is missing from the comparison. Check whether it is stored as text, contains leading or trailing spaces, or falls outside the reference range. Re-enter or convert the value to a real number, then verify the full range. If the number was added below the original last row, extend the comparison range and copy the formulas to the new row.
- The rank is correct but the displayed order looks wrong. A rank formula calculates a position; it does not rearrange the records. If you want the rows physically ordered by rank, select the complete data set and use Data > Sort, choosing the rank column and the desired order. Sorting only one column can disconnect names or other details from their values.
Frequently Asked Questions
What is the basic formula to rank numbers in Excel? With values in A2:A6 and the first rank beside them in B2, use =RANK.EQ(A2,$A$2:$A$6,0) and fill down. The final 0 makes the largest value rank first.
How do I rank the smallest number as 1? With the values in A2:A6 and the first result in B2, enter =RANK.EQ(A2,$A$2:$A$6,1). The final 1 requests ascending order, so the smallest value gets rank 1.
How does Excel rank duplicate numbers? RANK.EQ assigns the same rank to equal values and skips the rank positions they occupy. For values 92, 85, 85, 73, and 60 ranked largest first, the results are 1, 2, 2, 4, and 5. RANK.AVG gives tied values the average of their occupied positions instead.
Can I rank numbers without sorting the list? Yes. RANK.EQ calculates each value’s position against the specified range without moving any rows. Sorting is only needed if you also want the records displayed in rank order.
Why does my formula return a number that seems wrong? Check the order argument, the fixed comparison range, and whether the source values are genuine numbers. Also inspect ties and hidden decimal places. A formula can calculate correctly while the displayed number format makes the inputs appear equal or different.
Can I rank only part of a list? Yes. Set the reference to just the values in the group you want to compare. For example, create a separate reference range for each class or category, then rank each value against its own group’s range. Ensure that every formula in a group uses the same complete range.
Related
- How to Paste in Excel Only Visible Cells Without Hidden Rows
- Paste in a Filtered List in Excel Without Hidden Rows
- How to Paste in Excel in Multiple Cells at the Same Time
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUMIFS With Multiple Criteria: A Practical GuideWith sales in D2:D7, regions in B2:B7, and products in C2:C7, use =SUMIFS(D2:D7,B2:B7,"East",C2:C7,"Tea") to add sales where the…
- Excel SUBTOTAL Formula Examples for Filtering and TotalsWith sales values in B2:B6, use =SUBTOTAL(9,B2:B6) to add them; for the example values 120, 80, 150, 70,…
- Filter Excel by Highlighted CellsYes. In Excel for Microsoft 365 on Windows, open the filter arrow in the column containing the highlighted…
- Excel MIN IF Multiple Criteria: How to Find the Smallest MatchThe direct answer: Excel has no single MINIFS function, but you can combine MIN with the IF function…
- If Excel Contains Specific Text: Formulas That WorkTo check whether a cell contains specific text in Excel, use IF with SEARCH and ISNUMBER. With the…
- SQL Server REPLACE Function: Syntax and ExamplesSQL Server’s REPLACE(string_expression, string_pattern, string_replacement) function replaces every occurrence of one string with another and returns the revised…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.