Conditional formatting in Calc applying to too many cells

[sremick]

So I have a column in Calc (E) where I want the minimum value to be highlighted. I made my own style which gives the text a yellow background. I then create a conditional format to apply to E2:E9999 where “if cell value =MIN(E2:E9999)” then the format is applied.

At first, it works, and the lowest # in the column, 18, is highlighted on E65. But then 945 is also highlighted on E189, 960 is highlighted on E212, 2128 on E311, 2143 on E334 and 3268 on E364. There is only one other conditional format set, that applies to a single cell elsewhere not in column E and that is working fine so there isn’t any overlap. This seems straightforward enough that I’m at a loss as to what is going wrong.

[JohnSUN]

Just use absolute address - MIN($E$2:$E$9999)

[anon73440385]

at a loss as to what is going wrong.

The algorithm for conditional formatting of a cell range basically works as follows:

• Take the formula for the upper left corner of the cell range (in your case E2 and you enter the formula in fact only for this cell)
• Check if the formula yields TRUE and, if yes, apply defined cell style
• Move to next cell of the range (in your case E3) and important adapt the formula if not using absolute addressing - hence it gets adapted to MIN(E3:E10000) (which is not what you want)
• Check if then formula yields TRUE and, if yes, apply defined cell style.
  …and so on for the whole range the conditional formatting should be applied to.

And that’s what @JohnSUN’s advice is about - avoid the formula adaption through absolute addressing.

[sremick]

Thank you! I was going to ask @JoshSUN for details as to why that was necessary. I suppose the reasoning behind how the conditional formatting works on a range makes sense, just wasn’t obvious that it was something I’d have to accommodate for.

I adjusted it to use absolute cell references and it’s working fine now.