Software

10 creative uses for Excel's conditional formatting feature

Distinguish business rule violations

As you can see here, working with the time values complicates things a bit. This solution uses >.34 to represent time values greater than eight hours, which will work in most cases -- you can't use the value 8 or even the time value 8:00. You can also use the predefined Greater Than rule in Excel 2007 and 2010, which will automatically use the more accurate value of 0.333333….

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

2 comments
mark
mark

You have to go in to Conditional Formatting, find and open the rule for edit (but don't have to make changes), [OK] it, [Accept] it and [Close] it. What a pain! So how is this made 'Dynamic'?

jberezinski
jberezinski

Hi Susan, great tips for Excel users. But what do you mean "This rule is unstable if you use normal referencing"? I realize it might be off-topic or beyond the scope of your post, but is there a straight-forward set of rules or guidelines for determining when it is appropriate to use named ranges so that the formula or conditional formula will produce the desired outcome? I would have probably attempted this using normal referencing, and when it didn't work, I would have probably resorted to using additional columns of formulas referencing the original data set / range.