Excel Conditional Formatting Top 1 exclude 0

Question

Hello,

I've seen numerous posts and answers online but no one seems to actually just answer this formula question:

I have a column using two conditional formats:

• One to highlight every other row for easier visibility
• One to highlight the maximum value in that column, but exclude cell values of 0

I can't just add another formatting to exclude those cells with that value because that messes up the every-other-row highlighting. I just need the working formula for the top 1 conditional formatting that excludes cell values of 0, so that the whole column isn't initially highlighted when everything is at 0

Thanks :)

Answer 1

Let's say you want to apply this to B4:B39.

The formula to use in conditional formatting would be

=AND($B4=MAX($B$4:$B$39),$B4<>0)

Answer 2

Hi,

Select B4 then drag to select B4:b39 and apply this conditional format formula.

=B4=MIN(IF($B$4:$B$39<>0,$B$4:$B$39,""))

Answer 3

Looks like that got it. Thank you sir :)

Answer 4

Thanks Mike. Unfortunately, that's still leaving all the zeros highlighted with the minimum colour. Am looking for the same result as with the initial MAX formatting, except this time, only the minimum values, higher than 0, will be highlighted.

Had also tried:

=$T12=MIN(IF($T12:$T34=0,FALSE,$T12:$T34))

but that also left the zeros still highlighted :(

Answer 5

Let's say you want to apply this to B4:B39.

The formula to use in conditional formatting would be

=AND($B4=MAX($B$4:$B$39),$B4<>0)

So, I had hoped that the reverse, for minimum values would be simply a matter of 

=AND($B4=MIN($B$4:$B$39),$B4<>0)

However, it's not highlighting anything, I think because it's still taking the zeros into account when looking for the minimum value, and therefore not highlighting those results :(

Thoughts, suggestions?