Highlight Top 10 Ranks in Single or Each Column in Google Sheets

To highlight top 10 ranks in Google Sheets we can use the RANK or LARGE functions.

The formulas using the said two functions are capable of highlighting up to rank 10 (n) in a single column or if there are values in multiple columns in each column separately.

If there are multiple columns to highlight, we should carefully use the dollar sign (relative and absolute reference) in the formula reference.

If we do it correctly, no need to change the formula depending on the number of columns. A single column highlight rule (formula) will automatically adapt to the ranges (columns) selected to highlight.

This post describes both the methods (RANK or LARGE) to highlight the top 10 ranks (values) in Google Sheets.

RANK Formula to Highlight Top 10 Ranks in Columns

Single Column

Let me start with a single column. The value to highlight up to top 10 ranks are in B2:B as below.

Highlight Top 10 Ranks - Single Column

I have used the below RANK formula.

=RANK(B2,B$2:B)<=10

The formula determines the rank of the value in cell B2 in the range B$2:B. Cell B2 is the first cell in the range.

Cell B2 in the formula is relative which means the conditional format will first determine the rank of the value in cell B2 in B$2:B, then the rank of the value in cell B3 in B$2:B and so on.

You May Like: Relative Reference in Conditional Formatting in Google Sheets.

Since the range B$2:B is absolute (only the row numbers are absolute, the column is relative), it won’t change to B$3:B, B$4:B, and so on.

So the formula will correctly highlight the top 10 ranks in the said column. To use this formula as a custom format rule, please follow the steps below.

Steps:

  1. Select B2:B.
  2. Click Format > Conditional formatting which will open the contextual conditional format menu as a sidebar.
  3. Make sure that “Apply to range” is B2:B (probably B2:B1000 if there are 1000 rows).
  4. Select “Custom formula is” under “Format rules”.
  5. Insert the above Rank formula in the blank custom formula field.
  6. Click “Done”.

If you have successfully implemented the settings detailed in the steps, the settings would look like as shown on the screenshot below.

Single Color Format Rule Settings

Each Column

To highlight the top 10 values in each column, you can use the same above formula. Because in the formula I’ve correctly used the absolute/relative cell references.

See B$2:B, which is the range to highlight. In this, as I have already mentioned, only the row numbers are absolute.

What are the change(s) required then?

If the columns to highlight are B2:B, C2:C, and D2:D only select B2:D before applying the highlighting rule or change the “Apply to range” to B2:D in the sidebar panel.

Highlight Top 10 Ranks - Each Column

There is no other change.

LARGE Formula to Highlight Top 10 Ranks in Columns

In Google Sheets, we can also use the Large formula for the said purpose.

=B2>=large(B$2:B,10)

The above formula will work in single as well as multiple columns similar to the Rank formula. I’m not detailing it as all the settings are the same as per the Rank formula.

Highlight Top 10 Values in Ascending Order

The earlier two conditional format rules assign rank # 1 to the max value in a column. To assign rank # 1 to the least 1 value (smallest value) in a column we should change the formulas.

Regarding the first formula (Rank) change it as below.

=RANK(B2,B$2:B,1)<=10

Regarding the second one (Large), we should change the function Large to Small.

=B2<=small(B$2:B,10)
Conditional Format Least 10 Ranks

This way we can highlight top 10 ranks in ascending or descending order in Google Sheets.

Resources:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

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