📊 Excel Basics #7 – Conditional Formatting
Conditional Formatting automatically changes the appearance of cells based on rules or conditions. It helps you quickly identify trends, outliers, duplicates, and important values.
📌 What is Conditional Formatting?
Instead of manually highlighting data, Excel formats cells automatically when they meet a specific condition.
Go to: Home → Conditional Formatting
📌 Common Rules
✅ Highlight Cells Rules
• Greater Than
• Less Than
• Between
• Equal To
• Text That Contains
• A Date Occurring
• Duplicate Values
Example:
Highlight all sales greater than 10000.
📌 Top/Bottom Rules
• Top 10 Items
• Top 10%
• Bottom 10 Items
• Bottom 10%
• Above Average
• Below Average
Example:
Highlight the top 5 highest-performing employees.
📌 Data Bars
Display colored bars inside cells to compare values visually.
Example: Higher sales → Longer bar | Lower sales → Shorter bar
Perfect for comparing performance without creating charts.
📌 Color Scales
Apply gradient colors based on values.
Example:
• Green → Highest values
• Yellow → Medium values
• Red → Lowest values
Useful for marks, revenue, or KPI analysis.
📌 Icon Sets
Display icons based on cell values.
Examples:
• 🟢 Green Arrow → High
• 🟡 Yellow Arrow → Medium
• 🔴 Red Arrow → Low
Great for dashboards and performance reports.
📌 Custom Formula Rule
You can create your own formatting rules using formulas.
Example: =A2>100
This highlights cells where the value is greater than 100.
💡 Real-World Examples
• Highlight overdue payment dates
• Find duplicate Employee IDs
• Identify sales below the monthly target
• Highlight the top 10% of performers
• Track project status using colors
✅ Best Practices
• Use colors consistently (e.g., Green = Good, Red = Attention)
• Don't overuse multiple formatting rules
• Keep formatting simple and easy to understand
• Review and manage rules regularly to avoid conflicts
Double Tap ❤️ For More
Conditional Formatting automatically changes the appearance of cells based on rules or conditions. It helps you quickly identify trends, outliers, duplicates, and important values.
📌 What is Conditional Formatting?
Instead of manually highlighting data, Excel formats cells automatically when they meet a specific condition.
Go to: Home → Conditional Formatting
📌 Common Rules
✅ Highlight Cells Rules
• Greater Than
• Less Than
• Between
• Equal To
• Text That Contains
• A Date Occurring
• Duplicate Values
Example:
Highlight all sales greater than 10000.
📌 Top/Bottom Rules
• Top 10 Items
• Top 10%
• Bottom 10 Items
• Bottom 10%
• Above Average
• Below Average
Example:
Highlight the top 5 highest-performing employees.
📌 Data Bars
Display colored bars inside cells to compare values visually.
Example: Higher sales → Longer bar | Lower sales → Shorter bar
Perfect for comparing performance without creating charts.
📌 Color Scales
Apply gradient colors based on values.
Example:
• Green → Highest values
• Yellow → Medium values
• Red → Lowest values
Useful for marks, revenue, or KPI analysis.
📌 Icon Sets
Display icons based on cell values.
Examples:
• 🟢 Green Arrow → High
• 🟡 Yellow Arrow → Medium
• 🔴 Red Arrow → Low
Great for dashboards and performance reports.
📌 Custom Formula Rule
You can create your own formatting rules using formulas.
Example: =A2>100
This highlights cells where the value is greater than 100.
💡 Real-World Examples
• Highlight overdue payment dates
• Find duplicate Employee IDs
• Identify sales below the monthly target
• Highlight the top 10% of performers
• Track project status using colors
✅ Best Practices
• Use colors consistently (e.g., Green = Good, Red = Attention)
• Don't overuse multiple formatting rules
• Keep formatting simple and easy to understand
• Review and manage rules regularly to avoid conflicts
Double Tap ❤️ For More