Conditional Formatting in Table

[leahschneider]

I have a table in which I use conditional formatting on a column, darker for higher and lower numbers, white for middle numbers.  This column often has all 0s and in those cases, everything is colored even if I set the middle value to 0 and set that to white.  Is there any way I can get all 0s to be all white?

[Seth_C_Bauer]

@leahschneider Have you tried selecting the "diverging" checkbox? This allows you to choose 3 colors across the range. In this case, you could then choose white for your lowest values

[leahschneider]

Yes.  I have it in diverging.  I have white selected for "0" or middle values.  I would like only the highest and lowest numbers that are above 0 and not blank highlighted.

[Seth_C_Bauer]

@leahschneider What about having 0 and white as your low value? That is how I did it on a small sample and the output was what I expected since 0 was my lowest value.

[leahschneider]

I need both low values and high values highlightedso I can't set low values to white.

[Seth_C_Bauer]

@leahschneider I may just be missing the objective... but if I want high to low, and exclude 0's and make them all white, what I describe should work. This still gives you a high to low color range...

[leahschneider]

This is the issue that I'm dealing with:

When Column2 happens to have values that are not 0, it works fine.  However, in some cases, there are no non zero data and I would like it to be highlighted in white.

[Seth_C_Bauer]

@leahschneider ok. I'm not aware of being able to add "except" conditions in conditional formatting.

[leahschneider]

I don't want except conditions.  I want the number 0 to be white when I have conditional formatting that says that the number 0 is white.

[Seth_C_Bauer · Accepted solution]

@leahschneider When only 0 is the value, then that it is your highest number and will assume the highest value (dynamic) color. The only other way around this would be to manually choose a high value that exceeds or is close to your maximum value in the data set so that the conditional format can compare your '0' - white value to a hard value (example: 1,000,000)

Then, when you only have '0', the conditional format understands what the upper and lower bounds of the colors should be. Without that, your example of dynamic high will always be the high color since the highest value is 0.00 in the result set.

Manually setting this upper bound will produce your expected result.