Welcome to Excel Jet Consult blog post. In this post, I’m going to walk you through how to create advanced conditional formatting using the new rule and then use the checkboxes to turn of and on the highlighting of the rows. Let’s get started.
Video
Sample Data
In the picture below, we’ve got a sample sales data with handful of columns. We want to apply the advanced conditional formatting to highlight the entire rows with green, yellow and red fill colors when Sales Amount is greater than or equal to 50000, less than 50,000 and greater than or equal to 25,000 and less than 25,000 respectively.
Advanced Conditional Formatting
To achieve this, we are going to use the AND logical function that requires that all the argument of the AND function must be true.
Green Color: Sales Amount Greater Than of Equal to 50,000
- To apply the Conditional Formatting, Select all the data excluding the headers.
- In the Conditional Formatting drop-down of the home tab, select New Rule
- In the New Formatting Rule dialogue box, select use a formula to determine with cells to format rule
- In the Format values where this formula is true box, execute this formula: =AND($D5,$C5>=50000)
- Click Formatting and choose Green color in the Fill and Bold and Italic and White Font in the Font tabs. We can see preview of the formatting
- Click OK
Yellow Color: Sales Amount Greater less than 50,000 and greater than or equal to 25,000
- In the Conditional Formatting drop-down of the home tab, select New Rule
- In the New Formatting Rule dialogue box, select use a formula to determine with cells to format rule
- In the Format values where this formula is true box, execute this formula: =AND($D5,$C5<50000,$C5>=25000)
- Click Formatting and choose yellow color in the Fill and Bold and Italic font in the Font tabs.
- Click OK
Red Color: Sales Amount Greater less than 25,000
- In the Conditional Formatting drop-down of the home tab, select New Rule
- In the New Formatting Rule dialogue box, select use a formula to determine with cells to format rule
- In the Format values where this formula is true box, execute this formula: =AND($D5,$C5<25000)
- Click Formatting and choose red color in the Fill and Bold and Italic and white font in the Font tabs.
In the picture below, we checked the boxes for Sales Amount that is greater than or equal to 50,000 and the rows was highlighted in green. The same applies to the Sales Amount less than 50,000 and greater than or equal to 25,000 (yellow fill color) and Sales Amout less than 25,000 (red fill color).
