Conditional formatting automatically applies colors, icons, and data bars based on cell values. Transform boring spreadsheets into visual dashboards.

Most Useful Rules

1. Highlight Values Above/Below Average

Select range → Conditional Formatting → Top/Bottom Rules → Above Average

Use case: Instantly spot outliers in sales data, test scores, or performance metrics.

2. Color Scales (Heat Maps)

Select range → Conditional Formatting → Color Scales → Red-Yellow-Green

Low values = Red
Middle values = Yellow
High values = Green

Perfect for: Revenue tables, KPI dashboards, correlation matrices

3. Data Bars (In-Cell Charts)

Select range → Conditional Formatting → Data Bars

Result: Each cell gets a bar proportional to its value. Creates instant mini bar charts inside cells.

4. Icon Sets

3 Arrows: Up/Flat/Down trends
Traffic Lights: Good/Warning/Bad status
Stars: Rating systems

Select range → Conditional Formatting → Icon Sets

Formula-Based Rules (Most Powerful)

Highlight Entire Row Based on One Column

Select A2:E100 → New Rule → Use a formula:

=($D2 > 1000)

The $ before D locks the column
The 2 without $ makes it relative to each row
Highlights entire row when column D > 1000

Highlight Duplicates in a Column

Select A2:A100 → New Rule → Use a formula:

=COUNTIF($A$2:$A$100, A2) > 1

Shows which values appear more than once

Alternate Row Shading

Select range → New Rule → Use a formula:

=MOD(ROW(), 2) = 0

Colors every even row
Use = 1 for odd rows instead

Highlight Weekends in Date Column

=WEEKDAY(A2, 2) > 5

Where A2 contains dates
Highlights Saturdays and Sundays

Practical Business Examples

Scenario	Formula
Overdue tasks	=AND($C2="Open", $B2<TODAY())
Low inventory alert	=$D2<$E2
Blanks in required field	=ISBLANK($C2)

Managing Multiple Rules

• Home → Conditional Formatting → Manage Rules
• Order matters! Drag rules up/down to change priority
• Check "Stop If True" to prevent lower rules from running
• Copy formatting: Format Painter works with conditional formatting

Performance Tips

• ✅ Apply to specific ranges, not entire columns (A:A)
• ✅ Use built-in rules when possible (faster than formulas)
• ❌ Avoid volatile functions: NOW(), TODAY(), RAND() in formulas
• ❌ Too many rules (10+) on large ranges slows Excel