How to Rank Values in Excel from Highest to Lowest
Question details
The user needs a method to rank a list of numeric values, such as batting averages or test scores, ensuring the largest value receives the number 1 rank.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing and evaluating statistical data to determine the descending order or leaderboard standing of specific numeric figures.
- Observed behavior
- Requires a formula that outputs sequential ranks next to the original data, dynamically assigning rank 1 to the highest number.
Ensure your numeric data is organized in a single continuous column without blank cells or text values, as these can disrupt the ranking formula calculations.
Use the RANK.EQ Function with Absolute References
This is the standard and most reliable method for ranking values in descending order, compatible across almost all Excel versions.
The RANK.EQ function evaluates a specific number against a range of numbers and returns its relative size. Using absolute references (dollar signs) ensures the comparison range does not shift when you apply the formula to other cells.
Click on the empty cell directly adjacent to your first data point where you want the rank to appear (for example, cell B2).
Type =RANK.EQ(A2, $A$2:$A$6, 0) into the formula bar. Replace A2 with your target number and $A$2:$A$6 with the full range of your data.
Press Enter to confirm the formula. Then, click the bottom-right corner of the cell and drag the fill handle down to rank the remaining values in your list.

Use a Dynamic Array Formula (Microsoft 365 / Excel 2021+)
If you are using a modern version of Excel, you can use a dynamic array to spill the results automatically without needing to drag the formula down.
Rank Data Easily with WPS Office Spreadsheets
WPS Spreadsheet fully supports the RANK.EQ function and dynamic arrays, allowing you to quickly determine the standing of your data seamlessly. It's a highly capable tool for complex data analysis without the high subscription costs.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing the values you want to rank.
- 2. Enter the ranking formula: Select the adjacent cell and type =RANK.EQ(A2, $A$2:$A$6, 0) to set up the highest-to-lowest ranking.
- 3. Confirm the calculation: Press Enter to generate the rank for your first data point.
- 4. Fill the series: Double-click or drag the fill handle at the bottom-right corner of the cell to instantly rank all remaining values.

Frequently Asked Questions
What happens if two values are exactly the same?
When two or more values are identical, the RANK.EQ function assigns them the same top rank. For example, if two values share the 2nd place, both are assigned the rank '2', and the next value in the list will be ranked '4'.
Can I rank values from lowest to highest instead?
Yes. To rank values in ascending order (where the smallest number is 1), change the final argument in your formula from 0 to 1. The formula will look like this: =RANK.EQ(A2, $A$2:$A$6, 1).
What is the difference between RANK and RANK.EQ?
RANK.EQ is the modern, updated equivalent of the older RANK function. While RANK still works for backward compatibility with older Excel versions, Microsoft introduced RANK.EQ to improve accuracy and consistency in modern spreadsheet applications.




