Mastering Conditional Formatting in Excel
Conditional formatting is a powerful tool in Microsoft Excel that allows you to apply data to cells based on their values. These features are useful for highlighting cell data based on conditona, identifying anomalies, and making data interpretation easier. In this article, we’ll review the basics of custom formatting and demonstrate its use with practical examples.
Use of Conditional Formatting
- Highlight important cells based on trends
- Use colored boxes or data bars to display data trends.
How can access in excel (according to 2019)
- Go to Home Tab
- Choose conditional formatting in style group
Real world Example: Sales Analysis
| Name | April | May | Jun | July | Aug | Sep |
|---|---|---|---|---|---|---|
| Mr Ramesh | 1200 | 1500 | 1300 | 900 | 1600 | 1700 |
| Mr Mahesh | 900 | 1100 | 1000 | 1200 | 1300 | 1400 |
| Ms Rekha | 1600 | 1700 | 1800 | 1500 | 2000 | 2100 |
1.Highlight Top Performers
To highlight sales person who have high sales:
- Select the range B2:G5 (April to Sep sales data).
- Go to Home > Conditional Formatting > Top/Bottom Rules > Top 10%.
- Choose a formatting style (e.g., red fill with dark green text) and click OK.
This will highlight Sep 2100 ms Rekha top 10% of sales figures in green, making it easy to identify high performers.
2.Highlight Underperformers
- Select the range B2:G5.
- Go to Home > Conditional Formatting > Highlight Cell Rules > Less Than (1000)
- Enter 1000 and choose a formatting style (e.g., Yellow fill with dark yellow text) and click OK.
This will highlight July 900 Mr Ramesh and April Mr Shyam less than 1000 of sales figures in green, making it easy to identify underformers.
like that can use this formatting for between, text, date, duplicate, for top, for below , average . also can create data bar,color scale
3. Create Custom Rules
- Select the range B2:G5.
- Go to Home > Conditional Formatting > New Rule.
- Select Use a formula to determine which cells to format.
- Enter Formula suppose you want to highlight more than 1000 and less than 2000 then =AND(B2>=1000, B2<=2000).
In this formatting this will color all cell which have less than 2000 or more than 1000.
4. Mange Rules
in this can edit format style color, rules
- select cell b2:b5
- conditional formatting
- manage rules
Best Way of Effective Conditional Formatting
- Select Specific range: Apply formatting to specific ranges to avoid unnecessary formatting and improve accuracy.
- Use Proper Formats: Ensure that the formatting you apply makes the data easily read.
- Combine Rules: It can apply for multiple rules to the same range for more visualizations.
Note : Test your formatting rules to ensure they work as expected.
Conclusion:
Click here for more online classes Playlist