Home // Video Tutorials // 3 - 6 (Intermediate) // 5 // Rank Cells in Excel

Rank Cells in Excel

How to rank cells in excel using the RANK function.

Rank Cells in Excel

The RANK function allows you to rank cells or groups of cells in either ascending or descending order. In this segment you will learn how to rank cells in Excel by finding and activating the function using the Function Wizard, and how to use the RANK function on a cell to either generate a ranking in its ascending order, or descending order

The RANK function can only be used to rank numeric cells; it cannot be used to rank alphabetical cells unless you use another function in Excel. What the RANK function is very useful for, is determining for example who is the best and worst in a situation, and their rankings in between.

So for example we have a list of Sales People here, we have how many Units they’ve sold of a product, and as result of the average price they’ve achieved there’s a Total Sales value. And what we’d like to do is see who is the best, worst, and their rankings in between in terms of Units Sold, and likewise the best, the worst, and the rankings in between for the Total Sales value. To do this you need to do the following;

click in the cell where you want the information displayed,
click on the Function Wizard
now you need to find the RANK function, so what you can do is either go to its subcategory which is Statistical,
click inside the box
push R
and RANK will come up
or else you can go to All the functions
again click in the box
push R
and find the RANK feature
click Ok,
Now it asks you for a bit of details to help rank these numbers,

the first one is the Number and it wants to know what number is being ranked, so in this case we want to rank Adrian’s Units Sold so you click on that
the next criteria is the Reference and what its asking is, this number you’ve given me what must I compare it against, and in this case I want to highlight all the Units Sold
now because the chances are very good you are going to copy and paste this formula across, I highly recommend you anchor this by putting the dollar signs in. And you can push F4 to do it quickly,
the last criteria is in what order must it rank it in, is it a descending or ascending order, and as it shows you down here if you want it in descending order you type a 0 or you can omit it
and if you want an ascending order you can put any none 0 value for example 1
in this case we want to get it in descending order; we want to see whose first and whose last, who sold the most. So we type a 0 in there
and you push Ok,
What you’ll see is it tells you immediately that Adrian’s 78 Units Sold is ranked 3rd which makes sense in terms of Jane has got 89, and Trevor had 81.

Now you can quite easily copy
and paste this all the way down
and what you quickly get is the rankings of the various Sales People
So you’ll see quickly that Jane is number 1 with 89
2nd is Trevor on 81
and 3rd is Adrian on 78,
what is interesting is you’ll see Mary and Cary are both on 67 and the way Excel handles this is by putting them both at 4th
and you’ll see that then there is no 5th place it goes immediately to 6th and then there’s 7th
And that gives you a good quick ranking of the Units Sold.

Now we can do the same to RANK the Sales People by Total Sales values, so again

you click in the cell you want
I recommend you use the Function Wizard
you find the RANK feature,
this time the Number you comparing is the Total Sales values so you point to that
you want to compare it against the other Total Sales values so you just highlight that section
again I recommend you anchor it by pushing F4 or putting the dollar signs in
the order again is going to be descending order, so we going to type in a 0
you click Ok
and you’ll see that Adrian again is 3rd in Total Sales value
you can copy it down
And now you have quickly and automatically performed rankings, which give some interesting statistics. For example Antoinette although she was the worst in terms of Unit Sold is the best in Sales value because her average sales price was the best.

Now for example what’s nice about the RANK feature is let’s say we were actually wrong

and this was actually should have been 100
when you click enter
automatically the rankings are redone
Now Antoinette is the top in Units Sold and everyone else has been adjusted down.