How To Highlight The Top 5 Values In Google Sheets With Formulas

In this post, you’ll learn how to find and highlight the top 5 values in Google Sheets.

For all the examples that follow, we’ll use this dataset, which is available in the downloadable template at the end of this post:

Example Dataset

We’ll see how to highlight the rows with the top 5 values, as well as how to extract those values using SORTN.

How To Highlight The Top 5 Values In Google Sheets

Method 1: Use the LARGE Function

Highlight your data range, in this case, A2:C21 (omitting the header row).

Then go to the menu: Format > Conditional formatting

Under Format rules, select Custom formula is

Enter this formula (explained below):


Set your formatting styles like the yellow background color shown in this example.

Your conditional formatting sidebar should look like this:

conditional Formatting to highlight top 5 values in Google Sheets


The conditional formatting will highlight the top 5 values in your table.

How does this formula work?

The LARGE function returns the nth largest element from a dataset.

In this example, it returns the 5th largest value from column C of our data:


(If you want the top 10 values, simply change the 5 to a 10.)

Notice the $ signs around the range are required for the conditional formatting. They turn the range into an absolute reference so it stays locked on column C.

This part of the formula (in red):


tests each row to see if it’s larger than or equal to this 5th largest value.

Notice the $ in front of the first C, but not in front of the 2, i.e. $C2 not $C$2. This is crucial to apply conditional formatting across the whole row.

Method 2: Use the RANK Function

Follow the steps above, but use the RANK function as the basis for the custom formula instead:


How does this formula work?

The RANK function returns the position of a value within a set of values.

In this example, the RANK function returns the rank of each value in column C:


As earlier, note the position of the $ signs around the cell references as these are crucial when we create the conditional formatting rule.

The full formula then checks if it’s less than or equal to 5.

(If you want the top 10 values, simply change the 5 to a 10.)

If the value satisfies this criterion, then the conditional formatting is applied to the whole row.

Now, suppose you want to return the top 5 rows, rather than just highlight them, how would you do that?

How To Find The Top 5 Values In Google Sheets With The SORTN Function

The SORTN function sorts your data and returns the first N items.

This formula will return the top 5 rows from the data:


It sorts the data from high to low and returns only the first five values.

SORTN Function Top 5 Values

The SORTN function has a lot of options.

From left to right, the arguments inside the formula are:

$A$2:$C$21 is the range to sort.

5 is how many results to return. If you want the top 10 values, simply change the 5 to a 10.

0 ensures the SORTN returns only 5 values. You can specify other options to deal with ties.

3 is the number of the column to sort, which represents column C in this example.

FALSE sorts in descending order, high to low. TRUE sorts ascending.

Highlight The Top 5 Values In Google Sheets Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.

9 thoughts on “How To Highlight The Top 5 Values In Google Sheets With Formulas”

  1. Hey ben, you’ve been a great help for me. Is there a way to create conditional formatting (for the top 5) from a conditional format?

  2. Hi,

    This is very helpful. I have a question though. Suppose the range of cells is not in one column and is scattered in the Sheets somehow, how to go about using the RANK and LARGE functions?

    My dilemma is that I’m trying to get the top 3 values for cells in (for example) C10, F10, I10, L10, and so on.

  3. Ok, so I want to do this with the range of A2 through L57

    Some of L57 is #DIV/0!, so when I do true, those show up first, so how can I adjust this formula: =SORTN($A$2:$L$57,10,0,11,FALSE) and get only the top 10 times I want, but only Collumn a and collumn L?

  4. Hi. I just noticed that =$C2>=LARGE($C$2:$C$21,5) doesn’t work anymore. Do you have any idea why? I need to highlight 3 highest values but i don’t know how..

  5. I have multiple columns, in each column I want to highlight the top 4 values. But i dont want to do it manually column by column, is there an easier way to do it for 1 column and extend it ?

Leave a Reply

Your email address will not be published. Required fields are marked *