How to Use Conditional Formatting in Excel

Conditional formatting automatically changes cell appearance (color, font, borders) based on cell values. It makes patterns and outliers instantly visible without any manual coloring.

How do I apply conditional formatting in Excel?

Select the range first, then choose a rule from Home → Conditional Formatting, set the condition and the format, and click OK. The rule stays attached to the range and re-evaluates automatically whenever values change — you never re-apply it manually.

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

What are the rule types? A quick tour

Excel ships five families of built-in rules: Highlight Cells (compare against a value or text), Top/Bottom (rank-based), Data Bars (in-cell bar chart), Color Scales (heatmap gradient), and Icon Sets (arrows, traffic lights). Formula-based rules are the sixth, do-anything option.

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

How do formula-based rules work (and why does $ matter)?

A formula rule evaluates your formula for the top-left cell of the selection, then copies it across the range like a dragged formula — relative references shift, absolute ones don't. That's why $ anchoring is the whole game: =$D2="Complete" locks the column but lets the row move, so every cell in a row checks that row's column D.

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

$ anchoring cheat sheet (rule applied to A2:E100):
=$D2="Complete"   column locked, row moves → whole-row logic
=D$2="Complete"   row locked, column moves → whole-column logic
=$D$2="Complete"  both locked → every cell checks D2 only
=D2="Complete"    nothing locked → each cell checks a
                  different cell (usually wrong!)

How do I highlight an entire row based on one cell?

Select the whole data range (not just one column), create a formula rule, and lock only the column of the test cell: =$D2="Complete". Because the row number stays relative, row 2's rule checks D2, row 3's checks D3, and every cell across each matching row gets the format.

Goal: Highlight entire row if status = "Complete"

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

Rule applies to each row, checking that row's column D

More formula rule recipes

Alternate row colors:
=MOD(ROW(),2)=0        (even rows; use =1 for odd)

Highlight weekends (A2 holds a date):
=OR(WEEKDAY(A2)=1, WEEKDAY(A2)=7)

Highlight duplicates in a column:
=COUNTIF($A$2:$A$100, A2)>1

Compare two columns:
=$A2<>$B2   (differences)
=$A2=$B2      (matches)

The duplicate rule leans on COUNTIF; brush up on its criteria syntax if the formula looks unfamiliar, and see removing duplicates in Excel for cleaning them up afterward.

Advanced Examples

Expiration Dates

Expired (past dates):
Formula: =A2<TODAY()
Format: Red fill

Expiring soon (within 30 days):
Formula: =AND(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

What are the top conditional formatting mistakes?

The mistakes that bite most people: wrong $ anchoring (the rule checks the wrong cells as it copies across the range), formatting entire columns like A:A (slows the workbook to a crawl), stacking conflicting rules without managing priority, and using an IF() wrapper when the rule formula itself must simply return TRUE or FALSE.

• Missing or misplaced $: =D2="Yes" instead of =$D2="Yes" — the column drifts as the rule copies right
• Whole-column ranges: applying rules to A:A evaluates a million cells; limit to the real data range
• Wrong active cell: the formula is written relative to the top-left cell of the selection — select A2:E100 starting at A2, and write the formula for row 2
• Redundant IF(): write =B2>100, not =IF(B2>100, TRUE, FALSE)
• Rule pile-up: months of copy-pasting creates dozens of fragmented rules — clean up in Manage Rules regularly
• Comparing text to numbers: "50" from an import is not 50 — the rule silently never fires

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

Troubleshooting

Formatting Not Applying

Check:
1. Is formula returning TRUE? (test it in a spare cell)
2. Are cell references correct ($ anchoring)?
3. Is there a higher-priority conflicting rule?
4. Are cells locked/protected?
5. Is the rule's "Applies to" range what you expect?

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

More time-savers in 10 Excel keyboard shortcuts that save hours.

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 a cell before using in a rule
• Limit ranges: Don't format entire columns
• Use consistent colors: Red = bad, Green = good, across all sheets
• Consider colorblind users: Pair color with icons or patterns
• Use sparingly: Highlight what matters most