Excel Conditional Formatting: Complete Guide

Staring at a spreadsheet full of numbers makes it hard to spot what matters. Conditional formatting automatically applies colors, icons, and data bars to cells based on rules you define — turning raw data into visual insights.

What Is Conditional Formatting?

It is a feature that changes a cell’s appearance based on its value. For example: “If the value is above 100, make it green; if below 50, make it red.” When the data changes, the formatting updates automatically.

How to Apply It

1. Select the range you want to format
2. Go to Home tab > Conditional Formatting
3. Choose a rule type

Rule Types

1. Highlight Cell Rules

The most basic type — highlights cells that match a specific condition.

Example: Flag sales figures below target ($50,000)

1. Select the sales column
2. Conditional Formatting > Highlight Cell Rules > Less Than
3. Enter 50000, choose red text formatting

Other built-in options:

• Greater Than / Less Than
• Between
• Equal To
• Text That Contains
• Duplicate Values

2. Top/Bottom Rules

Automatically highlights the highest or lowest values in a range.

Example: Highlight the top 10 products by revenue

1. Select the revenue column
2. Conditional Formatting > Top/Bottom Rules > Top 10 Items
3. Choose green fill formatting

You can also use:

• Top 10% / Bottom 10%
• Above Average / Below Average

3. Data Bars

Draws a proportional bar inside each cell based on its value, giving you a mini bar chart.

Example: Visualize monthly revenue

1. Select the revenue column
2. Conditional Formatting > Data Bars > choose a color

4. Color Scales

Applies a gradient of colors based on value — perfect for creating heat maps.

Example: Employee performance heat map across months

1. Select the performance data range
2. Conditional Formatting > Color Scales > Red-Yellow-Green
   • Low values → Red
   • Mid values → Yellow
   • High values → Green

5. Icon Sets

Displays icons (arrows, stars, traffic lights) based on value thresholds.

Example: Show KPI status with traffic lights

1. Select the KPI achievement column
2. Conditional Formatting > Icon Sets > 3 Traffic Lights
   • 80%+ → Green light
   • 60–80% → Yellow light
   • Below 60% → Red light

Advanced: Formula-Based Rules

Custom formulas unlock much more powerful conditions.

Highlight an Entire Row

To highlight a full row based on one column’s value:

1. Select the entire data range (e.g., A2:E100)
2. Conditional Formatting > New Rule > Use a formula to determine…
3. Enter: =$C2="Complete"
4. Choose your fill color

Important: Lock the column ($C) but not the row — this lets the rule evaluate each row independently.

Highlight Weekends in a Schedule

Automatically shade Saturday and Sunday rows:

=WEEKDAY(A1, 2) >= 6

• WEEKDAY(date, 2) returns 1 (Monday) through 7 (Sunday)
• >= 6 matches Saturday (6) and Sunday (7)

Highlight Duplicate Values

Flag any value that appears more than once:

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

Highlight Rows with Blank Cells

Find rows where a required field is empty:

=LEN(TRIM($B2)) = 0

Managing Conditional Formatting

Rule Priority Matters

When multiple rules overlap, the rule listed first takes priority.

• Go to Conditional Formatting > Manage Rules to reorder them
• Check “Stop If True” to prevent lower-priority rules from applying

Copy Formatting to Other Cells

1. Select a cell with conditional formatting
2. Use Format Painter (paintbrush icon) to apply it elsewhere
3. Or use Paste Special (Ctrl+Alt+V > Formats)

Remove Conditional Formatting

Select the range > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells

Performance Tips

• Avoid applying conditional formatting to entire columns (e.g., A:A). Use specific ranges instead.
• Keep the number of rules manageable — too many overlapping rules slow down large workbooks.
• Use Manage Rules periodically to clean up unused rules.

Key Takeaways

Conditional formatting transforms flat spreadsheets into visual dashboards. Start with the built-in highlight rules, then graduate to formula-based rules as you grow more confident. The goal is to let your data speak at a glance — so anyone reading the sheet immediately sees what matters.