The Ultimate Guide To Excel Conditional Formatting [7 Tricks]

Conditional Formatting is one of the most excellent options in excel. I frequently use this feature in my excel works. Lots of hidden conditional formatting tricks are available. We will see all those in this article.

Conditional Formatting Types

You can see different types as Highlight cell Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets.

Highlight Cells Rules and Customized rule using New Rule is effective one.

Highlight Cells Rules

We can use this feature for quick work. MS Excel provides this option to use very quickly during our work. 

In excel, if I have a binary column like Yes / No, then I want to highlight only Yes; these options are Very Useful.

1. Select a Column which you want to apply.
2. Select Highlight Cells Rules under Conditional formatting.
3. Now Click Select that test contains.
4. Type text as Yes or Male or whatever you want.
5. You can select default formatting or custom format.

Excel Conditonal Formatting Examples

Excel conditional formatting based on another column

Previously we have seen simple conditional formatting. Now we will see Excel conditional formatting based on another column.

Here we will use the Formula to highlight the values like; if the cell value is greater than apply this format like that.

1. Select the column that you want to apply conditional formatting.
2. Select New Rule under conditional formatting.
3. Now select “Use a formula..”

Type the Formula in the text box. I typed $A1>40

Explanation: Here, I am formatting the “C” column when the A column value is greater than 40. 

Excel Conditional Formatting 3 Colour Scale Percentage

To understand the distribution of numbers, we can use three colour scale percentage. 

1. After opening Conditional formatting, select Format all cells based on their values.
2. Following select format style as 3 Colour Scale
3. In type, select percentile and select the value based on how you want to see the value.
4. Example: 10% percentile fewer values in Dark Red and more.

Another 3 Color Values using Formula. Here we will highlight if the value is less than 30 then display green colour. For the values more than 30 and less than 50, then display red. And greater 50 values are another different colour.

=AND(($R1>39) , ($R1<50))

Conditional Formatting If Cell Is Blank

To do Excel Conditional Formatting Blank Cell, you need the below Formula to apply in the formula section, and the remaining steps are the same.

=$F1="" or =ISBLANK($F1) 

1. First, select the column in which you want to apply conditional formatting.
2. Select Use a Formula to determine which cells to format
3. In Formula section type the formula =$F1=”” or =ISBLANK($F1)

Conditional Formatting Comparing Two Columns

In this tutorial, we are planning to compare two columns using conditional formatting. In the first example, we will see numeric and text value. In the second example, we will make text and text comparison.

Scenario: User want to highlight the second column based on the first column, like if the first column value is more than 4, then highlight the cells in the second column.

1. First, select the column in which you want to apply conditional formatting.
2. Select Use a Formula to determine which cells to format.
3. In Formula section type, the Formula =$A2>4
4. In format, select the format how you want to fill. 
5. Next, Click Apply and Okay.

Conditional formatting based on date

Scenario: You have an Excel which has date and downtime Yes or No for one year. In that Excel file, you need to highlight the cells with green colour on Saturday.

Here we are trying a conditional format based on date and excel conditional formatting based on another cell’s date.

As usual, the first three steps are the same, and only you need to update the Formula. 

=WEEKDAY($A1)=4

4 is Wednesday here; based on the requirement, you can change that number and column.

Excel Conditional Formatting Formula Greater Than and Less Than

Scenario. Excel sheet contains age and disease details. You got a task highlighting the disease whose Age is more than 45 in green colour and less than 45 in Red colour.

To complete this, you need to create two conditions like below. Here I added two Formula. 

=$B1<3.1 and =$B1>3.1

One Formula is greater than 3.1 and less than 3.1 with two different colours.

Find Duplicate Value using Excel Conditional Formatting Formula.

Scenario: Highlight the second occurrence of duplicate values in the excel sheet. So, we need to ignore the first occurrence.

Now let see the steps.

The formula section applies this Formula and design or sets your colour format based on your needs.

Here I highlighted it in yellow colour.

If you want to highlight the second occurrence, then you need to tweak the Formula a little bit.

=COUNTIF($B$1:$B1,$B2)>1

So, from the above Formula, now you understand the third occurrence. 

=COUNTIF($B$1:$B1,$B2)>2

Now you may have a doubt about how to highlight only the third occurrence, then Formula is 

=COUNTIF($B$1:$B1,$B2)=3

Conditionals Formatting Formulas And OR Formula

Scenario: You have excel data with Age and Blood pressure, and you want to highlight the value of more than 120 and less than 150.

As usual, select the column and go to conditional formatting and select the Formula to apply the formatting.

=AND($C1>120, $C1<150)

Now apply and click okay.

Now let see the OR condition and how to use it.

Scenario: You want to highlight only the value 220 OR 170 blood pressure. 

The above scenarios are based on one column and its values. Now we will see how to create a conditional formatting based on two columns using AND condition.

Scenario: Highlight the cell where blood pressure is more than 219 and job type is 1.[Two Columns]

=OR($C1>219, $B1=1)

Let see how the complex Formula like AND OR condition in the same Formula.

=OR(AND($C1>219, $B1=1),$A1>60)

Explanation If Age is 60 OR blood pressure is more than 219, and job type is one, highlight or format the cells.

Conditionals Formatting Formulas

AND Conditions	=AND($C1>219, $B1=1)
OR Conditions	=OR($C1>219, $B1=1)
Multiple Conditions	=OR(AND($C1>219, $B1=1),$A1>60)
Duplicate Values	=COUNTIF($B$1:$B1,$B2)>1
Date Conditions	=WEEKDAY($A1)=4
Equal to	=$B1=100
Greater Than	=$B1>100
Less Than	=$B1<100
Between Two Values	=AND($C1>219, $C1<221)

How to Remove the Conditionals Formatting in Single Column

We have applied conditional formatting, now we have done with our analysis, so we need to remove all formatting.

1. Select the column that you want to remove formatting.
2. Click Conditional Formatting in Home Screen
3. Select Clear Rules from Selected Cells

Conclusion

This article has shared the complete definitive guide for Excel Conditionals Formatting with Examples and formulas. If anything missed or any issue with conditional formatting, please feel free to contact us through comments.