Conditional Format

Use conditional formatting to automatically highlight data, quickly identify important information and trends

What is Conditional Formatting

Conditional formatting is a powerful feature in Excel that automatically applies formatting based on cell values. This makes data visualization simple, helping you quickly identify trends, patterns, and outliers.

Data Bars

Display colored bar charts in cells to visually show value sizes

Color Scales

Use gradient colors to represent value ranges, quickly identify high and low values

Icon Sets

Use icons (arrows, symbols, etc.) to mark different data levels

Highlight Rules

Highlight specific cells based on conditions

Basic Conditional Formatting

Cell Value Rules

from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font
from openpyxl.formatting.rule import CellIsRule

wb = Workbook()
ws = wb.active

# Add data
data = [10, 20, 30, 40, 50, 60, 70, 80, 90, 100]
for idx, value in enumerate(data, start=1):
    ws[f'A{idx}'] = value

# Create conditional formatting rule: cells greater than 50 display in green
green_fill = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')
rule = CellIsRule(operator='greaterThan', formula=['50'], fill=green_fill)
ws.conditional_formatting.add('A1:A10', rule)

# Cells less than 30 display in red
red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
rule2 = CellIsRule(operator='lessThan', formula=['30'], fill=red_fill)
ws.conditional_formatting.add('A1:A10', rule2)

wb.save('conditional_format.xlsx')

Supported Operators

Color Scales

Color scales use gradient colors to represent value ranges, perfect for heatmap effects: 

from openpyxl.formatting.rule import ColorScaleRule

# Create three-color scale: red (min) - yellow (mid) - green (max)
color_scale = ColorScaleRule(
    start_type='min',
    start_color='FF0000',  # Red
    mid_type='percentile',
    mid_value=50,
    mid_color='FFFF00',  # Yellow
    end_type='max',
    end_color='00FF00'  # Green
)

ws.conditional_formatting.add('A1:A10', color_scale)

# Two-color scale
color_scale_2 = ColorScaleRule(
    start_type='min',
    start_color='FFFFFF',  # White
    end_type='max',
    end_color='0000FF'  # Blue
)

ws.conditional_formatting.add('B1:B10', color_scale_2)

Data Bars

Data bars display colored bar charts in cells to visually show value sizes: 

from openpyxl.formatting.rule import DataBarRule

# Create data bar
data_bar = DataBarRule(
    start_type='min',
    end_type='max',
    color='638EC6'  # Blue data bar
)

ws.conditional_formatting.add('A1:A10', data_bar)

# Custom data bar range
data_bar_custom = DataBarRule(
    start_type='num',
    start_value=0,
    end_type='num',
    end_value=100,
    color='FF6347'  # Red data bar
)

ws.conditional_formatting.add('B1:B10', data_bar_custom)

Icon Sets

Icon sets use icons to mark different data levels: 

from openpyxl.formatting.rule import IconSetRule

# Three-arrow icon set
icon_set = IconSetRule(
    '3Arrows',
    'percent',
    [0, 33, 67]
)

ws.conditional_formatting.add('A1:A10', icon_set)

# Traffic light icon set
traffic_lights = IconSetRule(
    '3TrafficLights1',
    'percent',
    [0, 33, 67]
)

ws.conditional_formatting.add('B1:B10', traffic_lights)

Available Icon Sets

  • 3Arrows - Three arrows (up, flat, down)
  • 3ArrowsGray - Gray three arrows
  • 3TrafficLights1 - Traffic lights (red, yellow, green)
  • 3TrafficLights2 - Traffic lights (with border)
  • 3Signs - Three signs
  • 3Symbols - Three symbols (circles)
  • 4Arrows - Four arrows
  • 4Rating - Four-level rating
  • 5Arrows - Five arrows
  • 5Rating - Five-level rating

Formula-based Conditional Formatting

Use formulas to create more complex conditional formatting rules: 

from openpyxl.formatting.rule import Rule
from openpyxl.styles import Font, PatternFill

# Highlight even rows
red_fill = PatternFill(start_color='FFCCCC', end_color='FFCCCC', fill_type='solid')
formula_rule = Rule(
    type='expression',
    formula=['MOD(ROW(),2)=0'],
    fill=red_fill
)

ws.conditional_formatting.add('A1:C10', formula_rule)

# Highlight duplicate values
yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
duplicate_rule = Rule(
    type='expression',
    formula=['COUNTIF($A$1:$A$10,A1)>1'],
    fill=yellow_fill
)

ws.conditional_formatting.add('A1:A10', duplicate_rule)

Conditional Formatting Best Practices