Use Conditional Formatting to change the cell color based on criteria


Conditional Formatting is great feature to easily identify the cells which met specified criteria. Condtional formatting highlight the cells which differs from the huge range of data. It can also be  useful for visulizing data using data bars, Color Scales and Icon Sets based on criteria.

Use this simple steps to apply Conditional formatting.

1. Select the data range of cells where want to apply conditional formatting.(Change of background color based on given criteria)

1. select data

2.  Go to Home-> In Styles group->Select Conditional Formatting

2. Conditional Formatting

3 Conditional Formatting have many default option to quickly set your criteria.

1. Highlight Cell Rules:  Use this to highlight cells based on criterial like, “Greater than value”, “Less than value”, “Between two value” ,”Equal To =”, “Text that contains particular value”, “Highlight Duplicate Values” or “Specified days like This week or next week”.

For example, here we have selected the criteria “Greater than”. Click on More Rules to set your criteria.

3. Highlight cell rules

It will pop-up the Greater Than dialog Box, specified your criteria and choose the color to highlight cells.

3.1 Set criteria for Highlight cell rules

2. Top/Bottom  Rules: Highlight cells for Top/Bottom 10 values or Top/Bottom 10 % or to identify the Above Average or Below Average value. Click on More Rules to set your criteria.

4. Top-bottom rules

For example, Here it will highlight Top 10 values. You can change it to 5 to highlight Top 5 values.

4.1 Top-bottom rules

3. Data Bars: Data Bars are really useful to visulize data trend. Click on More Rules to set your criterial.

5. Data bars

4. Color Scales: Same way Color Scales can be used to display cells with different color based on its value. Click on More Rules to set your criteria.

6.color scales

5. Icon Sets: Set the different icons like arrows, shapes, indicators or ratings for your value.  Click on More Rules to set your criteria.

6. Icon-sets

2,510 total views, 2 views today

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>