About this lesson
Learn to create custom conditional formatting rules based on formulas.
Multiple versions of this lesson are available, choose the appropriate version for you:
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.Conditional Formats with Custom Rules.xlsx
10.8 KB Conditional Formats with Custom Rules - Completed.xlsx
10.2 KB Conditional Formats with Custom Rules - Extra Practice.xlsx
Creating custom conditional formatting rules based off formulas.
When to use
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 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
- 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
- The Green and Red rules do not change the font, but all rules change the fill colour
- An element cannot be changed once set
- The green rule set the fill (>75000), then the yellow rule (>50000) can’t change the colour, but it can change the font, so it does resulting 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.