Descending rank
=RANK.EQ(B2,$B$2:$B$6,0)Ranks the largest value as 1.
Rank numbers in Excel with RANK.EQ, choose descending or ascending order, and understand ties, RANK.AVG, and legacy RANK.
Direct answer
Use =RANK.EQ(B2,$B$2:$B$6,0) to rank B2 from highest to lowest. Use 1 instead of 0 for ascending order. Tied values receive the same RANK.EQ rank.
=RANK.EQ(B2,$B$2:$B$6,0)Ranks the largest value as 1.
=RANK.EQ(B2,$B$2:$B$6,1)Ranks the smallest value as 1.
=RANK.AVG(B2,$B$2:$B$6,0)Returns the average rank for tied values.
=RANK(B2,$B$2:$B$6,0)The older RANK function remains available for compatibility.
Press ⌘+Enter to submit
Need more than a single RANK Excel Formula formula? Try our free AI for Excel — it reads your entire spreadsheet, answers questions in plain English, and builds charts and analysis in the same chat.
RANK.EQ ranks the highest sales value first. The two $92,000 values share rank 2.
| Rep | Sales (B) | Descending rank |
|---|---|---|
| Maya | $98,000 | 1 |
| Noah | $92,000 | 2 |
| Iris | $92,000 | 2 |
| Leo | $84,000 | 4 |
| Zoe | $79,000 | 5 |
Use B2 as the number whose position you want to calculate.
Use $B$2:$B$6 so the ranked range stays fixed when you fill the formula down.
Use 0 for descending rank or 1 for ascending rank.
Use RANK.EQ for equal ranks or RANK.AVG when tied values should receive the average position.
number:
The number for which you want to find the rank within the specified ref.
ref:
The array or range of numbers that you want to evaluate against the specified number.
order:
An optional parameter that specifies how to rank the numbers. Use 0 or omitted for descending order (highest number gets rank 1) and any non-zero value for ascending order (lowest number gets rank 1).
The RANK function in Excel is a useful tool that allows you to determine the rank of a specific number within a list of numbers. It evaluates a range of cells and assigns a rank based on the value of the number in relation to the other numbers in the list.
In simpler terms, the RANK function helps you understand how a particular value compares to others, making it easier to analyze performance or standings.
Imagine you are managing a student performance report, and you have the following data in an Excel spreadsheet:
| Student | Score |
|---|---|
| Alice | 85 |
| Bob | 92 |
| Charlie | 78 |
| David | 88 |
| Eve | 95 |
You want to find out the rank of each student based on their scores.
To calculate the rank of Bob's score, you would use the RANK formula as follows:
excel=RANK(B2, B2:B6, 0)
When you apply the formula for Bob, it evaluates his score against the other scores. The output would be:
| Student | Rank |
|---|---|
| Bob | 2 |
Here, Bob's score of 92 ranks him second among the students, as only Eve has a higher score of 95.
The RANK function is particularly useful when you need to assess performance or standings based on numerical values. It allows you to quickly determine how a specific value compares to others, which can be beneficial in various scenarios such as competitions, sales performance, or academic results.
Understanding how to use the RANK function can significantly enhance your data analysis capabilities in Excel, enabling you to extract meaningful insights from your datasets.
Happy ranking!
You have a table where Column A contains student names and Column B contains their corresponding exam scores. You can use the RANK function to determine the rank of each student based on their exam scores. This allows for easy identification of top performers and helps in making decisions regarding awards and recognitions without the need for complex calculations.
Beyond the RANK Excel Formula function: Formula Bot is a full Excel AI that goes well past individual formulas. Upload any spreadsheet and ask questions in plain English — you'll get formulas, data analysis, and charts back in seconds. It's a free AI Excel assistant that works with any .xlsx, .xls, or .csv file — no plugins, no Microsoft 365 required.
Don't have a spreadsheet yet? Use our free AI spreadsheet generator to turn a text prompt into a downloadable Excel file — a full AI Excel sheet maker that builds headers, columns, and formulas from your description.
Compare your options
Open a neutral research prompt in the AI tool you already use. Compare capabilities, limitations, and fit before you choose.
AI answers can be incomplete. Check source links, current pricing, and product documentation before deciding.
Sign up for Formula Bot and get unlimited AI-powered formula generation, explanations, and advanced data analysis.