Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Understanding why you can define two different conditional formatting rules, yet end up with a mixture of both.
Download this lesson’s related exercise files.Understanding conditional formatting rule precedence.xlsx
37 KB Understanding conditional formatting rule precedence - Completed.xlsx
Understanding Conditional Formatting Rule Precedence
Understanding conditional formatting rule precedence.
When to use
Debugging conditional formatting rule results when they are returning unexpected results.
Understanding Rule Precedence
- Rules are applied from top to bottom in the Manage Rules dialog
- For each cell which has the conditional formatting rules applied EVERY rule will be processed, resulting in rule layering and complex results
- Once a specific element has been set (font, font color, fill color, borders, etc..), it cannot be overwritten later by another rule
Modifying Rule Order
- To modify rule application order go to the Manage Rules dialog
- Select your rule and move it up or down in the order by clicking the arrows above the rule list
Using Stop as True
- The Stop as True setting can be configured for any (or all rules) in the manager
- Rules will be processed until a rule configured with Stop as True has been triggered
- Upon encountering a true result for a Stop as True rule, no further rules will be processed
Hints & tips
- Rule precedence can be very useful to create complex layered rules
- If you do want to ensure that the rules only ever appear exactly as you’ve set, use Stop as True on the rule
- Often, just reordering the rules will help return the results you expect
Lesson notes are only available for subscribers.