This post contains two conditional format rules to highlight the min value in each group in Google Sheets.

Assume you have a Sheet containing a player list entered in columns A (name), B (age), and C (country name).

If you want to find the min age of players by country name, you can use a conditional format rule.

The rule will locate and highlight the min age in column B by grouping the country name in column C.

But for the explanation purpose, I am using a sample dataset that contains a list of fruit names and their quantity.

We will use the formula to highlight min quantity w.r.t. of each fruit.

Formula to Highlight the Min Value in Each Group in Google Sheets

Please see our sample data in A2:B20 and the highlighting applied in B2:B20.

As per my method, we require two conditional format rules to highlight the min value in each group in Google Sheets, and here are them.

Rule # 1:

=isblank($B2)=true

Rule # 2:

=B2=MIN(FILTER($B$2:$B$20, $A$2:$A$20=A2))

How Do I Apply the Above Rules to Highlight the Min Value in Each Group?

1. Select B2:B20.
2. Go to Format > Conditional formatting.
3. Copy Rule # 1 above and paste it into the blank field under Format cells > Custom formula. Choose “White” fill color.
4. Copy Rule # 2 above. Select “Add another rule” and paste it into the designated field, possibly overwriting Rule # 1. Select “Orange” fill color.
5. Select Done.

This way, we can apply the conditional format rule to highlight the lowest (min) value in each category (group) in Google Sheets.

Anatomy of the Highlight Rules

We will see Rule # 2 first which highlights the min value in each group.

In the first row in the range A2:B20, the FILTER filters B2:B20 (quantity) wherever A2:A20 (fruit names) matches A2 (“apple”).

The purpose is to filter all the quantities corresponding to “apple.”

So the result would be 100, 255, and 125.

=FILTER($B$2:$B$20, $A$2:$A$20=A2)

Using MIN, we find the min value of the first group (“apple”), which is 100.

In the second row, the formula will automatically become =FILTER($B$2:$B$20, $A$2:$A$20=A3), and in the third row =FILTER($B$2:$B$20, $A$2:$A$20=A4).

So the min value in the first three rows will be 100 as A2, A3, and A4 contain “apple” as the criterion.

So the rule will become =B2=100, B3=100, and =B4=100 in the first three rows.

It evaluates to TRUE in B2, so the formula fills B2 with the “Orange” color.

The same applies to other groups (categories) in the range.

The purpose of Rule # 1 is to highlight any blank cells in the min column with the “White” fill color.

It supersedes Rule # 2. It’s necessary because the first rule will highlight all blank cells in the range with the “Orange” color.