Arrow left
Back to product guides
Google Sheets Formulas

How to Use RANK Function in Google Sheets

In this article, you will learn how to utilize the RANK formula in Google. The Rank formula is beneficial when you want to rank each number in a range.

How to Use the RANK formula in Google Sheets

  1. Type “=RANK” or go to “Insert”“Function” “Statistical”“RANK”.
  2. Select a value whose rank is determined.
  3. Input a range containing all numbers you want to rank.
  4. Define how to rank numbers in ascending or descending order.
How to insert the RANK function from the menu bar in Google Sheets

The generic syntax is as follows:

=RANK(value, data, [is_ascending])

Value: This is a value whose rank is checked in the selected range.

Data: This is a data set including all figures you want to rank.

[is_ascending]: You can input 0 or 1. If you enter none of them, the formula assumes 0 is input. You can use “FALSE” and “TRUE” instead of 0 and 1, respectively

0 or “FALSE”: The function ranks the numbers in descending order. The greatest number is positioned in the first rank.

1 or “TRUE”: The formula ranks the values in ascending order. The lowest value is positioned in the first rank.

Assume you are in a corporate planning department and need to rank your clients in descending order according to the number of account receivables.

How to use the RANK function in Google Sheets

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

Value: C3

Data: $C$3:$C$12

[is_ascending]:FALSE

As it is efficient to copy the formula in cell D3 from D4 to D12 to evaluate all account receivables, we recommend you use the absolute reference for the “data” argument.

How do you rank from lowest to highest in sheets?

As described above, you can enter “1” or “TRUE” to rank figures in ascending order.

How do you find the top 5 in Google Sheets?

You can use Conditional Formatting to highlight the top 5 values in a range, assuming the largest value is the top. Assume you want to highlight the entire rows for the top 5 clients in the data set.

  1. Open the Conditional Formatting menu from the menu bar.
  2. Select the entire table.
  3. Choose “Custom formula is…” in Formula rules.
  4. Enter “=$D3<=5”*, which means cell format changes if the rank is equal to or lower than five (=top 5).
  5. Change “Formatting style”.
  6. Press “Done” at the bottom right.

*You must use the partial absolute reference because you want to use only the “Rank” Column (Column D) as a criterion for Conditional Formatting.

How to color cells with Conditional Formatting based on values’ ranks

Analyze your live financial data in a snap

Are you learning this formula to process financial data, build a financial model, or conduct financial analysis? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real time. For instance, you can have a live Account Receivable Dashboard. Furthermore, you can access more than fifty templates here, from the simple Income Statement and Operational KPI templates to Multi-Currency Consolidated Income Statement, Balance Sheet, and Cash Flow templates. You can also customize these templates as you want without breaking the automated data inflow.

To learn more about LiveFlow, book a demo.

Automate any custom financial dashboard in Google Sheets with LiveFlow

Learn how to do this step-by-step in the video below 👇

Do you need personal help?

Our team of real people are here to help you any time between 9am and 10pm GMT.
Check Icon
Email us at: founders@liveflow.io
Check Icon
Call us at +1 (415) 650-1711