Google Sheets – Highlight Minimum Value in Each Row Ignoring Zeros

conditional formattinggoogle sheets

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.)

Best Answer

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

=A1=ArrayFormula((MIN(IF({$A1,$B1,$D1,$E1}>0,{A1,$B1,$D1,$E1}))))

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

Related Topic