Jun 15 2007

# Rank Formula in Excel

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 (non-numeric 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 non-zero number for ascending.

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 non-zero 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

Did you find this post helpful? Try signing up for our free daily tips.

### 29 Comments on this post

1. Top Posts in 2008 | Excel Hints wrote:

[...] Rank Formula in Excel [...]

January 1st, 2009 at 4:52 pm
2. EDU 3105 – Teknologi dalam P&P - BORANG MARKAH (MARKSHEET) wrote:
August 7th, 2011 at 1:14 pm
3. Top 10% of Sorted Data wrote:

[...] Or maybe something like this… Rank Formula in Excel [...]

March 5th, 2013 at 12:03 pm
1. Deepak kesarwani said:

July 24th, 2009 at 5:41 am
2. Eng Fook Kang said:

It’s a great hints and help, epecially the example provide.

August 27th, 2009 at 7:38 am
3. 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
4. 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
5. m.kamatchi said:

using rank formula in Students Total and reslut Pass or fail

December 16th, 2009 at 5:51 am
6. Ranjeet said:

its an amazing site

February 10th, 2010 at 4:24 am
7. 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

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
9. 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
10. 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
11. sudhakar bandari said:

its an amazing site , but i want base excel formulas

August 7th, 2010 at 1:41 am
12. 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
13. phunsum said:

i have a problem with the ranking.

April 5th, 2011 at 5:44 am
14. 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
15. 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
16. 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
17. 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
18. 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 2

February 28th, 2012 at 3:51 am
19. Thomas Aloyce said:

its cool site for excel learners

September 20th, 2012 at 6:46 am
20. Fidelis said:

Can one get discontinuous range from a range with the original relative addresses

October 8th, 2012 at 9:23 am
21. thirukganeshan said:

thanks your great help to find doubt

December 21st, 2012 at 12:19 pm
22. Param said:

Good work

January 16th, 2013 at 1:27 pm
23. Sarathy said:

Thanks and Very good Concept.its very useful for students

August 7th, 2014 at 8:03 am
24. Alemu Sime said:

It is a very helpful site. thank you! I have a question. How can I rank selected numbers in MS-excel 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
25. Pravesh said:

Sir i please tell me in excel formula,
some student in a school get 10,20,30,40, marks. if 10 marks student return value is 5, >10,20&<=30, return value 25,

August 12th, 2015 at 12:45 am
26. Jeetendra said:

all student is 20 and if 3 and 4 number student same rank 3 than 5th number student rank is 4, 6th number student rank is 5
and after 7th & 8th student rank is 6 than 9th number student rank is 7, 10th number student rank is 8