We often sort lists in Excel to make it easier to compare numbers, for example an inventory item list may get sorted by sales instead of by item, or expense accounts could be re-sorted by balance instead of account number. But what do you do when you need a sales report sorted by item and you still want to quickly see which items had the highest or lowest sales? Use Excel’s Rank function.
The format for the Rank function is fairly simple.
RANK(number,ref,[order])
- Number is the number you want to compare to a list or rank. You could actually enter a number here but usually you would enter a reference to a cell, such as C2, that contains the number to be compared.
- Ref is the cell reference for the list you want “Number” compared to.
- [order] tells Excel how to determine the best and worst rankings. It’s shown in brackets because it’s optional. If you enter 1 for the order, the lowest number in the list will be ranked number 1. If you leave it out or enter 0, the highest number in the list will be ranked number 1.
In this example we will use column D to hold a ranking of the sales amounts from column C. The formula in cell D2 tells Excel to rank the value in cell C2 based in the range of values from C2 through C9. Notice that in the formula, C2 (Number) has no dollar signs and therefore is a relative reference. The dollar signs in $C$2:$C$9 (Ref) make it an absolute reference. That means that the row number “C2” will automatically be adjust as it is copied down, but “$C$2:$C$9” will stay the same.
If there are no other numbers below this data, then another option is to use the entire column C as the Ref instead of the specific cells. That would look like this =RANK(C2,C:C).
So entering the formula like this:
| A | B | C | D | |
| 1 | Item ID | Item Description | Sales($) | Rank |
| 2 | AVRY-10050-LG-CSL | Prefabricated Birdhouse | 239.97 | =RANK(C2,$C$2:$C$9) |
| 3 | AVRY-10050-LG-EFL | Prefabricated Birdhouse | 239.97 | =RANK(C3,$C$2:$C$9) |
| 4 | AVRY-10050-LG-HTL | Prefabricated Birdhouse | 479.94 | =RANK(C4,$C$2:$C$9) |
| 5 | AVRY-10050-SM-EFL | Prefabricated Birdhouse | 359.94 | =RANK(C5,$C$2:$C$9) |
| 6 | AVRY-10050-SM-HTL | Prefabricated Birdhouse | 359.95 | =RANK(C6,$C$2:$C$9) |
| 7 | AVRY-10050-SM-PYR | Prefabricated Birdhouse | 179.97 | =RANK(C7,$C$2:$C$9) |
| 8 | AVRY-10100 | Bird House Kit | 389.97 | =RANK(C8,$C$2:$C$9) |
| 9 | AVRY-10110 | Bird House-Pole 14 Ft. | 1,499.70 | =RANK(C9,$C$2:$C$9) |
Will give you this:
| A | B | C | D | |
| 1 | Item ID | Item Description | Sales($) | Rank |
| 2 | AVRY-10050-LG-CSL | Prefabricated Birdhouse | 239.97 | 6 |
| 3 | AVRY-10050-LG-EFL | Prefabricated Birdhouse | 239.97 | 6 |
| 4 | AVRY-10050-LG-HTL | Prefabricated Birdhouse | 479.94 | 2 |
| 5 | AVRY-10050-SM-EFL | Prefabricated Birdhouse | 359.94 | 4 |
| 6 | AVRY-10050-SM-HTL | Prefabricated Birdhouse | 359.93 | 3 |
| 7 | AVRY-10050-SM-PYR | Prefabricated Birdhouse | 179.97 | 8 |
| 8 | AVRY-10100 | Bird House Kit | 389.97 | 3 |
| 9 | AVRY-10110 | Bird House-Pole 14 Ft. | 1,499.70 | 1 |
Rows 2 and 3 have the same sales amount so they have the same rank, 6 in this case, and nothing is ranked 7.
The Rank function has actually been renamed Rank.EQ in Excel 2010, but for compatibility with older versions, you can still use Rank. I used Rank in my example since it works with all versions. Excel 2010 also offers Rank.AVG which averages the rank of items with the same value. In the example above, the first two items would both have a rank of 6.5 instead of 6 if we had used Rank.AVG.
