What is Conditional Formatting?

Conditional formatting automatically changes cell appearance (color, font, borders) based on cell values. Makes patterns and outliers instantly visible.

How to Apply

1. Select cells
2. Home tab → Conditional Formatting
3. Choose rule type
4. Set conditions and format

Quick Built-In Rules

1. Highlight Cells Rules

Greater Than: Highlight values > threshold
Less Than: Highlight values < threshold
Between: Highlight values in range
Equal To: Highlight exact matches
Text Contains: Highlight cells containing text
Duplicate Values: Highlight duplicates
Date Occurring: Highlight dates (today, tomorrow, last week, etc.)

Example: Highlight Values > 100

1. Select B2:B20
2. Conditional Formatting → Highlight Cells Rules → Greater Than
3. Enter: 100
4. Choose format (e.g., Light Red Fill)
5. Click OK

2. Top/Bottom Rules

Top 10 Items: Highlight top N values
Top 10%: Highlight top percentage
Bottom 10 Items: Highlight bottom N values
Above Average: Highlight above average
Below Average: Highlight below average

Example: Highlight Top 5 Sales

1. Select sales range
2. Conditional Formatting → Top/Bottom Rules → Top 10 Items
3. Change 10 to 5
4. Choose format
5. Click OK

3. Data Bars

Visual bars inside cells (like a bar chart in each cell)

1. Select number range
2. Conditional Formatting → Data Bars
3. Choose gradient or solid fill
4. Click to apply

Result: Longer bars = larger values

4. Color Scales

Gradient coloring from min to max

2-Color Scale: Low = one color, High = another
3-Color Scale: Low = red, Mid = yellow, High = green

Example:
1. Select data
2. Conditional Formatting → Color Scales
3. Choose Red-Yellow-Green
4. Low values = red, high values = green

5. Icon Sets

Add icons based on value thresholds

Options:
- Arrows (↑ → ↓)
- Traffic lights (🟢 🟡 🔴)
- Flags, stars, ratings

Example:
1. Select data
2. Conditional Formatting → Icon Sets
3. Choose 3 Arrows
4. Excel divides into thirds automatically

Custom Rules with Formulas

Create Formula-Based Rule

1. Select cells
2. Conditional Formatting → New Rule
3. Choose "Use a formula to determine which cells to format"
4. Enter formula (must return TRUE/FALSE)
5. Set format
6. Click OK

Example 1: Highlight Entire Row Based on Column

Goal: Highlight entire row if status = "Complete"

1. Select A2:E100 (entire data range)
2. New Rule → Use a formula
3. Formula: =$D2="Complete"
   (Note: $ before D, not before 2)
4. Set green fill
5. OK

Rule applies to each row, checking column D

Example 2: Alternate Row Colors

Formula: =MOD(ROW(),2)=0

Highlights every even row
Change to =MOD(ROW(),2)=1 for odd rows

Example 3: Highlight Weekends

Formula: =OR(WEEKDAY(A2)=1, WEEKDAY(A2)=7)

Where A2 contains date
1 = Sunday, 7 = Saturday

Example 4: Highlight Duplicates in Column

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

Highlights any value appearing more than once

Example 5: Compare Two Columns

Highlight differences between columns A and B:
Formula: =$A2<>$B2

Highlight matches:
Formula: =$A2=$B2

Advanced Examples

Expiration Dates

Expired (past dates):
Formula: =A2=TODAY(), A2<=TODAY()+30)
Format: Yellow fill

Future dates:
Formula: =A2>TODAY()+30
Format: Green fill

Budget Tracking

Over budget (spent > budget):
Formula: =$B2>$C2
Format: Red text

At risk (>80% of budget):
Formula: =$B2>$C2*0.8
Format: Orange fill

Under budget:
Formula: =$B2<=$C2*0.8
Format: Green fill

Performance Ratings

Excellent (>90):
Formula: =B2>90
Format: Dark green

Good (70-90):
Formula: =AND(B2>=70, B2<=90)
Format: Light green

Needs Improvement (<70):
Formula: =B2<70
Format: Red

Managing Rules

View All Rules

Conditional Formatting → Manage Rules

Shows all rules applied to:
- This Worksheet
- Selection
- Specific range

You can:
- Edit rules
- Delete rules
- Change rule order (priority)
- Enable/disable rules

Rule Priority

Rules apply top-to-bottom
First matching rule wins (unless "Stop If True" unchecked)

Use arrows to reorder:
↑ Move up (higher priority)
↓ Move down (lower priority)

Copy Formatting

Method 1: Format Painter
1. Select cell with conditional formatting
2. Click Format Painter (brush icon)
3. Select destination cells

Method 2: Copy/Paste
1. Copy cell
2. Select destination
3. Paste → Paste Special → Formats

Common Use Cases

Sales Dashboard

Data bars for sales amounts (visual comparison)
Green fill for above target
Red fill for below target
Icon sets for performance tiers

Project Timeline

Red: Overdue tasks (deadline < today, status ≠ complete)
Yellow: Due this week
Green: Completed
Gray: Future tasks

Data Quality

Highlight blanks: Format cells with no value
Highlight errors: =ISERROR(A2)
Highlight outliers: Values beyond 2 standard deviations

Tips and Tricks

Formula Tips

• Use $ correctly: =$A2 (column fixed), $A$2 (both fixed)
• Formula must return TRUE/FALSE
• Formula applies to active cell first (top-left of selection)
• Test formula in cell first before using in rule

Performance

• Limit range: Don't format entire columns (A:A)
• Minimize rules: Too many rules slow Excel
• Use built-in rules when possible (faster than formulas)

Design Best Practices

• Don't overuse color: Too much is distracting
• Use consistent colors: Red = bad, Green = good
• Consider colorblind users: Use patterns/icons too
• Test visibility: Ensure colors show on projector/print

Troubleshooting

Formatting Not Applying

Check:
1. Is formula returning TRUE? (test in cell)
2. Are cell references correct?
3. Is there a higher-priority conflicting rule?
4. Are cells locked/protected?
5. Is conditional formatting enabled?

Formula Errors

WRONG: =A2="Yes" (quotes need to be straight, not curly)
RIGHT: =A2="Yes"

WRONG: =A2>B2 (comparing wrong cells when copied)
RIGHT: =$A2>$B2 (lock columns as needed)

Clearing Formatting

Clear from selection:
Conditional Formatting → Clear Rules → Clear Rules from Selected Cells

Clear from sheet:
Conditional Formatting → Clear Rules → Clear Rules from Entire Sheet

Keyboard Shortcuts

Alt + H + L + H: Highlight cells rules
Alt + H + L + T: Top/Bottom rules
Alt + H + L + D: Data Bars
Alt + H + L + S: Color Scales
Alt + H + L + I: Icon Sets
Alt + H + L + N: New Rule
Alt + H + L + R: Manage Rules

Quick Reference

Highlight > value:     Greater Than rule
Highlight duplicates:  Highlight Cells Rules → Duplicate Values
Visual comparison:     Data Bars
Gradient coloring:     Color Scales
Performance tiers:     Icon Sets
Custom logic:          New Rule → Use a formula
Entire row coloring:   Formula with absolute column: =$D2="Value"
Alternating rows:      =MOD(ROW(),2)=0

Best Practices

• Start simple: Use built-in rules first
• Test formulas: Verify in cell before using in rule
• Name your rules: Makes management easier
• Document rules: Add notes for complex logic
• Use sparingly: Highlight what matters most
• Consistent colors: Same meaning across sheets