Locked lesson.
About this lesson
Learn to create custom conditional formatting rules based on formulas.
Exercise files
Download this lesson’s related exercise files.
Custom Conditional Formats - Begin.xlsx24.3 KB Custom Conditional Formats - Complete.xlsx
24.4 KB
Quick reference
Conditional Formats with Custom Rules
Creating custom conditional formatting rules based on formulas.
When to use
Conditional formatting is used to highlight key pieces of data that meet certain conditions. Unfortunately, Excel’s defaults won’t cover every scenario we need, so we have the ability to build our own rules to suit.
Instructions
Creating Custom Rules
- Select D4:D13, go to the Home tab > Conditional Formatting > New Rule
- Choose “use a formula to determine which cells to format”
- Enter =D4<25000 and click Format
- From the Fill tab select a light red background and click OK to exit the rules
- Create 2 more new rules
- =D4>50000 to apply a yellow background with a dark red font
- =D4>75000 to apply a light green background
- Notice the rules have been applied
Rule Precedence
- Why do we have green cells with red font when we didn’t set a rule that way?
Managing Custom Rules
- Select D4:D13, go to the Home tab > Conditional Formatting > Manage Rules
- Notice the rules are applied top to bottom
- Move the yellow rule up and all the green goes away
- Move it back down
Understanding precedence
- The Green and Red rules do not change the font, but all rules change the fill color
- An element cannot be changed once set
- The green rule set the fill (>75000), then the yellow rule (>50000) can’t change the color, but it can change the font, so it results in a green fill with red font
- On the yellow rule, select Stop if True: the green rule will now have a black font as the engine will stop processing any more rules once the >75000 condition is met
Lesson notes are only available for subscribers.