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.
- Introduction To Conditional Formatting In Excel
- How To Apply Conditional Formatting In Excel
- In-built Conditional Formatting Rules In Excel
- How To Create A Conditional Formatting Rule In Excel
- Useful Applications Of Conditional Formatting In Excel
We will talk about each of the below rule of conditional formatting.
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]
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.
For 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.
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.
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.
For 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.The products with top sales are colored “Dark Orange”.
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.Let 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.
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.