How to Applying Conditional Formatting Efficiently Across Entire Sheet

Dereck H

Can you apply a conditional format of say "anytime the value '0' appears throughout the sheet, change its color to RED" efficiently or do you have to make one conditional rule per column for all the columns you have in the sheet?

Additionally, is there an "OR" function within conditional formatting? For example, "If Column 1 is equal to OR greater than 0 - then apply this format". Right now I have to create 2 separate rules, one saying "If Column 1 is equal to" and another saying "If Column 1 is greater than".

Any tips or advice is appreciated, thanks!

Mike Wilday

Currently, you have to designate statuses for each column. it would be great if you could select more than one column but the Conditional Formatting does not let you.

Also, there is no OR function in the conditional formatting. You will have to set them up individually... 

Dereck H

Understood Mike, thanks for the confirmation, just wanted to double-check and see if there wasn't something I was missing or some clever workaround someone figured out.

J. Craig Williams

Be aware that Conditional Formatting is one of the limiting factors for performance. The more you have, the slower the sheet, given the same data.

Craig

Mike Wilday

That is good to know. Thanks for sharing. 

Brad Jones

Yes Craig hit it on the head.

Conditional formatting can be cool, but it severely impacts your performance.  In fact, ALL formatting has a negative impact on your sheet performance, along with formulas and links.

That said, it would be nice if one could apply one rule for a whole sheet instead of one rule per column per condition.  The latest update to the conditional formatting makes it easier to apply a rule from one column to multiple columns, but does not solve the real problem of having one rule that applies to any column where the condition is true.

I have one sheet that has 6 rules per column with 14 columns.  The new update won't address my needs.  So, I had to manually create 84 rules just to have it format the way we wanted.  And this was just a simple calendar... with 84 rules.

Valerie Barton

Yes, this would be a great feature to add. I have a similar problem. A simple sheet, I want all "No" responses to be red, but I have to do a rule for each column. Yuck.