HomeApplicationOffice information

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!

4 ★ | 1 Vote
excel rank

You should read it

May be interested

  • How to use Hlookup function on ExcelHow to use Hlookup function on Excel
    hlookup function basically also has the function syntax and features like vlookup function, which is to help users find data in excel table, with the conditions or given information. here is the guide for using the hlookup function in detail.
  • How to use the SUM function to calculate totals in ExcelHow to use the SUM function to calculate totals in Excel
    sum is a popular and very useful excel function, and is also a basic arithmetic function. as its name suggests, the sum function is used to calculate totals in excel. and the parameters can be single parameters or ranges of cells. in this article, tipsmake.com will guide you to use the sum function to calculate the sum in excel, the common errors when calculating sum by sum and how to fix it.
  • How to use the LEN function in ExcelHow to use the LEN function in Excel
    the len function in excel is used to measure the length of a certain string of characters, including spaces and correctly return the total character for the user.
  • How to use COUNTIF function on ExcelHow to use COUNTIF function on Excel
    the countif function on excel is the cell count function to satisfy the given condition, often used in statistics tables.
  • How to use the MOD function and QUOTIENT function in ExcelHow to use the MOD function and QUOTIENT function in Excel
    injury in excel has many ways of doing it, can be used manually or using the calculation function.
  • 8 little-known Excel functions that can save you a lot of work8 little-known Excel functions that can save you a lot of work
    even seasoned excel users often find themselves stuck performing tasks manually that could be automated with a few clever functions.
  • How to use MID functions to get strings in ExcelHow to use MID functions to get strings in Excel
    mid function in excel is a function that takes the middle character string corresponding to the value that the user requires to perform.
  • How to use Excel's VALUE functionHow to use Excel's VALUE function
    excel's value function will convert a string to a digital form, which can be combined with other functions such as left function, right function, mid function.
  • How to use the WRAPROWS function in ExcelHow to use the WRAPROWS function in Excel
    having trouble with handling large data sets in excel? then learn how to use the wraprows function to split them into more manageable rows.
  • How to use the IFS function in Excel 2016How to use the IFS function in Excel 2016
    the ifs function in excel is a logical function introduced in excel 2016. this function is a function that replaces the nested if function and is much easier to use. the ifs function checks one or more conditions and returns a value that meets the first true condition.

Office information