December 4, 2020

How to do conditional formatting in Excel

Let’s say we have the below table containing certain number of students and their rank in an University entrance exam.

Now we need to check if there is any duplicate rank. We can use conditional formatting to search the identify the duplicate ranks as follows.

  • Select the data in Rank column.
  • In Home tab, click on ‘Conditional Formatting’ in ‘Styles’ group.

  • Go to ‘Highlight Cell Rules’ and then ‘Duplicate Values’

  • Here you can select the color combination as per your choice to highlight the cells containing duplicate values and click OK.

  • Below are the duplicate cells highlighted with rank 77.

  • Now as we have got the duplicate values highlighted using conditional formatting, now it’s easy to count the number of duplicate values in this column.
  • First select the table header and click Filter available in Home tab.

  • Now select ‘Filter By Color’ In the filter option available in rank Column. Select the desired color option you want to filter with based on cell color or font color and click OK.

  • Now only the rows with duplicate rank will be displayed as follows. Now select the cells and right click on the status bar of this worksheet and select Count. In this way you can see the count of the duplicate cells in the status bar.

  • In this way we can use conditional formatting on various conditions. Below are couple of examples of conditional formatting on some predefined conditions in excel.
  • Like on the same data set if we need to highlight the top 10 ranks, first select the data in Rank column. 
  • Then in Conditional Formatting, go to ‘Top/Bottom Rules’ and then select ‘Top 10 Items’.

  • Here you can select the numbers if instead of 10 you need some other count from top and select the highlight color and click OK.

  • See below the top 10 ranks have been highlighted.

  • Now select the data in Rank column and in Conditional Formatting go to ‘Data Bars’ and choose the style you need to create the data bars based on the rank.

  • You can also create New Rule and set your conditional formatting rule as follows.

We will learn couple of more important usage of conditional formatting by exploring some more easy examples.

In the below example we will show how to find the numbers greater than a specific number with conditional formatting. In the following table we have the list of ranks for certain number of students. 

  1. Now select the ranks in cell B2:B18 and go to Conditional Formatting in Home tab
  2. Navigate to ‘Highlight Cell Rules’ and then click ‘Greater Than’

3. Now in the pop up window type the desired number. Here we have put 45 as we need to identify the Ranks greater than 45. As you can see the respective cells have been highlighted in the Rank column of the table.

4. Now let’s  identify the ranks between 25 to 45. We need to again select the cells B2:B18 and go to Conditional Formatting in Home tab

5. Navigate to ‘Highlight Cell Rules’ and then click ‘Between’

6. Now in the pop up provide the lowest and highest numbers and this time we have chosen different formatting options to highlight the cells. As you can see below the ranks between 25 & 45 have been highlighted with yellow.

7. Now in the same way we will identify the ranks lesser that 25. select the ranks in cell B2:B18 and go to Conditional Formatting in Home tab

8. Navigate to ‘Highlight Cell Rules’ and then click ‘Less Than’

9. Now in the pop up type 25 and select a different formatting option so that the cells can be identified separately. Now as you can see below the ranks less than 25 have been highlighted in Green.

10. Now on the same example we will show you some conditional formatting techniques on text string. So far we have done conditional formatting on numbers. Now let’s say we need to identify the student names that contain the string “ie”. The first select the cells A2:A18 and Click Conditional Formatting in Home tab

11. Navigate to ‘Highlight Cell Rules’ and then click ‘Text that Contain’

12. Now in the pop up type “ie” as follows. This characters are case insensitive, it means if you type in Capital letters as “IE” then also it will identify the cells that contain the given string irrespective of which case it exists in cell. Here as you can see below the respective student names containing the string “ie” are marked with red according to the selected format.

13. As we have already mentioned above in the example of find and count the duplicates in a column in excel, here also you can filter by color as follows.
First apply filter on the column header and then select ‘Filter by Color’ option for the filter in rank Column. As you can see the list of available cell color and font color has come up. Now you can filter based on cell color or font color as per your requirement.

14. Let’s say we need to filter only the ranks less than 25 which we have highlighted with green so we selected the Green in ‘Filter by cell Color’. Below is the list of students with rank less than 25.

15. Now let’s see another example of doing Filter by Font color. As we have identified the student names containing ‘ie’ and applied conditional formatting to change the font color as red for the respective cells, now let’s select ‘Filter by Color’ on the Student’s Name column and select ‘Filter by Font Color’ as red.

16. See below, only the rows for the student names containing ‘ie’ have been appeared.

Now to clear all filters on the entire table you can again select the column header and click Clear Filter in Sort & Filter option available in Home tab.

We can also click the Filter icon there to remove the filter arrow in the column header.

Now as the last step we will learn how to remove all conditional formatting applied in a table.

  • Click ‘Conditional Formatting’ in ‘Styles’ group available in Home tab.
  • There you will get the option as ‘Clear Rules’. Navigate to that. You can choose any one from the 2 options based on your need.
  • If you need to remove conditional formatting rules for a group of cells then first select those cells and then go to ‘Clear Rules’ in ‘Conditional Formatting’ and choose ‘Clear Rules from Selected Cells’ here.
  • If you need to remove conditional formatting rules from the entire excel worksheet then select ‘Clear Rules from Entire Sheet’ here.

  • Here as we have prior selected the entire table, we can click any one option to remove the conditional formatting rules from our entire table. And as you can see below all conditional formatting colors have been disappeared from our table.

Please Note : The cell colors and font colors applied by conditional formatting cannot be just removed or reset or changed from the Font Color and Fill Color options available in Font group of Home tab.

The highlighted colors applied by conditional formatting can only be removed by clearing the conditional formatting rules as demonstrated above.

Leave a Reply

Your email address will not be published. Required fields are marked *