Conditional Formatting in Excel Formula, Icon Sets, Find Duplicate or Unique value and Edit or Modify or Remove Conditional Formatting

Hi friends,

After discussing about basic Conditional formatting I think we should learn few more Conditional formatting features. Formula driven Conditional formatting is one of the awesome features in Microsoft Excel.

In today's article we will learn about the advanced uses of Conditional formatting with few functions and will use the Icon Set like, Arrows, Flags, Street Light symbols, colored circles etc.

Conditional Formatting Icon Sets

Using Formula in Microsoft Excel Conditional formatting

Using Formula in Microsoft Excel Conditional formatting is very simple click Format only cells that contain' in New formatting rule window and type your formula in the Edit the Rule Description box as shown in the below picture. You can use any formula in this box as per your requirement.

Conditional Formatting Using Formula To Decide Cell Fore Color and Font Color

How to apply Conditional formatting in all cells

To apply Conditional formatting in all the cells in Excel you need to select the cell where you have applied the Conditional formatting and then click on the Format Painter button in the Home Tab as shown in the below picture and apply it to the require cells.

Format Painter

In Microsoft Excel you will find few Icon Sets under Conditional Formatting > Icon Sets as shown below. If you select a cell containing data and hover over these icons you will find these icons are displaying in the selected cell. Let us understand the implementation of Icon Sets.

Conditional Formatting Icon Sets

Use of colored Arrows from Icon Sets in Conditional formatting

To use the Icon Sets select any one cell in your data table where you need these icons. Now there are two ways to use Icon Sets in Excel.

    1.  Go to Conditional formatting > New Rule, New Formatting Rule Window will be displayed or

    2.  Click Conditional formatting > Icon Sets, a icon will be displayed in the selected cell, now go to Conditional formatting > Manage Rules (At the bottom of the drop down list, I prefer to use this method)

Now, if you use the First method then Steps are,

    1.  Select 'Format all cells based on their values'

    2.  You will find one drop down list Format Style, as shown in the below image. Here you will find few options namely

a.  2-color scale

b.  3-color scale

c.  Data bar

d.  Icon Sets

    
       3.  Select Icon Sets and at the bottom of the window you will find another Drop down list Icon Style, select your icon as per your requirement.

Now, if you use the Second method then Steps are,

1)  go to Manage Rules from Conditional formatting button in the Home tab

2) Conditional formatting rules manager will appear, click on Edit Rules button

3) Now in the Edit the Rule Description head put your logic as per your requirement and Click Ok, done. Please follow the below images.

Conditional Manager Rules Manager

Conditional Formatting Edit Formatting Rules

If you practice it few times you will be able to master it very easily, if you need video tutorial, please click here to view this in YouTube.

How to delete Conditional formatting

Follow the steps :

   a)  Go to Conditional Formatting > Clear Rules

a) Click on Clear Rules from the selected cells if you want to delete conditional formatting from specific cells or

b) Click on Clear Rules from Entire Sheets if you want to clear it from the entire sheet.

Conditional Formatting New Formatting Rules

How to Find Unique or Duplicate in Excel

Conditional formatting is a very good tool to find out the unique or duplicate values in Excel Columns.

As show in the below picture, go to Conditional formatting > New Rule, click 'Format only unique or duplicate values'.

In the Edit the Rule Description section select unique or duplicate as per your requirement, format the cell color or font by clicking Format button. Click OK and you are done.

Thank you for reading... There are more advanced methods in Conditional formatting which I'll discuss latter in another article... Keep reading and asking questions...