How to do the Conditional Formatting in MS Excel !!

CONDITIONAL FORMATTING

What is Conditional Formatting?

Conditional Formatting is a feature in many spreadsheet applications that allows you to apply specific formatting to cells that meet a certain criteria. It is most often used as color-based formatting to highlight, emphasize or differentiate among data and information stored in Spreadsheet.

What are the Advantages of Conditional Formatting?

• Conditional Formatting is the better way to visually highlight important information in the worksheet.
• It can be used to highlight cells as per Pre-defined Rules. You can use various color coding or icons to highlight current status, blank cells etc.
• The formatting in our document will automatically Update after any change in our Data.  
• It helps you to identify the cells faster. You can easily identify the data.  Suppose you have to make any data set with monthly sales figure of particular store and wish to see what items are good for your business. We have made a table of that data and used Conditional Formatting. Highest figure as green color and gets lighter as the sales is decreasing and lowest with red.

In this way we can easily know about the higher sales or the lower sales. 

How to Use Conditional Formatting?

• To start the Conditional Formatting you first need to open the MS Excel.
• After Starting the MS Excel you need to first draw the required Table in which you can do the Conditional Formatting.
• After Completing the table > Go to Home Tab > In Styles you have to select Conditional Formatting.
• Click on Conditional Formatting, a table displayed with option:

Ø Highlight Cells Rules

Ø Top/Bottom Rules

Ø Data Bars

Ø Color Scales

Ø Icon Sets

Ø New Rules

Ø Clear Rules 

ØManage Rules.

Each one has different Uses as follows:

  Ø Highlight Cell Rules

We can highlight the cell According to their Values.

 i.e. Greater than any value,

 Less than any value, 

Between any two values, 

After or before any value.etc.

  Ø Top/Bottom Rules

We can highlight the cell According to their Values.

 i.e. Top10 items, 

Top 10 %, 

Bottom 10 items, 

Bottom 10%, 

Above Average,

 Below Average.

Ø Data Bars

We can show our values more representative with the help of Data Bars. We can show them with from Highest to Lowest with the help oh the Bars. We can understand the Data in a better way.

Ø Color Scales

We can show our values more representative with the help of Color scales. We can show them with from Highest to Lowest with the help of the Color Scales. It helps us to uderstand the data in a better way.

Ø Icon Sets

We can set the icons in front of the Values to show them in a unique way or can make them easy to understand.

Ø New Rules 

We can make new rules for our tables.

When we Click on New Rules a dialog box displayed named New Formatting Rules. You have to fill the options according to your need, then click OK.

Ø Clear Rules 

We can clear the rules from our tables. 

Ø Manage Rules 

We can Manage the new rules. We can delete or Insert New Rules.

• Select the Any One Options from the followings and Apply it to your Document or Table.
• Conditional Formatting applied to your Document.
• Now it makes your Document more Representative and Easy to understand.

Properly Conditional Formatted Table

Writer: Mr. Krishan Kumar Saini

Today we have learnt about How to do the Conditional Formatting in MS Excel. Hope this lesson is helpful for you.