How to Use the RANK Function in Google Sheets

In this article, I will teach you how to use the RANK formula in Google Sheets. The RANK formula is handy when you want to rank each number within a range.

Using the RANK Formula in Google Sheets

To use the RANK formula in Google Sheets, follow these steps:

1. Type “=RANK(” or go to “Insert” → “Function” → “Statistical” → “RANK”.
2. Select a value for which you want to determine the rank.
3. Indicate a range that contains all the numbers you want to rank.
4. Define how to rank the numbers in ascending or descending order.

The generic syntax is as follows:

• Value: This is the value for which the rank is checked within the selected range.
• Data: This is the dataset that includes all the values you want to rank.
• [is_ascending]: You can enter 0 or 1. If you don’t enter any, the formula assumes 0. Alternatively, you can use “FALSE” and “TRUE” instead of 0 and 1, respectively.
  • 0 or “FALSE”: The function ranks the numbers in descending order. The largest number occupies the first rank.
  • 1 or “TRUE”: The formula ranks the values in ascending order. The smallest value occupies the first rank.

Let’s say you work in a business planning service and need to rank your clients in descending order based on the number of customer claims.

For example, the arguments in the formula in cell D3 are as follows:

• Value: C3
• Data: $C$3:$C$12
• [is_ascending]: FALSE

Since it is efficient to copy the formula from cell D3 to D4 to evaluate all the customer claims, we recommend using absolute reference for the “data” argument.

How to Sort from Lowest to Highest in Google Sheets

As described above, you can enter “1” or “TRUE” to sort the values in ascending order.

How to Find the Top 5 in Google Sheets

You can use conditional formatting to highlight the top 5 values within a range, assuming the highest value is the first. Let’s say you want to highlight the entire rows of the top 5 customers in the dataset.

Here’s how to do it:

1. Open the “Conditional Formatting” menu in the toolbar.
2. Select the entire table.
3. Choose “Custom formula is…” in the rule formulas.
4. Enter “=$D3<=5”, which means the cell format changes if the rank is equal to or lower than five (= top 5).
5. Modify the “Formatting style”.
6. Click “Done” on the bottom right.

*You must use partial absolute reference because you only want to use the “Rank” column (column D) as the conditional formatting criteria.

Now that you know how to use the RANK function in Google Sheets, you can easily rank your data and identify the top values.

For more tips and tricks on productivity and optimizing Google tools, visit Crawlan.com.