We can easily highlight unique top n values in columns or rows in Google Sheets. A combination of UNIQUE and LARGE will do that.

But I’ll also use the functions ArrayFormula(), AND(), N(), and LEN() to address blank cells and a COUNTIF() optionally to apply a white fill color to duplicates.

Let me give an example. Then we will go to the formulas.

I have the numbers {1;2;3;4;5;6;7;8;9;10;1;2;3;4;5;6;7;8;9;10} in a column (range B2:B21) in Google Sheets.

If n=5, the unique top n numbers to highlight in B2:B21 will be 10, 9, 8, 7, and 6, not 10, 10, 9, 9, and 8.

In addition to that, you may or may not want to apply the fill color to the multiple occurrences of the top n numbers (in that case, we can optionally use a COUNTIF rule).

Here are examples (screenshots) of single-color approaches. Regarding multi-color, I’ll explain in a later part of this post.

Unique Top 5 (All the Occurrences):

Unique Top 5 (First Occurrence):

Highlight Unique Top N Values in Columns in Google Sheets

Here are the formulas and steps to follow.

Select the menu Format > Conditional formatting. Under “Single color” (tab), follow the below settings.

1. Apply to range – B2:B21 (or B2:B).
2. Format rules > Format cells if… > Custom formula is – Insert the formula given after point 4 below.
3. Format rules > Formatting style – Choose a fill color (here Green).
4. Select Done.

=and(len(B$2:B),B2>=large(unique(arrayformula(n(B$2:B)),false),$F$1))

Enter 5 in cell F1 to dynamically control the n. If you want to highlight the unique largest 10 numbers, replace 5 with 10.

It will highlight all the occurrences of unique top n values.

If you want to highlight only the first occurrences of unique top n values, in addition to the above, you may require to add one more rule.

=countif(B$2:B2,B2)>1

The “Apply to range” is the same as above, i.e., B2:B21. The fill color should be White here.

Once added, you should drag and place this rule above the first rule, and that’s very important.

What about highlighting the largest unique n numbers in each column in B2:E21?

Just modify the “Apply to range” to B2:E21 from B2:B21.

Formula Explanation

Let me break the first formula (Green fill color) into four parts. That seems the easiest way to learn it.

Part_1:

arrayformula(n(B$2:B)) – N converts blank (or text if any) to 0 (zero) and return other numbers as it is.

Part_2:

unique(part_1,false) – UNIQUE returns only the unique numbers in the selected column range.

Part_3:

large(part_2,$F$1) – LARGE finds the nth largest value based on F1.

Part_4 (final formula):

It’s a logical AND test as follows.

AND(logical_expression1, logical_expression2).

That’s equal to AND(len(B$2:B),B2>=part_4).

Here the LEN in logical_expression1 returns TRUE (any number) if the cell has value or FALSE (0) if blank.

The second formula (White fill color) is self-explanatory as it’s a COUNTIF to count the occurrences.

Highlight Unique Top N Values in Rows in Google Sheets

In the above example, we have learned how to highlight unique top n values in a column or each column in Google Sheets.

What about a row or each row?

Here in the following example, the “Apply to Range” is B2:U2 (or B2:U).

Green Color:

=and(len($B2:2),B2>=large(unique(arrayformula(n($B2:2)),true),$B$1))

White Color (optional):

=countif($B2:B2,B2)>1

Here B1 controls the N.

Additional Tips (Multiple Colors)

Sometimes you may want to differentiate unique largest n values with different colors. What will you do then?

Of course, we can use the same COUNTIF to fill white color to skip remove highlighting in duplicate value cells.

But for top 1, top 2, top 3…, we must use rule 1, rule 2, rule 3…

We must choose different fill colors for each rule.

Here is how to highlight unique top n numbers with multiple colors in Google Sheets.

If the set of numbers is in columns, use our earlier formula. There are two changes, though.

Rule 1:

=and(len(B$2:B),B2=large(unique(arrayformula(n(B$2:B)),false),1))

Earlier it was B2>= , here it is B2=. Also, we have specified the ‘n’ directly here, which is 1.

Rule 2:

=and(len(B$2:B),B2=large(unique(arrayformula(n(B$2:B)),false),2))

Similarly, add more rules. Only change n and use different fill colors.

The COUNTIF must be on top of these rules.

Example (screenshot) with five multi-color rules applied for largest unique five values in Google Sheets.

For data (set of numbers) in rows, you can use our earlier related rules with the above two modifications.

That’s all. Thanks for the stay. Enjoy!

Related:-

• Highlight Max Value Leaving Duplicates in Row Wise in Google Sheets.
• How to Highlight Largest 3 Values in Each Row in Google Sheets.