About this lesson
Leveraging the logic of the IF() function to drive conditional formats based on formulas.
Download this lesson’s related exercise files.Formula based conditional formats.xlsx
36 KB Formula based conditional formats - Completed.xlsx
Formula Based Conditional Formats
Creating custom conditional formatting rules based off formulas.
When to use
To create rules that are more complicated than those allowed via the standard “highlight cells” rules.
Creating Custom Rules
- Select your data starting in the top left corner of your data set
- Go to the Home tab --> Conditional Formatting --> New Rule
- Choose “use a formula to determine which cells to format”
- Enter a formula that tests your data
- Click Format
- Configure the formatting as you’d like to see applied
Modifying Custom Rules
- Select any cell that holds the conditional formatting rule
- Go to the Home tab --> Conditional Formatting --> Manage Rules
- Locate and select your rule, then click Edit
- Make your modifications as desired
Hints & tips
- Use F4 to toggle the absolute/relative state of your formulas while you are building them
- When editing rules, you can apply them from the conditional formatting rules manager
- Always select your data from the top left when you are building rules, as it makes it easier to set up relative/absolute references correctly
- Many rules can be set up that overlap
Lesson notes are only available for subscribers.