Share in Facebook


03 September 2017

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
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 and Font Color
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.


Conditional Formatting Format Painter
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
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 Manager Rules Manager

Conditional Formatting Edit Formatting Rules
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
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...

5 comments:

Best Erotic Bondage Blindfolds Restraints said...

Best Erotic Bonage Blindfolds Restraint We strive to have a positive impact on small to medium businesses, customers, employees, the economy, and communities. Surjmor bring together smart, passionate builders with different backgrounds and goals, who share a common desire to always be learning and inventing on behalf of our customers. With all the family of business that are a part of us, our goals is providing customers with the best service possible.

https://xxxtoys.top/

Mallela said...

Thanks for posting the blog is very good.ai courses in kolkata

Mallela said...

Thanks for posting the best information and the blog is very good.ethical hacking institute in ahmedabad

Mallela said...

Thanks for posting the best information and the blog is very good.machine learning course in kolkata

ONIL CHRISTIAN said...

Wonderful! I really love your post and what you share with us is updated and quite informative. The visa for Turkey is completely open now and you can take benefits of the Turkish visa facility and Explore the Turkey like a local .