|
Home > Formulas > Statistical > RANK Excel RANK Function Examples
|
Video: How to Use RANK Function
Use the RANK worksheet function to compare numbers to other numbers in a list. I show the steps in this video, and the written steps are below the video.
- ✅ To follow along, get the Excel sample file in the download section
Three RANK functions
There are three Microsoft Excel functions for ranking numbers:
- RANK - in all versions of Excel
- RANK.EQ - same as RANK, available Excel 2016 and later
- RANK.AVG - different results for ties, available Excel 2016 and later
All three functions are in the Statistics category
On this page, I use the RANK function, and you could use RANK.EQ in any of the formulas, for the same results.
See details for the newer functions on the following Microsoft support pages:
- RANK.EQ Function support page - ranks the same as RANK, treats ties the same as RANK (top rank)
- RANK.AVG Function support page - ranks the same as RANK, treats ties differently (average rank)
Example: Rank Student Scores
The RANK function compares individual numbers, to a list of numbers, and returns the rank of a number in the list.
- Note: Any non-numeric values in the list are ignored
For example, in the screen shot below, there is an Excel table, with 10 student names and test scores.
To calculate the rank for each student's score, from highest to lowest, enter this formula in cell C2:
- =RANK([@Scores],[Scores])
or use this formula:
- =RANK.EQ([@Scores],[Scores])
The formula automatically fills down, to the last row in the table.
The student scores are all ranked, from highest value (rank 1), to the lowest number (rank 10).
RANK Function Syntax
In the RANK function syntax, there are 2 required arguments, and 1 optional argument:
- number: (required) The number you want to rank
- ref: (required) The list of numbers to use for comparison
- order: (optional) How to rank the numbers - descending or ascending
- Descending: Use zero, or leave this argument empty, to rank numbers largest to smallest
- Ascending: Use 1, or any number except zero, to rank numbers smallest to largest
Note: The RANK.EQ and RANK.AVG functions have the same 3 arguments in their syntax.
RANK Function Order
In the RANK function, the 3rd argument (order), is optional. The order argument tells Excel whether to rank the list in ascending or descending order.
Descending Order
If you use a zero as the setting for order, or if you don't use the 3rd argument, the rank is set in descending order.
- The largest number gets a rank of 1
- The 5th largest score gets a rank of 5.
Ascending Order
If you use a 1 as the setting for order, or if you enter any number except zero as the 3rd argument, the rank is set in ascending order.
- The smallest number gets a rank of 1
- The 5th smallest number gets a rank of 5.
RANK IF Formula
There isn't a RANKIF function in Excel, but you can use the COUNTIFS function to calculate a number's rank based on specific criteria.
In the example below, the COUNTIFS formula in column D ranks each day's sales, compared to other days in the same week.
- Week 23: June 4th has the week's highest sales, and gets rank 1
- Week 24: June 10th has the week's highest sales, and gets rank 1
How the Formula Works
Here's the COUNTIFS sales ranking formula in cell D2:
- =COUNTIFS([Wk], [@Wk], [Sales], ">"&[@Sales])+1
And here's how the formula calculates its rankings:
- First, the formula checks the Wk column, for other sales with the same week number:
- =COUNTIFS([Wk], [@Wk]
- Next, it finds Sales amounts that are larger than the current row's sales amount.
- [Sales],">"&[@Sales])
- The formula counts all the rows that meet those 2 criteria
- Then,it add 1 to that number, to get the ranking. +1
For example, for June 11th, there are two larger numbers in week 24, so its rank is 3 (2+1)
- Tip: Learn more about the COUNTIFS function, and see other examples of what it can do!
Get the Sample File
Get the zipped sample Excel RANK Function file. The file is in xlsx format, and does not contain macros.
Down the Rabbit Hole 🐰
In this section, you'll find related Excel tips, shortcuts, skills, and details. These things are nice to know, but not essential, so you can skip this section, and get back to work. Or dive in, for some extra Excel fun and knowledge!
-- Sort Order - Worksheet Cell
Sort Order - Worksheet Cell
Instead of typing the order argument number into a RANK formula, you can use a cell reference, to create a flexible formula.
For example, in the screenshot below, I typed a 1 in cell E1, and linked to cell E1 in the formula, for the order argument.
| ▶ |
Note: Be sure to use an absolute reference ($E$1), if the formula will be copied down to other rows. If you use a relative reference (E1), the reference will change in each row. |
=RANK(B2,$B$2:$B$6,$E$1)
By linking to a cell, you can quickly see different results, without changing the formula. Type a zero in cell E1, or delete the number, and the rank will change to Descending order.
Sort Order - Check Box
Another way to input the sort order setting, is to use a check box on the worksheet.
- In the screen shot below, the check box in cell E1 is linked to cell E3.
- The box is checked, so cell E3 shows TRUE
.In cell C2, the RANK formula refers to cell E3, to get the sort order setting.
- =RANK(B2,$B$2:$B$6,$E$3)
This example is in the RANK function sample file, on the OrderCheck worksheet. For instructions on how to add a check box, and use it in a formula, see the Use Check Box Result in Excel Formula page.
More Examples of Flexible Formulas
For more examples of flexible formulas, see
- how to use drop downs for the CONVERT function units
- how to choose options for SUBTOTAL and AGGREGATE functions.
Get Monthly Excel Tips!
Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.
Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!
Related Tutorials
Last updated: May 20, 2025 7:42 PM