Conditional formatting in EXCEL

Conditional formatting is powerful functions in Microsoft excel. it is underutilized function in Microsoft excel.it converts raw data in to manageable data which visualized by different colors and icons. For Example lowest and highest performance area of company which is highlight by simply using conditional formatting. Conditional formatting make your work easy and eye catching wherever data needs 

WHAT IS CONDITIONAL FORMATTING??

Conditional formatting is automatic function which highlight the conditional values or rules in different color, icon, font style etc.Manually formatting of data will make errors and other problems while applying conditional formatting makes our work too much easy.

WHY USE CONDITIONAL FORMATTING?

1. Conditional formatting highlights the specific data as per condition  applied by following rules and values
2. you can quickly visualise the data you want as per condition given in different colors
3. by using the conditional formatting you can save your precious time because your hefty work is done by a simple condition based formatting 
4. Conditional formatting is more accurate and precise

HOW TO APPLY CONDITIONAL FORMATTING

1. Select Your Data Range
    Highlight the cell where you want to apply formatting

2. Go To Conditional Formating
    on the home tab locate Conditional Formatting button in the style group

3. Choose The Rule Type

HIghlight the cell rule: Highlight the values greater than less than or equal to a specified value.

    Highlight the cell rule: Highlight the values greater than less than or equal to a specified value.
    Top/Bottom Rules: Highlight the top 10 items, bottom 10 items

    Data Bars, color scale, icon sets: use visual aids to represent data trends
    Custom Formulas: create advanced rules using excel formulas

4. Set Formatting Options
    Define the colors, fonts, or borders for the highlighted cells.

5. Clock Ok
    Your selected cell will now reflect the conditional formatting based on your rules

Examples

 For a student how much students get more than 80 marks from allt he students than 
We have to use conditional formatting

We have this data and we have to find students with 80 or more than 80 marks

Than we use conditional formatting 

Step 1 Select the range

Step 2 Click on Conditional Formatting from the Home Tab Style Section

Step 3 Select Highlight Cells Rules than Select Greater than and write 80 and Select color combination you want to highlight

Here We can see that the every students who has more than 80 marks are highlighted

 Advanced Tip:

For greater flexibility you can use formulas to define conditional formatting by using New Rules

For that you have to 

select the range of data 

Got o Conditional Formatting=>New Rules=>Use a formula to determine which cell to format

Enter the formula

Choose your desired formatting

Common pitfalls to avoid

Applying rules to wrong range
Always double check your your selected range to avoid unexpected results

Too many rules:
Overloading your sheet with multiple rules can slow down performance
Prioritize clearly

Not understanding formula based rules:
Formula needs to start with equal to (=) sign and reference the correct cell