How to rank on Excel with RANK function

When working with the statistics table on Excel need to classify rankings, the survey grows, we need to use the RANK function. RANK function will help you rank data to know which data is high and low. Users using RANK function can rank in order from high to low or from low to high. Using RANK function is also relatively easy as other basic Excel functions. The following article will guide you how to use RANK function on Excel.

1. How to use the SUM function to calculate totals in Excel
2. How to combine Vlookup function with If function in Excel
3. How to automatically display names when entering code in Excel

Instructions for using RANK function on Excel

We will apply to the score table below with the requirement to rank the score in with the RANK function.

The function RANK has the formula = RANK (number, ref, [order]) .

1. Number: The value to rank in the block.
2. Ref: List, data block to be sorted.
3. Order: Sort order, sort type (ascending or descending). In the case of order = 0 (or without this parameter), it will be calculated from high to low. And order = 1 will rank from low to high.

How to rank on Excel with RANK function Picture 1

1. Sort from high to low

Step 1:

In the first cell in the Ranking column, enter the formula = RANK (B3, $ B $ 3: $ B $ 7.0) .

Inside:

1. B3 is the number to look for in the rankings as the first student's score.
2. $ B $ 3: $ B $ 7 is the number list that is the Score column.
3. 0 is sorted in ascending order (the highest score will rank 1).

How to rank on Excel with RANK function Picture 2

Step 2:

After pressing Enter they will have a result of 3, meaning that Tran Ha An is ranked 3rd with 20.1 points. In the first result box in the Ranking column, you scroll down to the remaining cells and get the ranking results for the others.

In case of overlapping numbers, there will be equal rankings and affect rankings later. For example, the number 22 will appear twice, it will rank 2. And the number 20.1 will rank 4th always and there is no 3rd rank.

How to rank on Excel with RANK function Picture 3

2. Sort from low to high

In this case it needs to be reversed, meaning that those with a high score will rank at the bottom of the list. Here we will replace 0 to 1.

We will also enter the formula in the first rank cell = RANK (B3, $ B $ 3: $ B $ 7.1) and then press Enter.

How to rank on Excel with RANK function Picture 4

The result will also be as shown below.

How to rank on Excel with RANK function Picture 5

So you know how to use RANK function in Excel to rank data, from high to low or from low to high. Using RANK function will save more time to find the highest, or lowest, data than manual number detection.

Video tutorial using RANK function on Excel

See more:

1. 4 basic steps to color alternating lines in Microsoft Excel
2. Summary of expensive shortcuts in Microsoft Excel
3. How to keep Excel and Excel columns fixed?

I wish you all success!