Function Problems

How to Rank Values in Excel from Highest to Lowest

Ayan MasoodAyan Masood Nov 10, 2026 872 views

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.

How to Rank Values in Excel from Highest to Lowest
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target cell

Click on the empty cell directly adjacent to your first data point where you want the rank to appear (for example, cell B2).

2
Enter the RANK.EQ formula

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.

3
Apply to the rest of the column

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 the RANK.EQ Function with Absolute References
Formula Breakdown: The '0' at the end of the formula explicitly tells Excel to rank in descending order. If tied values exist, they will receive the exact same rank.

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. 1. Open your dataset: Launch WPS Spreadsheet and open the document containing the values you want to rank.
  2. 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. 3. Confirm the calculation: Press Enter to generate the rank for your first data point.
  4. 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.
100% compatible with Microsoft Excel formulas and functionsEasily format and rank large datasets with a familiar user interfaceFree to use, lightweight, and highly responsive for daily data processing
microsoft office alternative - wps office

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.