How to Apply Conditional Formatting in Excel with Python

In Excel data processing, conditional formatting is a powerful feature that automatically applies different formatting styles based on cell values. It helps quickly identify data patterns, trends, and outliers. This article explains how to use Python to apply conditional formatting in Excel worksheets for effective data visualization.

Conditional formatting is widely used in scenarios such as financial reporting, sales monitoring, and performance evaluation. Automating this process through programming can significantly improve efficiency when handling large batches of Excel files.

Prerequisites

First, install the Spire.XLS for Python library:

pip install Spire.XLS

This library provides rich Excel manipulation capabilities, including creating, reading, modifying, and converting Excel files—without requiring Microsoft Excel to be installed.

Basic Conditional Formatting

The core idea of conditional formatting is to automatically change the appearance of cells based on defined rules. The most basic use case is applying different colors based on value ranges.

The following example demonstrates how to create a worksheet and apply conditional formatting to a data range:

from spire.xls import *
from spire.xls.common import *

# Create a workbook
workbook = Workbook()
# Get the first worksheet
sheet = workbook.Worksheets[0]

# Insert sample data into A1:C4
sheet.Range["A1"].NumberValue = 582
sheet.Range["A2"].NumberValue = 234
sheet.Range["A3"].NumberValue = 314
sheet.Range["A4"].NumberValue = 50
sheet.Range["B1"].NumberValue = 150
sheet.Range["B2"].NumberValue = 894
sheet.Range["B3"].NumberValue = 560
sheet.Range["B4"].NumberValue = 900
sheet.Range["C1"].NumberValue = 134
sheet.Range["C2"].NumberValue = 700
sheet.Range["C3"].NumberValue = 920
sheet.Range["C4"].NumberValue = 450

# Set row height and column width
sheet.AllocatedRange.RowHeight = 15
sheet.AllocatedRange.ColumnWidth = 17

This code creates a worksheet containing 12 numeric values. Next, we will add conditional formatting rules to highlight specific value ranges.

Adding Multiple Conditional Formatting Rules

In real-world scenarios, multiple rules are often applied simultaneously. For example, you can highlight values above a threshold in red and values below a threshold in green.

# Rule 1: Values greater than 800 in red
xcfs1 = sheet.ConditionalFormats.Add()
xcfs1.AddRange(sheet.AllocatedRange)
format1 = xcfs1.AddCondition()
format1.FormatType = ConditionalFormatType.CellValue
format1.FirstFormula = "800"
format1.Operator = ComparisonOperatorType.Greater
format1.FontColor = Color.get_Red()
format1.BackColor = Color.get_LightSalmon()

# Rule 2: Values less than 300 in green
xcfs2 = sheet.ConditionalFormats.Add()
xcfs2.AddRange(sheet.AllocatedRange)
format2 = xcfs1.AddCondition()
format2.FormatType = ConditionalFormatType.CellValue
format2.FirstFormula = "300"
format2.Operator = ComparisonOperatorType.Less
format2.FontColor = Color.get_Green()
format2.BackColor = Color.get_LightBlue()

