The Ultimate Guide To Excel Conditional Formatting [7 Tricks]

by

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.

Excel Conditional Formatting

Highlight Cells Rules

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

Conditionals Formatting Types

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

Excel Conditional Formatting
  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..”
Excel conditional formatting based on another column

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

Excel conditional formatting based on another column

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. 

Excel Conditional Formatting 3 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))
Excel Conditional Formatting 3 Colour Scale Percentage

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) 
Conditional Formatting If Cell Is Blank |
  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.

Conditional Formatting Comparing Two Columns
  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 based on date in another cell

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.

Find Duplicate Value using Excel Conditional Formatting Formula
=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.

Conditionals Formatting Formulas And OR Formula

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

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

Conditionals Formatting Formulas And OR Formula

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

How to Use Conditional Formatting in Excel?

To Use conditional formatting first, you need to select the column you want to apply conditional formatting. After selecting the column, click the conditional formatting, then click highlight rules or New rule. https://www.e-coli.net/wp-content/uploads/2021/03/excel-Conditionals-Formatting–1024×538.gif

How to Remove Conditional Formatting in Excel?

To remove conditional formatting in a column, select that column and select conditional formatting, then clear rules and Clear rules from selected cells. To clear all conditional formatting, set “Clear Rules from Entire Sheet.”

How to Copy Conditional Formatting in Excel?

Select the cell or column with conditional formatting, with format painter, then apply that formatting to the destination column.

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.

Leave a Comment