# [Calc] How to highlight the minimum non-zero value in a row?

I’m trying to highlight (as in change the background colour of the cell) the minimum non-zero value in a row of numbers. I can set up conditional formatting to do this, but whenever I add a new row of numbers, the formatting extends to include the new row (e.g. highlight minimum of range B2:B6, insert a new row below, range is now B2:C6). But the new row needs to be compared against itself and not any other rows (row B compared only to row B, row C compared only to row C, etc.). How can I do this without having to manually adjust the formatting for every single row of the worksheet whenever I add new rows?

And clone-formatting doesn’t work for this either. It, also, changes the range of the formatting instead of applying the formatting to the new range (e.g. range B2:B6 gets cloned onto D2:D6, range is now B2:B6;D2:D6; it should be range B2:B6 and a separate rule for range D2:D6).

This is especially aggravating because I never had these problems in Office (it applied formatting exactly the way I expected it to) and I really don’t want to shell out \$100+ for an Office licence to fix this one issue.

Hello,

this answer assumes (that’s how I understood your question):

1. You want to highlight the Minimum in a row >0.
2. A row spans from column `A` to column `F`
3. Data begin in row `2` (Row 1 contains header;top left cell is `A2`)
4. There are `19` rows (bottom right cell is `F20`)

Condition: `Cell value is` `equal to` `SUMPRODUCT(MIN(IF(\$A2:\$F2>0,\$A2:\$F2,"")))`
Cell Range: `A2:F20`

please see the following sample file

MinimumByRow-Conditional.ods and adapt ranges to fit your special needs.

Hope that helps.

I ended up using
Condition: `formula is` `a2=minifs(\$a2:\$g2,\$a2:\$g2,">0")`
Cell Range: `a2:g20`
But your method works, too.

Edit: Your method fails when I add new rows and start inputting numbers into those new rows. The `minifs` method I described still works.

That’s a simple addition.

With B2:B6;D2:D6 should work fine if the formula in the condition it’s done with relative addresses for B row.

I got it working by changing the formula from bottom N element 1 to formula is b2=minifs(\$b2:\$g2,\$b2:\$g2,">0") over range b2:g6. Adding new rows within that range highlights cells as appropriate.