# Save the file
workbook.SaveToFile("ApplyConditionalFormatting.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Result:

The above code defines two rules:

• Cells with values greater than 800 are formatted with red text and a light salmon background
• Cells with values less than 300 are formatted with green text and a light blue background

ConditionalFormats.Add() creates a new conditional formatting collection, while AddCondition() adds a specific rule. FormatType defines the condition type, FirstFormula sets the comparison value, and Operator specifies the comparison operator.

Visualizing Data with Data Bars

In addition to color formatting, Spire.XLS supports Data Bars, which display horizontal bars inside cells to visually represent value magnitude.

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Populate data
for i in range(1, 11):
    sheet.Range[f"A{i}"].NumberValue = i * 10

# Add data bar formatting
dataBars = sheet.ConditionalFormats.Add()
dataBars.AddRange(sheet.Range["A1:A10"])
dataBarCondition = dataBars.AddCondition()
dataBarCondition.FormatType = ConditionalFormatType.DataBar
dataBarCondition.BarColor = Color.get_Blue()
dataBarCondition.ShowValue = True

workbook.SaveToFile("ApplyDataBarsToCellRange.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Result:

Data bars are ideal for quickly comparing values without creating separate charts.

Applying Icon Sets

Icon Sets provide another intuitive visualization method by displaying icons such as arrows, traffic lights, or ratings next to cell values.

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Insert data
sheet.Range["A1"].NumberValue = 90
sheet.Range["A2"].NumberValue = 75
sheet.Range["A3"].NumberValue = 60
sheet.Range["A4"].NumberValue = 45
sheet.Range["A5"].NumberValue = 30

# Add icon set formatting
iconSets = sheet.ConditionalFormats.Add()
iconSets.AddRange(sheet.Range["A1:A5"])
iconSetCondition = iconSets.AddCondition()
iconSetCondition.FormatType = ConditionalFormatType.IconSet
iconSetCondition.IconSet.IconSetType = IconSetType.ThreeTrafficLights1

workbook.SaveToFile("ApplyIconSetsToCellRange.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Result:

Icon sets are useful for categorizing data states. For example, traffic light icons can represent task status (red = delayed, yellow = in progress, green = completed).

Formula-Based Conditional Formatting

For more advanced use cases, you can use custom formulas to define formatting conditions, enabling complex logical evaluations.

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Populate data
sheet.Range["A1"].Value = "Name"
sheet.Range["B1"].Value = "Sales"
for i in range(2, 7):
    sheet.Range[f"A{i}"].Value = f"Employee{i-1}"
    sheet.Range[f"B{i}"].NumberValue = (i-1) * 1000

# Highlight values above average using a formula
formulaFormat = sheet.ConditionalFormats.Add()
formulaFormat.AddRange(sheet.Range["B2:B6"])
formulaCondition = formulaFormat.AddCondition()
formulaCondition.FormatType = ConditionalFormatType.Formula
formulaCondition.FirstFormula = "=B2>AVERAGE($B$2:$B$6)"
formulaCondition.FontColor = Color.get_White()
formulaCondition.BackColor = Color.get_DarkGreen()

workbook.SaveToFile("CreateFormulaConditionalFormat.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Result:

The flexibility of formula-based formatting lies in its ability to use any Excel formula as a condition, enabling advanced scenarios such as highlighting the maximum value in each row or identifying duplicate values.

Practical Tips

Combine Multiple Formatting Effects

In real projects, combining multiple conditional formatting types can enhance data visualization:

# Apply both color formatting and data bars
colors = sheet.ConditionalFormats.Add()
colors.AddRange(sheet.Range["A1:D10"])
colorCondition = colors.AddCondition()
colorCondition.FormatType = ConditionalFormatType.CellValue
colorCondition.Operator = ComparisonOperatorType.Greater
colorCondition.FirstFormula = "500"
colorCondition.FontColor = Color.get_Red()

bars = sheet.ConditionalFormats.Add()
bars.AddRange(sheet.Range["A1:D10"])
barCondition = bars.AddCondition()
barCondition.FormatType = ConditionalFormatType.DataBar
barCondition.BarColor = Color.get_Blue()

Apply to Dynamic Ranges

Conditional formatting can be applied to dynamically determined data ranges:

# Get the used data range
usedRange = sheet.AllocatedRange
conditionalFormat = sheet.ConditionalFormats.Add()
conditionalFormat.AddRange(usedRange)

This ensures that formatting always applies to all populated cells without manually specifying the range.

Conclusion

This article introduced multiple ways to apply conditional formatting and data visualization in Excel using Python, including:

• Value-based cell formatting
• Using data bars for visual comparison
• Applying icon sets for categorical indicators
• Leveraging custom formulas for complex conditions
• Combining multiple formatting techniques

These techniques are highly applicable in automated report generation, data analysis dashboards, and batch Excel processing. By implementing conditional formatting programmatically, you can improve efficiency while ensuring consistency and accuracy.

With these skills, developers can build automated Excel data processing workflows and provide strong technical support for business analysis and decision-making.