I wanted to find and highlight the smallest value larger than 0, but the formula I am using does not work in the conditional formatting.

 =MIN(FILTER(L4:L100, (L4:L100)>0))

Namely, if I use it inside the sheet, it will find value 0.44 and write it out.

However, if I used it in the conditional formatting, it will mark ALL values larger than 0.

Anyone knows why the formula does not work in the conditional formatting?

  • Thanks for this question, it's the solution to my problem. :) The formula you entered here already works great for finding the lowest number (in a range) that is higher than a provided number. Aug 3, 2020 at 14:45

1 Answer 1


I found a solution out of sudden. I have forgotten (did knot know it was needed) to add another condition.

The correct formula is:

 =L4:L100=MIN(FILTER(L4:L100, (L4:L100)>0),1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.