How to use the Excel RANK function (Excel 2013)
Articles Blog

How to use the Excel RANK function (Excel 2013)

August 11, 2019


This is Stephanie from StatisticsHowTo.com,
and in this video I will show you how to use the Rank function in Excel 2013.
I have a list of students and their grade point averages.
I want to find out what their class ranks are.
Click an empty cell right next to the first data point.
Type=rank and you are given three options in Excel 2013.
The very last function, the Rank function is now obsolete, that is what was previously
used in Excel 2007 and earlier. So if you want your work sheet to be compatible
with versions of Excel 2007 or earlier then you need to use the rank function.
The equivalent rank function in Excel 2013 is the rank EQ, so I double clicked on that.
And it is giving me the inputs, number, ref and order.
Well if you are not sure what those mean, click the insert function button on the toolbar.
And it gives you a better description of what that is exactly.
Number is the numbers for which I want to find the rank that will be my first score.
Ref is the reference toour list of numbers. I am going to select all of my numbers.
And the order, the descending order is zero, any non-zero value would sort in ascendiing
order. I am just going to go ahead and sort in descending
order. So I put in zero, and OK.
And that first score is ranking at 2. And I am going to drag the formula down.
And right away I can see there is some kind of problem with my formula because I have
got several ranking at one. So if you are going to take a look at my last
cell. I see that Excel has copied the formula, but
it is going down ward with this center part of formula.
I don’t want it compare to the values of B11 to B20 because it is nothing in there.
So I am going to go back up to my first cell. And I am going to lock this center part
of the formula. Click on one of those cell locations.
Hit F4 and it locks the formula. Hit F4 again to lock that second part of the
formula, and now I can drag the formula down. And there are my correct rankings.
The second rank function, rank average. That is something slightly different, still
has the same inputs. This time I am going to remember to lock my formula
and drag it down. If you notice we have got two students who
scored the same, 72.7. With rank EQ it assigns an equal rank to
both students, and that rank is 6. It skips 7 and goes up to the 8th rank here
with Tray. We would rank average, it takes the average
of the rank not the average of the score but the average of the rank
Well they are actually scored 6 and 7, there average is 6.5 so the rank here is 6.5.
Which formula you used depends on what kind of results you want from your data.
The second way to find the rank is on the data analysis tool.
Click data. Click data analysis.
If you do not see data analysis, install the data analysis toolpak.
Choose rank and percentile, click OK. My input range, I am going to select my entire
list of scores. They are grouped by columns, and I did not
include the label in the first row. I selected outside of that.
My output range, I want it to go in this next column here, click OK and here are the result
of the data analysis. The rank is in the third column here, it matches
the results given from the rank EQ function. In the last column are the percentiles and
I will cover those in the next video how to use the Excel percentile function.
Visit us at StatisticsHowTo.com for more Excel and statistics tips.
Thanks for stopping by.

Only registered users can comment.

  1. Thanks! This helped me on my homework. I was stuck because I was getting the same issue of multiples of the same rankings w/ different values as you explained at 1:53.  Is this unique to version 2013?

  2. Thanks, but one problem is that, if two scores are same then two ranks are same but next rank is not shown, for example scores (72.7, 72.7) rank (6, 6) but rank 7 is not shown. When i apply this formula on result sheet for positions the i face problem, please help.

  3. Hi Good Morning

    Is there any method to get the ranks as follow:

    34 to 1
    32 to 2
    32 to 2 (not 3)
    30 to 3 (not 4)
    28 to 4

    We created a spreadsheet for a competition. What we need is when the score is same we need the same rank (prize) but no skipping in the rank as well.

    Thank you in advance

  4. What if my numbers are in horizontal ,
    like :
    A B C
    11 21 31

    and I want to rank in a normal vertical way
    ranking normally like
    A 3
    B 2
    C 1

    Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *