Excel RANK Function to Compare Numbers in a List

Excel RANK Function to Compare Numbers in a List

October 25, 2019 100 By Stanley Isaacs


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.