Today let me introduce to you the use of Large function to highlight the largest 3 values in each row in Google Sheets.

A while back I explained to you the use of Max function to conditional format the max value in each row.

Here is the link, if you didn’t see that – How to Highlight Max Value in a Row in Google Sheets.

With Large, we can highlight the largest ‘n’ values in each row. I have just put 3 in the title instead of ‘n’ for the sake of explaining the highlighting rule.

Here is one example of the use of the Large function to return the largest 1, 2, 3…. values from a row.

Here the formula =large(A1:D1,1) will return 50, and =large(A1:D1,2) will return 45.

Now see the syntax.

=large(data,n)

To get the 3rd largest value from row # 1 above, now you may know what changes you should make to the formula. If not, see that Large formula below.

=large(A1:D1,3)

As per the title, what we want to achieve is to color the cells containing the largest 3 numbers in each row, right?

Let me explain that in a step-by-step manner below.

Custom Formula to Highlight Max | Largest 3 Values in Each Row – Google Sheets

See the numbers highlighted in each row to understand what I am talking about.

In this type of conditional formatting, you will face three issues. So let’s address those issues in the step by step instructions below.

Normally to highlight the largest 3 values in each row, you can use the following formula in sheets.

=B2:B>=large($B2:$K2,3)

To apply, select the range B2:K, then go to Format > Conditional format and apply the above formula in the custom formula field.

See the above mentioned conditional format settings and one of the issues this formula has below.

Issue # 1: No Highlighting Happens When There Are Less Than Three Max Values

Please refer to this image for the format settings.

The formula has an issue that reflects in the last row B5:K5.

What is that issue?

The range B5:K5 is unaffected by the highlighting!

Since there are only two values in the said row, the Large function fails to return the 3rd largest value. You can try it on your sheet yourself.

=large(B5:K5,3)

As a result, it would only return NUM! error. So there is no point to expect the conditional format to properly work in that row. We can address this issue as follows.

Issue # 2: Large Function NUM Error

Fill all the blank cells in the range B2:K5 with 0. So the just above formula will return 0 as the third largest value instead of NUM! error.

That makes sense in the conditional formatting but not practical in a growing range. The sheet will look ugly if you fill 0s in all the blank cells in a range like B2:K.

Let me explain how to solve this by modifying the formula used above to highlight the largest 3 values in each row.

To handle the Large function NUM! error, here we can wrap the ‘data’ with the function N as below.

=B2:B>=large(arrayformula(n($B2:$K2)),3)

I have modified $B2:$K2 to arrayformula(n($B2:$K2)). The N function is used not with a single cell but with a range. So we mustn’t forget to use ArrayFormula together.

Let’s see the output now and get ready to solve a new problem!

Issue # 3: Highlighting Largest 3 Values in Each Row Affects Blank Rows Too

When we solve one conditional format issue another one surfaces. Hopefully, this is the last one.

Can anyone guess why all the bank rows got highlighted?

It’s because the N function used in the above example makes the blank cells filled with 0.

If all the values in a row are 0 means the largest three values are 0. So obviously the entire row got highlighted.

How to solve this highlighting puzzle?

We can use AND logical operator as below.

Syntax:

AND(logical_expression1, [logical_expression2, …])

In this, use the just above formula as the logical_expression2. Use len($B2:$K2) as the logical_expression1.

You May Like: LEN Function in Google Sheets and Practical Use of It.

So the final formula to conditional format/highlight the largest 3 values in each row in Google Sheets will be as follows.

=and(len($B2:$K2),B2:B>=large(arrayformula(n($B2:$K2)),3))

Resources:

• Conditional Format Max Two Values Based on Criterion in Google Sheets.
• AND, OR, or NOT in Conditional Formatting in Google Sheets.
• Highlight an Entire Row in Conditional Formatting in Google Sheets.
• Role of Indirect Function in Conditional Formatting in Google Sheets.
• How to Highlight Every Nth Row or Column in Google Sheets.
• Highlight Matches or Differences in Two Lists in Google Sheets.
• Highlight Intersecting Value in Google Sheets in a Two Way Lookup.
• Google Sheets – Highlight the Max Value in Each Group.
• Highlight Min Excluding Zero and Blank Cell in Google Sheets.