Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Learn to create custom conditional formatting rules based on formulas.
Download this lesson’s related exercise files.Conditional Formats with Custom Rules.xlsx
13.5 KB Conditional Formats with Custom Rules - Completed.xlsx
Conditional formatting – custom rules.
Creating custom conditional formatting rules based off formulas.
Where/when to use the technique
Conditional formatting is used to highlight key pieces of data that meets 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.
Creating Custom Rules
- Select C4:C19, go to the Home tab --> Conditional Formatting --> New Rule
- Change the 2-Color Scale to Classic
- Change the “Format only top or bottom ranked values” to Use a formula to determine which cells to format”
- Enter =C4<25000 and click Format
- Change “light red fill with dark red text” to “custom format…”
- From the Fill tab select a light red background and click OK to exit the rules
- Create 2 more new rules
- =C4>50000 to apply a yellow background with a dark red font
- =C4>75000 to apply a light green background (click Clear on the Font tab)
- Notice the rules have been applied
- Why do we have green cells with red font when we didn’t set a rule that way?
Managing Custom Rules
- Select C4:C19, 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
- 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 does resulting in a green fill with red font.
- On the green 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.