Skip to content

Highlight the Top 5 Values in Google Sheets

In this article, I will show you how to apply conditional formatting to highlight the top five values in your Google Sheets data.

Take the sample data below, for example.

I will show you how to apply conditional formatting to highlight the records with the top five total sales values.

Step 1 – Select the Data

Start by selecting the data you want to consider.

Step 2 – Apply Conditional Formatting

With the data selected, go to Format Conditional Formatting.

In the panel on the right, select Custom Formula Is for the “format cells if” option.

Then enter the following formula into the formula box.

=$D3>=LARGE($D$3:$D$12, 5)

The first reference (in blue) refers to the top-most total sales number in the selected range. Also, note that a dollar sign is in front of the column letter but not the row number.

The reference inside the LARGE function (in red) refers to the range of sales numbers. Also, note that this is an absolute reference.

Once you’ve written the formula, click Done to highlight the records with the top five total sales numbers.

Formula Explanation

The LARGE function looks at the range of total sales numbers and calculates the 5th largest value (due to the five as the second argument).

=$D3>=LARGE($D$3:$D$12, 5)

The formula then compares the total sales number with the calculated value for each record. There is no dollar sign in front of the row number for this reference (in blue).

Therefore, the row number updates as Google Sheets evaluates the formula for each record.

If the total sales number is greater than or equal to the 5th largest value, then the formula evaluates to TRUE, and Google Sheets applies the conditional formatting.

Highlight the Top 10 Values

To highlight the top ten values instead of the top five, change the five to a ten within the LARGE function.

=$D3>=LARGE($D$3:$D$12, 10)

You can actually change the 2nd argument of the LARGE function to any number you want to highlight the top N values where N is the chosen number.

For example, the formula below highlights the top 3 values.

=$D3>=LARGE($D$3:$D$12, 3)

And that is how to highlight the top five or any number of values within Google Sheets.

This Post Has One Comment

  1. I just recently learned about the LARGE function from your challenges videos and I can’t tell you how glad I am that I learned about it! This is a really cool application of that function.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top