Formula to find Rank of each student


The B column shows each student’s obtained marks out of 100.

In C2, write a simple formula
=RANK (B2, $B$2:$B$11, 0)
=RANK (number, Ref, [order])

  • B2 (Number) -> Give reference of the cell where that student’s marks are mentioned.
  • $B$2:$B$11 (Reference) -> Give reference to the range of the column till all the marks are mentioned. In this case, till B11. Why the Dollar signs?? Simply to fix the reference. This is known as Absolute Referencing, and is used when after writing 1 formula, you want to copy the formula to give you output of lot of data.
  • 0 (Order) -> Find the rank in descending order. Student with the highest marks will be ranked 1. If you put 1 instead of 0 in the formula, you will have 1 in output for the student with the lowest marks.


After you get the output in C2, simply copy the formula from C3 till C11. All your outputs will be achieved in 1 shot.


Tags: Rank, Student’s rank, calculate rank, find rank, rank function, rank excel, rank excel function.