Conditional Formatting in Excel

Conditional formatting in Microsoft Excel is used for providing formats on data available in your sheet and make them representable. Conditional formatting helps in highlighting your data with predefined formats. You can even provide your choice of formatting to selected text or cells by your choice of format options. It is capable of easily spotting patterns and entries in your data to format them. with the help of using its predefined features of bars, colours and even icons. it helps in highlighting your data visually and also marking out important values from it.

Conditional formatting is used in many scenarios where we want to analyse our data with the help of visual representation.

Types of Formatting

Conditional formatting provides five different types of format rules for our data.

Highlight Cells Rules: In this formatting style our data is highlighted by formatting based on different rules like value is greater than or less than a certain value. It might be equals to a certain number even if it can between certain numbers. You can even highlight dates occurring between certain days. Or even it can be duplicate or unique values.
Top / Bottom Rules: As the name suggest it helps formatting to highlights top values or bottom values from the selected data. It can even highlight above-average and below-average values.
Data Bars: In this formatting, it checks if the cell is filled with colours based on the value available in it. The values are compared with entire data selected and the colour is filled based on that data.
Colour Scales: This format divides entire data into different segments. these segments are further denoted by certain colours. Any value which occurs in a particular segment will automatically be highlighted by the colour assigned for the segment.
Icon Sets: This format is similar to colour scales. The only difference is that here segments are denoted by some sets of icons. If any value appears in a particular segment they will automatically highlight by Icon assigned for that segment.

New Rule

Sometimes there are situations where we do not want to apply format based on predefined designs explained above. For this, we can also apply formatting based on our own requirement with the help of another option of conditional formatting called "new rule". With this option, we can choose our choice of conditions which will help to format data as per our requirement. We can even mix different formatting styles and conditions to highlight important values. This way our data will become more appealing visually.

Manage Rules

There are times when we want to modify some rules which we have already created and applied to the current data. Sometimes you want to remove few formattings and apply new ones. These type of changes are done by another option of conditional formatting called "Manage rules". This option is very useful when we want to modify any previous formatting conditions. We can even provide some new formats based on values, formulas, or even data in reference to other data.

Clear Rules

Now when we have learnt to apply formatting to our selected data for visual highlighting, we also need to remove it from certain areas of the sheet. For this removal, we can use another option present in the same conditional formatting tool, this tool is named as "clear rules". As the name suggests it clears rule with two different types. You can clear rule from the entire sheet or even you can remove rules from the selected cells only.

For other excel tutorial, you can choose the below topic:

Conclusion

This way conditional formatting helps to provide formatting based on conditions provided by the user and make data visually more appealing and highlighting important values out of it.

I hope you like the article. Do comment and suggest if you need to learn something more like that. Your comments will encourage me to do a couple of more works like that.

Blogs by Abhishek Paul

Search This Blog