Python openpyxl Conditional Formatting Guide

Conditional formatting makes Excel data visual. It highlights important information automatically. Python's openpyxl module lets you apply these rules programmatically.

This guide covers all conditional formatting types. You will learn to create data bars, color scales, and icon sets. We include practical code examples for each type.

What is Conditional Formatting?

Conditional formatting changes cell appearance based on rules. It helps spot trends and patterns quickly. You can highlight values above average or below thresholds.

Openpyxl supports six main formatting types. These include data bars, color scales, and icon sets. You can also format cells containing specific text or values.

Setting Up Openpyxl

First, ensure openpyxl is installed. Use pip for installation if needed. Our install openpyxl guide covers this in detail.

# Install openpyxl if not present
pip install openpyxl

Import the necessary modules in your script. You need Workbook and the conditional formatting classes. This enables all formatting functionality.

from openpyxl import Workbook
from openpyxl.formatting.rule import Rule
from openpyxl.styles import PatternFill, Font
from openpyxl.formatting.rule import ColorScaleRule, DataBarRule

Basic Conditional Formatting

Start with simple cell highlighting. Format cells that meet specific criteria. This example highlights values greater than 50.

from openpyxl import Workbook
from openpyxl.formatting.rule import Rule
from openpyxl.styles import PatternFill

# Create workbook and add sample data
wb = Workbook()
ws = wb.active
ws.append([10, 20, 30])
ws.append([40, 50, 60])
ws.append([70, 80, 90])

# Create formatting rule for values > 50
red_fill = PatternFill(start_color='FF9999', end_color='FF9999', fill_type='solid')
greater_than_rule = Rule(type='cellIs', operator='greaterThan', formula=['50'])
greater_than_rule.fill = red_fill

# Apply rule to cell range
ws.conditional_formatting.add('A1:C3', greater_than_rule)

# Save workbook
wb.save('conditional_formatting.xlsx')

The output Excel file will show cells with values above 50 in red. This basic approach works for simple conditions.

Data Bars Conditional Formatting

Data bars create visual bar charts within cells. Longer bars represent higher values. They provide quick value comparisons.

from openpyxl import Workbook
from openpyxl.formatting.rule import DataBarRule

wb = Workbook()
ws = wb.active

# Add sample data
data = [5, 15, 25, 35, 45, 55]
for i, value in enumerate(data, 1):
    ws.cell(row=i, column=1, value=value)

# Create data bar rule
data_bar_rule = DataBarRule(start_type='min', end_type='max', color='638EC6')
ws.conditional_formatting.add('A1:A6', data_bar_rule)

wb.save('data_bars.xlsx')

The output shows gradient bars in cells A1 through A6. The bar length corresponds to each cell's value.

Color Scales Formatting

Color scales use color gradients to show value relationships. Lower values might be red, higher values green. This creates heat map effects.

from openpyxl import Workbook
from openpyxl.formatting.rule import ColorScaleRule

wb = Workbook()
ws = wb.active

# Add temperature data
temperatures = [15, 18, 22, 25, 28, 32, 35]
for i, temp in enumerate(temperatures, 1):
    ws.cell(row=i, column=1, value=temp)

# Apply 3-color scale (low-medium-high)
color_scale_rule = ColorScaleRule(start_type='min', start_color='F8696B',
                                 mid_type='percentile', mid_color='FFEB84',
                                 end_type='max', end_color='63BE7B')

ws.conditional_formatting.add('A1:A7', color_scale_rule)
wb.save('color_scales.xlsx')

The output displays temperatures with color gradients. Cool temperatures appear red, warm ones green.

Icon Sets Formatting

Icon sets add symbols to cells based on values. You can use arrows, flags, or traffic lights. They categorize data visually.

from openpyxl import Workbook
from openpyxl.formatting.rule import IconSetRule

wb = Workbook()
ws = wb.active

# Add sales performance data
sales = [45, 65, 75, 85, 95, 110]
for i, amount in enumerate(sales, 1):
    ws.cell(row=i, column=1, value=amount)

# Apply 3-arrow icon set
icon_set_rule = IconSetRule(icon_style='3Arrows', type='percent',
                           values=[30, 60, 80])

