Articles, Blog

Excel RANK Function to Compare Numbers in a List

January 12, 2020


If you have test results, or another list of numbers in Excel, you could sort them to see
which scores are at the top, but if you want to leave the
list in its current order, you can use the RANK function to show the order of these scores. In cell C2, I’m going to create a RANK formula, and then copy it down to
the bottom of this list, and we’ll see which position
each student’s score is in. In cell C2, I’ll start with an=, and type RANK, R-A-N-K, and an (. The first argument is the number. I have to click on the
cell that has the number I want to find the rank for. In this row, it’s B2. I’ll type a comma. And then the reference, so what am I comparing it to? I want to compare it to
all the other scores. I’ll start at B2 and drag down to B11. Now I’m going to be
copying this formula down to all the other rows. As I copy it down, I want it to always change
the cell that it’s checking, but I don’t want it to change the cells that it’s comparing to. It should always be B2 to B11. While I’m at the end of this
reference that I just typed, on the keyboard I’m going
to press the F4 key, and that makes this an absolute reference so this won’t change as
I copy the formula down. And I’ll finish with a ) and press Enter. The score 46 is ranked 1, and I’ll click on this cell, and drag the fill handle down. We can see all the other ranks. In this cell, it’s now looking at B7, but it’s locked into that B2 to B11 range to check the rank.

You Might Also Like

