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)

2. Go to Home-> In Styles group->Select 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.

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

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.

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

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

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.

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