ws.conditional_formatting.add('A1:A6', icon_set_rule)
wb.save('icon_sets.xlsx')

The output shows arrows indicating performance levels. Down arrows for low values, up arrows for high.

Text-Based Conditional Formatting

You can format cells containing specific text. This highlights particular categories or keywords. Use the containsText operator.

from openpyxl import Workbook
from openpyxl.formatting.rule import Rule
from openpyxl.styles import PatternFill

wb = Workbook()
ws = wb.active

# Add product data
products = ['Apple', 'Banana', 'Orange', 'Grape', 'Apple', 'Pear']
for i, product in enumerate(products, 1):
    ws.cell(row=i, column=1, value=product)

# Highlight all Apple products
yellow_fill = PatternFill(start_color='FFFF99', end_color='FFFF99', fill_type='solid')
text_rule = Rule(type='containsText', operator='containsText', text='Apple')
text_rule.fill = yellow_fill

ws.conditional_formatting.add('A1:A6', text_rule)
wb.save('text_formatting.xlsx')

The output highlights all cells containing "Apple" in yellow. This works for partial matches too.

Top/Bottom Rules

Highlight top or bottom ranked values automatically. Useful for identifying outliers or best performers. No manual threshold setting needed.

from openpyxl import Workbook
from openpyxl.formatting.rule import Rule
from openpyxl.styles import PatternFill

wb = Workbook()
ws = wb.active

# Add test scores
scores = [85, 92, 78, 96, 88, 74, 99, 81]
for i, score in enumerate(scores, 1):
    ws.cell(row=i, column=1, value=score)

# Highlight top 3 scores
green_fill = PatternFill(start_color='99FF99', end_color='99FF99', fill_type='solid')
top_rule = Rule(type='top10', rank=3)
top_rule.fill = green_fill

ws.conditional_formatting.add('A1:A8', top_rule)
wb.save('top_bottom.xlsx')

The output highlights the three highest scores in green. The rule adapts as data changes.

Working with Multiple Rules

You can apply multiple conditional formats to the same range. Rules execute in order of application. Later rules can override earlier ones.

from openpyxl import Workbook
from openpyxl.formatting.rule import Rule
from openpyxl.styles import PatternFill

wb = Workbook()
ws = wb.active

# Add inventory data
inventory = [5, 15, 25, 35, 2, 45]
for i, count in enumerate(inventory, 1):
    ws.cell(row=i, column=1, value=count)

# Low stock rule (red)
red_fill = PatternFill(start_color='FF9999', end_color='FF9999', fill_type='solid')
low_rule = Rule(type='cellIs', operator='lessThan', formula=['10'])
low_rule.fill = red_fill

# High stock rule (green)
green_fill = PatternFill(start_color='99FF99', end_color='99FF99', fill_type='solid')
high_rule = Rule(type='cellIs', operator='greaterThan', formula=['30'])
high_rule.fill = green_fill

# Apply both rules
ws.conditional_formatting.add('A1:A6', low_rule)
ws.conditional_formatting.add('A1:A6', high_rule)

wb.save('multiple_rules.xlsx')

The output shows low stock in red and high stock in green. Values between 10-30 remain unformatted.

Best Practices

Use conditional formatting sparingly. Too many rules can make sheets confusing. Focus on the most important insights.

Choose colors carefully. Ensure sufficient contrast for readability. Consider colorblind users when selecting palettes.

Test rules with various data scenarios. Verify they work as expected with edge cases. Update rules when business needs change.

Common Issues and Solutions

Rules not applying? Check your cell range references. Verify they match your actual data locations exactly.

Unexpected formatting? Rules execute in application order. Later rules might override earlier ones unintentionally.

For more general openpyxl usage, see our Python openpyxl tutorial. It covers reading and writing Excel files comprehensively.

Conclusion

Conditional formatting transforms raw data into visual insights. Openpyxl provides powerful tools for automated Excel formatting.

You can create data bars, color scales, and icon sets programmatically. Combine multiple rules for sophisticated data visualization.

Remember to start with clear objectives. Apply formatting that supports your data story. Keep user experience in mind throughout.

For more advanced styling options, explore our style cells and fonts guide. It covers font formatting, borders, and alignment in detail.