Rank Formula in Excel
 27 Comment
The Rank Formula that is built into Excel can be a very useful way of speeding up the ranking of several items. Let’s take a look at the formula and see how this can help us out:
=rank(number, ref, order)
where number is the number whose rank you want to find; ref is a reference to the range of numbers you want to rank against (nonnumeric values in the range are ignored), and order refers to the way you want to ranking to appear – 0 or blank for descending and a nonzero number for ascending.
[ad#inpostad]
EXAMPLES
Example 1: =rank(B2,B2:B7) will return 2. Since we left the order reference blank it ranked in descending order and 97% is the second highest score in the list.
Example 2: =rank(B3,B2:B7,1) will return 1. Since this time we filled in order with a nonzero number it returned in ascending order and 67% was the lowest score.
Example 3: In this example, please refer to the image below of the updated spreadsheet. To obtain this result, all we had to do was take the formula in example 1 and lock some values (=rank(B2, $B$2:$B$7). After you enter this in cell C2, you just need to pull the formula down and it will fill the rest in correctly.
Further Applications
There are many other circumstances where you can use the Rank Formula in Excel. One example that I use it for all the time is to create percentiles (There is actually a percentile function and a percentrank function, which I’ll explore more soon).
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
More Excel Tips
27 Comments on this post
Trackbacks

Deepak kesarwani said:
It’s exiciting…..i want to learn more….
July 24th, 2009 at 5:41 am 
Eng Fook Kang said:
It’s a great hints and help, epecially the example provide.
August 27th, 2009 at 7:38 am 
Automobile India said:
I want to create a formula in excel that ranks revenue less than or equal to $50,000 a 1, anything from $50,001 – $100,000 a 2 and so on up to a ranking of 5. I think I’m supposed to use the IF function, but I’m not sure. Any ideas?
October 22nd, 2009 at 5:47 am 
Automobile India said:
How do I get the rank function to rank based on how many data points are available? And make this flexible enough so that no matter how many data points I enter it can still work?
November 1st, 2009 at 9:56 pm 
m.kamatchi said:
using rank formula in Students Total and reslut Pass or fail
December 16th, 2009 at 5:51 am 
Ranjeet said:
its an amazing site
February 10th, 2010 at 4:24 am 
Critter said:
I am trying to rank who has had the most opportunities to work overtime, have it available to the rest of the team so they can quickly and easily see who would be the next available person to who judging by who has worked the least amount. Meaning the person with the highest would be ranked number one and if there were not any ties (of which this is not a concern) all the way to 60 because there are presently 60 members on the team. Is this possible?
March 1st, 2010 at 6:50 am 
Mujumdar Madhav said:
I want to know how to get sum of numbers in Column C if text in Column A is filtered
March 31st, 2010 at 3:00 am 
Karthik said:
Hai my problem is excel sheet in rank
general rank formual is ok
but two columns rank one column is total another coloumn is section
sectino wise and total wise ranikg procedure plz
April 19th, 2010 at 5:23 am 
sunny said:
Hai I have excel file with 10 colums. These are track of letters. each letter as an N number like N102 N103 etc.. we have the letter scanned and stored in another folder (data) by the same N number.
Now I want to create a macro, which when executed should check of the N number from excel sheet and track the same N number in data folder and create a link to the subject.
Operating system – XP Ms Office 2007.
May 11th, 2010 at 8:24 am 
sudhakar bandari said:
its an amazing site , but i want base excel formulas
August 7th, 2010 at 1:41 am 
sunil said:
super , thank you.
i have problem with date , month and year [ ascending order ].
when , i tried, that time only dates wise changed but months and year not chnaged.
i want change date [ ascending order ] , month [ ascending order ] , year [ ascending order ]ex : 1 – 01 – 2008 to 15 – 08 – 2010. with names also change.solve this problem.
August 15th, 2010 at 9:04 am 
phunsum said:
i have a problem with the ranking.
April 5th, 2011 at 5:44 am 
Nelson Bostrom said:
For some reason it is returning #N/A, rather than the rank when there are identical numbers (such as 2 75%s above). I tried making sure the format of the cells being used in the formula are all numbers, but that doesn’t help.
April 5th, 2011 at 4:47 pm 
john said:
Can you provide any more detail? I’m not sure why it isn’t working off the top of my head.
April 7th, 2011 at 10:51 am 
vikash said:
i want to make a mark sheet programme in excel but i don’t know about the function of division (first,second,third).
April 14th, 2011 at 3:26 am 
gopalakrishnarao1 said:
I have tried ranking, suppose if the numbers are repetitive then How to assign the ranking.
May 24th, 2011 at 11:50 am 
darwin sh damanik said:
Thank’s for this post helpful, but I have a problem : how to find the average of two or more of the same rank? Example below :
Score Rank
45 7
54 5
27 8
55 3
49 6
55 3
66 1
61 2February 28th, 2012 at 3:51 am 
Thomas Aloyce said:
its cool site for excel learners
September 20th, 2012 at 6:46 am 
Fidelis said:
Can one get discontinuous range from a range with the original relative addresses
October 8th, 2012 at 9:23 am 
thirukganeshan said:
thanks your great help to find doubt
December 21st, 2012 at 12:19 pm 
Param said:
Good work
January 16th, 2013 at 1:27 pm 
Sarathy said:
Thanks and Very good Concept.its very useful for students
August 7th, 2014 at 8:03 am 
Alemu Sime said:
It is a very helpful site. thank you! I have a question. How can I rank selected numbers in MSexcel 2007/2010? For example; if I have numbers below
34 in cell A1 55 in cell A6 27 in cell A11
67 in cell A2 61 in cell A7 63 in cell A12
89 in cell A3 47 in cell A8 57 in cell A13
45 in cell A4 77 in cell A9 23 in cell A14
53 in cell A5 91 in cell A10 42 in cell A15
Then, how can I rank only numbers in cells A1, A3, A5, A7, A9, A11, A13, A14 &A15? Thank You!March 4th, 2015 at 9:42 pm
[...] Rank Formula in Excel [...]
[...] RANK function: http://excelhints.com/2007/06/15/rankformulainexcel/ [...]
[...] Or maybe something like this… Rank Formula in Excel [...]