100 Comments

  • Reply Stevo November 18, 2014 at 6:04 am

    That was very easy to understand, and very helpful.  Thank you kindly.

  • Reply BS November 26, 2014 at 10:59 pm

    great… thankyou

  • Reply R. Chandrasekaran January 2, 2015 at 4:11 pm

    super. plz keep on uploads .heartly i thank u.

  • Reply Simon White January 10, 2015 at 10:25 am

    thank you for the video, you help me so much,>W<

  • Reply Ronan Zhong February 3, 2015 at 8:31 pm

    great

  • Reply Yam Soti February 12, 2015 at 6:07 am

    hey Contextures Inc. where is 5th rank number? could you tell me please how to ranked  properly ?

  • Reply Wendell Foster February 20, 2015 at 10:37 pm

    Very useful. Thanks for posting.

  • Reply Frappuccino April 22, 2015 at 10:08 pm

    Very helpful, super easy to understand, Thanks!

  • Reply brittini17 April 27, 2015 at 11:46 pm

    Thanks for the extremely helpful video!

  • Reply Jkathlete June 2, 2015 at 5:19 pm

    Thank you so much

  • Reply yo goddamn July 30, 2015 at 1:17 pm

    thank you ma,am 🙂 i was trying but i guess i was missing to hit f4 and that's why i get wrong values again n again

  • Reply Eugeniu Manoli August 4, 2015 at 1:53 pm

    Sorry. but it has some lacks. The 5th place(rank) is omitted. And that problem appears every time when we have repetitions of rank. For example: if you had 3 times rank 4 the next rank would be 7 (5 and 6 will be skipped).

  • Reply Le Berino October 14, 2015 at 11:57 pm

    anyone who knew the equivalent of the f4 key in libreoffice calc? We were instructed to only use calc

  • Reply David Gill November 3, 2015 at 11:32 am

    Great video, exactly what I wanted, so easy to understand. Well done and thank you.

  • Reply Isa Alsaqer January 20, 2016 at 7:02 am

    perfection.

  • Reply shrirang joshi February 4, 2016 at 4:23 pm

    I AM LOOKING FOR SWIMMER TIMING FORMAT SO FOR EXAMPLE IF TIME 01:20.15 & 01:15.20 SO I WANT 1 ST PLACE FOR 01:15.20.
    IF ANY ONE KNOW THIS PLEASE TELL ME HOW TO DO THIS?

  • Reply StupidEarthlings February 5, 2016 at 1:56 pm

    AWESOME. Thanks.:)

  • Reply Ruthea Joseph February 16, 2016 at 10:41 pm

    thank you, this video was very helpful, Keep it up <3

  • Reply Ahmad Raza March 21, 2016 at 1:28 pm

    Thank You

  • Reply André Gomes April 16, 2016 at 11:21 pm

    Thank you

  • Reply Kevin Eason September 5, 2016 at 12:05 pm

    Thank you. Very simple and well explained.

  • Reply Usman Hafeez October 8, 2016 at 7:23 am

    Dear its good but not actually, where is 5th position

  • Reply Ida Guidry-Goffney October 8, 2016 at 2:41 pm

    I've been struggling with formulas for 3 days; I've purchased two books and spent time trying to follow them. Your simplistic but invaluable video is a blessing!!

  • Reply Muhammad Ishaq December 2, 2016 at 7:19 am

    Thank You So Much….

  • Reply John Joseph December 22, 2016 at 2:03 pm

    Thank you for this I did the formula and it worked. However when I dragged the formula down it gave me an error #N/A how do I fix the cells that have this but the formula in the bar looks correct?

  • Reply Julio Bobadilla January 10, 2017 at 5:00 am

    Thank you for the help. Very helpful explanation.

  • Reply SebUballe March 2, 2017 at 2:07 am

    Good video thanks!

  • Reply Peter Forint March 22, 2017 at 2:52 pm

    Thanks for teaching me both the RANK function and the Absolute Range Function F4 shortcut. The Absolute Range Function F4 shortcut was a totally unexpected bonus that will save me tons of time!

  • Reply Cathy toot May 21, 2017 at 1:06 am

    THANKS FOR DETAILED EXPLANATION, I FINALLY UNDERSTAND WHY I HAVE TO USE F4

  • Reply Nytrox Nikko May 24, 2017 at 6:03 pm

    Thanks a lot !

  • Reply oshan randika June 25, 2017 at 8:38 am

    Thank you for Upload this Video, Great Work Thanksssssssssssssssssssssssssssssssssssssssssss

  • Reply enragedpiston17 June 26, 2017 at 1:24 pm

    the "F4" key doesn't work for me im on windows 10 2016 exel how do i do it manualy

  • Reply Krishnaraju R August 6, 2017 at 1:35 pm

    How to identify the failure student? For exp if some students failed in one sub they should not be in the rank list.

  • Reply Kalle October 3, 2017 at 1:50 pm

    Set the video speed to 1.5 , you can thank me later!

  • Reply Cristian Xavier October 22, 2017 at 3:55 pm

    Thank you!

  • Reply Uday Kumar November 30, 2017 at 12:12 pm

    Thanks a lot

  • Reply Betty Stoecklin November 30, 2017 at 7:25 pm

    Extremely helpful! Thank you very much!

  • Reply ABHIJIT SAHA December 24, 2017 at 4:03 am

    G✿✿d

  • Reply Lav Dutt Bhargav December 26, 2017 at 4:55 pm

    It miss 5th rank in your video as well as in my PC

  • Reply Cassie Grether January 7, 2018 at 4:05 am

    Thank you so much! You are awesome!

  • Reply Tim O'Connell February 2, 2018 at 9:43 pm

    Thank you – very well done!

  • Reply Mourn Ny February 3, 2018 at 6:16 am

    how about to make in other?

  • Reply ephrem chernet February 7, 2018 at 7:49 am

    Thank u ✌️

  • Reply DoYouHaveAMinute? February 10, 2018 at 7:48 pm

    Thank you !

  • Reply Arpan Patel February 23, 2018 at 10:36 am

    Perfect explanations about that Dolore sign nd use thanks

  • Reply Lovely Nasirzai February 23, 2018 at 6:26 pm

    Thanks

  • Reply omprakash luitel February 24, 2018 at 4:57 pm

    >_<

  • Reply Marco Atzeni February 25, 2018 at 4:00 pm

    Very good explaination. Congratulations!

  • Reply Arpan Patel February 27, 2018 at 2:19 pm

    Hey can you tell me if i f no 30, 32, 20, 30, 40 if i arrange tham with RANK formula there is 40=1,32=2,30=3,30=3 than 20 is shift to Rank five. I want it remains 4. Can i ?? Please reply it really helps me

  • Reply Tahir Khan March 18, 2018 at 1:38 pm

    I like the way you explained it. Thanks!

  • Reply Andre Dominic Rubiano March 19, 2018 at 2:20 pm

    is there a unit for critical value? like, how is it reported in research papers?

  • Reply Khalid Shah April 26, 2018 at 6:52 pm

    Very nice video

  • Reply BostonBrew May 25, 2018 at 11:06 pm

    Thank you! I always forget how to do this!

  • Reply Jayati rajak May 31, 2018 at 4:45 am

    thanks for share this video.

  • Reply yusuph matonange mwandu May 31, 2018 at 11:44 am

    thank you it help me, but if you don't care can you send to me your E-mail address?

  • Reply Yisyaj Yaj June 4, 2018 at 12:03 am

    thanks

  • Reply Luke Donohue - SCUK June 16, 2018 at 1:13 pm

    How can you Rank so that the smallest score would be ranked in first place?

  • Reply Haseeb Haneef July 1, 2018 at 6:28 am

    Helpful video!
    Thanks😊

  • Reply Vipin Kumar September 24, 2018 at 5:09 pm

    Language smjh me na aati bus smjh jaya hoon vaise mai Very nice baby I like you voies

  • Reply MrFayezAlKhaldi October 1, 2018 at 6:21 pm

    great info thanks

  • Reply rangera raj October 4, 2018 at 8:20 am

    HELLO SIR, JAB JUM RANK NIKAL TE HA TAK AGAR SAME NUMBER AA JAYE TO EXA 11, 11 AGAR ENKI RANK MAAN LETE HA 4, 4 TO AGLI WALI YE RANK 5 KI JAGAH YE SIDHA 6 DIKHA RAHA HA KARAN BATANA SIR JI

  • Reply Gul Jaji October 6, 2018 at 6:07 am

    where is 5th score, (5th position?)

  • Reply J H October 9, 2018 at 4:00 pm

    Nice and clear, thank you.

  • Reply samikshya priyadarsini Sahu October 26, 2018 at 6:00 pm

    Thanks for the video

  • Reply Roshan November 1, 2018 at 7:25 am

    Thank you 🙂

  • Reply Ranjeet Garhwal November 9, 2018 at 5:21 am

    Isme rank 5 kha h

  • Reply Alpesh Prajapati November 14, 2018 at 4:55 am

    Thank u very very very much madam…😊

  • Reply DominateDepression December 5, 2018 at 10:55 pm

    How can I then have the table reorganize to have the 1st person that is doing the best at the top of the list and have the rank in descending order from there?

  • Reply J Sankar December 13, 2018 at 6:00 am

    It's very easy thank u mam

  • Reply Palestinian Patriot December 16, 2018 at 9:30 pm

    Thank you love

  • Reply Schanizan Shamsuddin December 27, 2018 at 1:58 am

    Thank you. Very clear explanation.

  • Reply Avnish Kumar December 27, 2018 at 11:26 pm

    But when I press enter in that process it says you have entered too few arguments, and as your comp tells the rankings mine one doesn't tell the rankings, but it says, you have entered too few arguments

  • Reply Syed Khasim December 30, 2018 at 3:11 pm

    millions of tanks

  • Reply MC OC February 8, 2019 at 1:49 pm

    Hi. Just subscribed to your channel. I have a question on ranking
    and formatting. I would like to format cells e.g. A1, C1, E1 and G1
    based on their ranks i.e from the lowest to the highest assigning colors
    based on what is there. Kindly assist. Thanks

  • Reply Syeda Areeba February 16, 2019 at 3:39 pm

    Thanku so much I hope u succeed in life and YouTube

  • Reply Farooq Taj February 17, 2019 at 10:02 am

    I have faced a problem use rank function….
    Problem:
    When I find my ranking for A1:A5
    A1.value =95
    A2.value =75
    A3.value =95
    A4.value =90
    A5.value =95
    My answer is
    (1,5,1,4,1)
    I want it ranking continue
    Like (1,3,1,2,1)
    How solve it?
    Thanks

  • Reply Richard Seto February 21, 2019 at 8:29 pm

    So helpful!

  • Reply Sebastián Largo February 26, 2019 at 10:36 pm

    I want to make a ranking from the first (highest score) to the last in a different list. In other words, I don't mind moving the names, I just want the positions. How do I do it? Can someone help me, please?

  • Reply Hassan Shahid February 27, 2019 at 12:29 pm

    thank u so much for making this video

  • Reply Somya Sharma March 4, 2019 at 7:22 am

    thanks a lot

  • Reply silent storm March 6, 2019 at 11:23 pm

    my problem is about equal numbers,there is 2 name for 4,when try to result of rank on an other function,large function give ıvy or joe,how can i know them,one is joe and other ivy

  • Reply Grumpy_Gaming March 12, 2019 at 7:56 pm

    Saved me so much time thankyou. Very well explained.

  • Reply Satyam Sarraf March 17, 2019 at 1:42 pm

    Not got clearly

  • Reply Mahmoud Abo Elnaga April 3, 2019 at 8:26 am

    That helped me … also knowing the function of pressing F4 I didn't know that and used to add it manually, thanks

  • Reply Bharat Kumar Gupta April 7, 2019 at 1:59 am

    Thanks

  • Reply Cool Sreejith April 30, 2019 at 5:45 pm

    Thank You!!

  • Reply Satendra Kumar May 2, 2019 at 11:00 am

    Thank u

  • Reply Shivam Bishnoi May 8, 2019 at 2:37 pm

    Where is rank 5

  • Reply krutik patel May 12, 2019 at 11:55 am

    4 number ka rank 2 bar he iske bad 5 number kyu nahi aya
    Yah problem ka solution nikal ke video banaye

  • Reply Made Putra July 6, 2019 at 8:38 pm

    so simple and help me a lot. thanks you

  • Reply Thearo KH July 24, 2019 at 2:05 pm

    I press F4 not change like you. please help me

  • Reply KawaiiPocky August 2, 2019 at 3:31 pm

    sooo helpful, thank you, thank you, thank you!

  • Reply vikarm sharma August 6, 2019 at 7:18 am

    5 vi rank kha h

  • Reply Ray Thomson August 7, 2019 at 2:57 pm

    This function is exactly what i was searching for to complete my lotto syndicate frequency table. You halped iron out some of the snags in my formula. Awesome thanks.

  • Reply Peter bogdanovich August 15, 2019 at 7:22 pm

    I love it that there are no frills in your videos. Right to the point without lengthy introductions. The perfect length (under 3 minutes) makes them excellent for quickly watch them and learn or refresh one's knowledge. Great job!

  • Reply Trafalgar Law August 21, 2019 at 11:55 am

    Thank you very much!!!😁😁😁👍

  • Reply Binod Bhatta September 13, 2019 at 1:21 am

    Thanks, it was useful.

  • Reply Differentvideosforyou September 29, 2019 at 12:54 pm

    Where is rank 5?

  • Reply Wayne Edmondson November 21, 2019 at 1:38 am

    HI Debra.. thanks for this quickie on RANK. Your videos are always useful and informative. Thumbs up!

  • Reply Jason Bigg November 26, 2019 at 10:43 pm

    Thank you

  • Leave a Reply