What is Conditional Formatting?

Not everyone knows about conditional Formatting in Excel, Specifically the beginners of Excel, The below learning is for Newbies, But Wait!! Advanced Users ~~ You may also know some additional information by reading this Posts or Watching the video clips – Go Ahead Click the Video

It is a mean by which we can format the data in each cell with respect to the values in it or with respect to certain conditions. You can chose from the ‘Home’ menu as shown below.

Highlight Cell Rules

It is a useful option to highlight the cells with respect to its values subjected to certain conditions. We can set conditions such as Greater Than, Lesser Than, Between, Equal To, Text that Contains, A date occurring, and Duplicate Values. Let us select the cells whose values are equal to zero.

How to use Highlight Cell Rules of excel?  The steps involved:

Step-1:  Select the cells for which the rule has to be applied.

Step-2: Go to Conditional Formatting and Go to Highlight Cells Rules.

Step-3: Choose ‘Equal to’ option from the list of conditions and type the value as ‘0’.

Alright, I know you are already Feeling Sleepy Reading My Post! Haha! Lets Get on with a video Tutorial then.

I hope you Enjoy the Video above and Had good input on that, Dont Wait, Go ahead and Subscribe to My channel if you like my Video, You will Never Regret!

Top/Bottom Rules

These rules enable us to pick a set of top values or bottom values, for example, top 3 or bottom 10 values. Some of the functionalities that we can perform here are finding the Top 10 items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average, and Below Average. Let us pick the top 3 values from the sales data available.

How to use Top/Bottom Rules of Excel? The steps involved.

Step-1: Select the cells for which the rule has to be applied.

Step-2: Go to Conditional Formatting and Go to Top/Bottom Rules.

Step-3: Select the ‘Top 10 Items’.











Step-4: Specify the count for the top list and also select the color in which the data has to be presented.






Data Bars

This is the representation of the value of each cell in the form of bars.  The bars are drawn inside each cell in the horizontal direction to denote how valuable the cell data is when compared to the other cell’s data. We also have the option to select a color of our choice to the bars. If we feel like the presence of bars and data gives an awkward appearance, we can have the option to ‘Show Bar Only’ under the ‘More rules’ of the data bars menu.


Here, let us have a look on our data in form of data bars with an orange color.

How to use Data Bars of Excel? The steps involved:

Step-1: Select the cells.

Step-2: Go to Conditional Formatting and go to Data Bars.

Step-3: Choose the color (Here, we have chosen the orange color).


Let’s Have a Look at another Interesting Video !

Color Scales

This is a colorful notion of our values in the cells and we have many choices of colors for the data to be represented. For example, Let us consider the scale Green-Yellow-Red. Here, the cells with comparatively higher values are presented in Green and we can see the color variation of the cells with respect to the value in it. The cells with values neither higher nor lower are presented in Yellow and the lower ones are in Red. We have so many color combinations such as Red-Yellow-Green, Blue-Yellow-Red, etc.  We can even choose a two color scale such as Green-Yellow, Red-Yellow, etc or even a single color scale such as Green, Blue, Red, etc.

How to use Color Scales of Excel? The steps involved:

Step-1: Select the cells.

Step-2: Go to Conditional Formatting and go to Color Scales menu.

Step-3: Choose the color scale combination (Below is the color scale with the Green-Yellow-Red combination).

Icon Set

It is another way of representing the values in cells with a set of icons. These icons can indicate the increase or decrease in the range of the values. It even shows an alert icon when there is no improvement in the data, especially, when we deal with sales.

How to use Icon Set of Excel? The Steps involved:

Step-1: Go to Conditional Formatting.

Step-2: Go to Icon sets and select the icon from the list.

As shown below, we have lots of choices in these sets and it can help us to a great extent to analyze the sales reports.

Set a New Rule

Apart from the ones we have seen earlier, we are also allowed to choose few new rules and are available as shown below. This option is extremely useful when we format the cells based on pre-determined values. For example, we can format only the top or bottom ranked values, or we can determine which cell to format using a formula.

How to use a New Rule on an Excel sheet? Steps involved:

Step-1: Go to Conditional Formatting.

Step-2: Select the ‘New Rule’ option.

These set of rules are also available under the ‘More Rules’ option of all of the Conditional Formatting menus such as Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets.













How to Clear Conditional Formatting Rule?

The rules we had set is no longer required now and we are in attempting to create newer ones. In such a case, we can always clear the already set rules by three ways.

Option-1:  We can use the ‘Clear Rules’ option from the Conditional Formatting menus. It helps to clear rules either for selected cells or for the entire sheet. We should be very careful while choosing it for the entire sheet as it may result in the deletion of necessary ones.

Option-2: Under the ‘Editing’ menus, we can choose the ‘Clear’ to clear only the Formats, Contents, or Comments






Option-3(Short-cut):  Look at the Clear rule YouTube video here.

Step-1: Select the cells in which you wish to clear the Formatting rules and other styling options.

Step-2: Press Alt+E+A+F keys to clear the rules.

Just try it by yourself and do represent your data more effectively! We do welcome your comments or suggestions. Is there a still better way to use it? Please let us know!

I hope you enjoyed it and Lets say see you! Until I bring you another blog post to enrich your knowledge!

Have a Great Day Peeps!