How To Rank Data In Google Sheets With The RANK Function

How To Rank Data In Google Sheets With The RANK Function
Reading Time: 4 minutes

What is the RANK Function in Google Sheets?

The RANK function in Google Sheets allows you to rank a list of numbers in a particular order, i.e. ascending or descending.

Example of the RANK function in Google Sheets
Figure 1: Example of the RANK function in Google Sheets

What is the syntax of the RANK function in Google Sheets?

RANK(value, data, [is_ascending])

  • value – The value whose rank will be determined.
    • If data does not contain the value in any cell or element, RANK will return the #N/A error.
  • data – The array or range containing the dataset to consider.
  • is_ascending – [ OPTIONAL – 0 by default ] Whether to consider the values in data in descending or ascending order.
    • If this is 0, the greatest value in data will have rank 1; if this is 1, the least value in data will have rank 1.

Important things to keep in mind before using the RANK function in Google Sheets

  • The values placed in the RANK function should be numeric values.
  • The value to be ranked should be in the data range, otherwise, Google Sheets will throw an #N/A error.
  • If data does not contain the value in any cell or element, RANK will return the #N/A error.
  • Reference your cells if you want to use the same cell range for multiple cell values.

How to use the RANK function in Google Sheets?

Step 1: Create a Dataset

  • Open Google Sheets.
  • Create a dataset of interest

Step 2: Use the RANK Function in Google Sheets

  • Take a cell outside the data range.
  • Use the RANK function in Google Sheets as per requirement.
  • It is important to keep the data range fixed and referenced. If you don’t keep the range fixed, the range will be dynamic and the ranking will be affected. An example of this error is covered later in this article.
  • Keep in mind that the value should be in the column range provided.

Example of the RANK Function in Google Sheets

Let’s say you have a dataset of marks scored by students in a mathematics class. The dataset looks like this:

Figure 2: Dataset of interest

Now, you want to rank them based on their scores. Use the RANK Formula in Google Sheets as shown below:

=RANK(B2,$B$2:$B$6)

  • Here, the first parameter is cell B2. 
  • The second parameter is the cell range. If you observe closely, the cell range is referenced. This makes the cell range fixed.
  • You can now drag down the selection to C6 and see the ranks of students, as shown below:
Example of RANK function in Google Sheets with fixed cell range
Figure 3: Example of RANK function in Google Sheets with fixed cell range

However, if you don’t reference your cells, the output is completely different.

Let’s say you use the following RANK formula in Google Sheets:

=RANK(B2,B2:B6)

  • Here, the cell range isn’t fixed. So if you drop down the selection, the cell range is changed for every cell, thereby giving undesired outputs.
  • For cell B3, the cell range is B3:B7, for cell B4, the cell range is B4:B8 and so on.
  • The output in this case is:
Example of RANK function in Google Sheets with dynamic cell range
Figure 4: Example of RANK function in Google Sheets with dynamic cell range

Using the RANK function in Google Sheets to rank in Ascending Order

Let’s say you have a dataset that looks like this:

Dataset of interest
Figure 5: Dataset of interest

You want to find the rank of anyone who has scored 68, with Rank 1 assigned to one with the lowest marks. Use:

=RANK(68,$B$2:$B$10,1)

  • Here, 68 is the value you want to rank.
  • The next parameter is selecting the cell range.
  • The third parameter is setting the ranking in ascending order. If you want to rank in descending order, change the third parameter to 0.
  • The output is that the rank is 5th.

Conclusion

The RANK function in Google Sheets allows you to rank cells in ascending or descending order. The value should be in the cell range provided else Google Sheets will throw an error. If you want to know more about the RANK function in Google Sheets, go through the official documentation here.

See Also

Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!

Filter Data by Color in Google Sheets: In this article, we would have a look at how to Filter Data by Color in Google Sheets.

How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.

Google Sheets: How to Use Pivot Tables: Learning how to create and use Pivot Tables for analysing data in Google Sheets.

Conditional Formatting in Google Sheets: Get started with Conditional Formatting in Google Sheets

Complete Guide on the REGEXEXTRACT Function in Google Sheets for 2022: Learn how to use the REGEXEXTRACT function in Google Sheets and its variations.

How to View Edit History of Cells in Google Sheets | Easy 2 Minute Guide: Learn how to view the edit history of cells in Google Sheets. Learn to spot mistakes or past changes.

One response to “How To Rank Data In Google Sheets With The RANK Function”

Leave a Reply