If there is no criterion or condition it will be easy to highlight max two values in a column or row. You can use the LARGE function for that. In order to conditional format max two values based on a criterion, the LARGE alone won’t work.
As a side note, I am adding another tutorial under the tag conditional formatting. This is for those who
Why the LARGE alone won’t work?
The reason, similar to MAXIFS, there is no LARGEIFS function in Google Sheets. But you can compensate that by using IF logical function with LARGE.
Here in this tutorial, you can learn that cool combination of LARGE + IF. Why am I using the function LARGE over MAX here?
If you are a newbie in Google Sheets, definitely you will have this question in your mind.
The answer is simple. I am using the LARGE function because of its ability to return max 1 value, max 2 value likewise. That means the Nth largest element in a column/row or dataset.
Example screenshot to conditional format max two values based on a
How to Conditionally Return Large Two Values Using Large + IF
To conditionally return large two values there are a variety of options in Google Sheets. This tutorial shed some light on that – Sum Large/Max n Values Based on Criteria in Google Sheets.
If you go through that tutorial, you can understand without any doubt that the use of the LARGE function is not a must. But if your focus is on conditional formatting, then you must use the LARGE + IF combination.
So let me first show you how to return two large values using the LARGE formula in Google Sheets. That means without any condition. Then we can see the LARGE + IF, conditional large.
Extract Large 2 Values without Conditions/Criteria in Sheets
Large # 1 Formula:
=large(C3:C8,1)Large # 2 Formula:
=large(C3:C8,2)How to include conditions in the LARGE formula?
Extract Large 2 Values with Conditions/Criteria in Sheets
Time to learn the use of IF with LARGE.
In my example, the purpose of the IF formula is to return the value in C3
See the formula below. Further, if you think my above explanation is not enough to understand my point, jump to the screenshot given after the formula.
=ArrayFormula(IF($B$3:$B$8>=100,$C$3:$C$8,0))Here is that IF array formula output.
Use this as the Data in the LARGE function to return large two values based on condition.
Large # 1 Formula with Condition:
=ArrayFormula(large(IF($B$3:$B$8>=100,$C$3:$C$8,0),1))Large # 2 Formula with Condition:
=ArrayFormula(large(IF($B$3:$B$8>=100,$C$3:$C$8,0),2))Our topic is how to conditional format max two values based on a criterion in Google Sheets. Other than the conditional formatting part, I have solved the
I mean, you know now how to return large 2 values based on a given criterion. The above two formulas are examples of that. How to apply these two formulas in conditional formatting?
Google Sheets Formulas to Conditional Format Max Two Values Based on Criterion
Open the “Conditional format rules” panel in Google Sheets. To open this click the Format menu Conditional formatting. There do the below settings.
Apply to range: C3:C18
Format rules > Custom formula is:
=C3=ArrayFormula(large(IF($B$3:$B$8>=100,$C$3:$C$8,0),1))Formatting style: Use the default color or pick the one that suits your taste.
That’s all. This will conditionally highlight the max 1 value in the selected range. For your reference here is the screenshot of conditional formatting rules.
To highlight the second max value based on condition follow the above conditional formatting settings. You must replace the custom formula with the below one.
=C3=ArrayFormula(large(IF($B$3:$B$8>=100,$C$3:$C$8,0),2))You know now where the difference lies. I mean to conditional format max 3 values, you must change the formula as below.
=C3=ArrayFormula(large(IF($B$3:$B$8>=100,$C$3:$C$8,0),3))All depends on the ‘n’ in LARGE. In concise;
LARGE(data, n)In this, the argument ‘data’ is the IF formula output. Change ‘n’ to 1, 2, etc. in each formula. This way you can highlight large 1, 2, 3… values in a data set conditionally in Google Sheets. Enjoy!
Resources:
