I'm trying to highlight the smallest value in each row using conditional formatting (CF), while ignoring zeros and blanks. Also, I don't want the ranges to be continuous (i.e. skipping certain columns).

For example, give the following sheet:

   A    B    C     D      E
1 345   0   skip  123  <empty>
2  0   211  skip  234  <empty>

I would like to have D1 and C2 highlighted, independently.

I can get part way by using a custom formula in CF: =A1=min({$A1,$B1,$D1,$E1})

However I'm running into a couple of issues:

  • Zeros and empty cells are highlighted (which I don't want)
  • How to apply CF to each row independently without manually creating CF for each row (i.e. without having to do =A2=min({$A2,$B2,$D2,$E2}), =A3=min({$A3,$B3,$D3,$E3}), etc.)

I found another way to do it with a single CF formula using ArrayFormula:


The key is to also Apply to Range: A1:A,B1:B,D1:D,E1:E


In your example, the entire range A2:D can be formatted in the specified way by using the custom formula

=and(column(A2) <> 3, A2 = min(filter({$A2,$B2,$D2}, {$A2,$B2,$D2} > 0)))

Which is a combination of two conditions:

  • column(A2) <> 3 says to not format column C
  • filter({$A2,$B2,$D2}, {$A2,$B2,$D2} > 0) says to ignore the empty cells and zeros when finding the minimum. The value of current cell being equal to the minimum of these (A2 = min(...)) ensures in particular that it's not zero or blank itself.

Your Answer

