In-built Conditional Formatting Rules In Excel – MS Excel Conditional Formatting Tutorial Part 3

So, we learnt about applying conditional formatting to highlight cells based on criteria in previous post, in this post we will explore the conditional formatting option in greater detail.

We will talk about each of the below rule of conditional formatting.

Highlight Cells Rules

As explored in the previous post the “Highlight Cells Rules” option can be used for highlighting values based on specific criteria. This option is often used for highlighting values greater than/less than or equal to a specific value. This is also used for highlighting redundant or duplicate values and for highlighting a specific text in our data.

[Enlarge the image by clicking on it for a better view]

conditional formatting in excelTop/Bottom Rules

This option is used for highlighting the top items in a list of items. It is also used for highlighting values which fall above or below the average value in a list.

conditional formatting in excelFor e.g. Let us suppose that we have the sales value (in INR) of 10 products. We need to highlight the top 10 products in terms of sales. We can highlight the top 10 products with maximum sales using Top/Bottom rules.

conditional formatting in excelData Bars

Data bars are nothing but small colored bars which reside inside the cell. Data bars are used in conditional formatting mainly for visual appeal. The length of a data bar represents the value inside the cell, higher the value longer the data bar.

conditional formatting in excelLet us consider our previous example in order to understand data bars. We can insert data bars in the field “Net sales”. The products with higher sales will have longer data bars.

conditional formatting in excelColor Scales

Color scales are used for representing values as per two/three gradients of colors. Normally the values are categorized and shaded as per the top values, intermediate values and bottom values. We can also choose the option “More Rules” here and choose our colors.

conditional formatting in excelFor e.g. let us consider the previous example. In this example we can choose a color rule to highlight different sales values of different products.conditional formatting in excelThe products with top sales are colored “Dark Orange”.

Icon Sets

Icon sets option is used for inserting icons inside cells with values. Green icons represent the highest values whereas “Yellow” and “Red” icons represent “Intermediate” and “Lowest” values respectively.conditional formatting in excelLet us consider our product sales example. If we use icon sets in this example the highest sale values in the net sales column will be represented by a green icon.conditional formatting in excel

Above conditional formatting rules are in-built rules that are already present in MS Excel. It is a great deal to know them as they are easy to use and come really handy. In our next tutorial, we will learn about creating new conditional formatting rule. We will also learn about managing and clearing conditional formatting rules.

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 class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">