Applying Conditional Formatting in Excel Using Python

In daily data processing workflows, conditional formatting is an extremely practical feature. It automatically applies specific formatting to cells based on their content, helping users quickly identify data patterns, highlight key information, or detect outliers. By automating conditional formatting with Python, data processing efficiency can be significantly improved, especially when handling large datasets or generating reports in batches.

This article demonstrates how to apply various types of conditional formatting in Excel worksheets using Python and the Spire.XLS library, including cell value-based formatting, highlighting duplicate and unique values, data bar visualization, and icon sets.

Environment Setup

First, install the Spire.XLS library:

pip install Spire.XLS

Once installed, you can start writing code to implement conditional formatting functionality.

Cell Value-Based Conditional Formatting

The most basic type of conditional formatting applies formats based on cell numeric values. For example, cells exceeding a certain threshold can be marked in red, while those below another threshold can be marked in green.

The following example demonstrates how to create a worksheet with sample data and apply cell value-based conditional formatting:

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

# Create a workbook
workbook = Workbook()
sheet = workbook.Worksheets[0]

# Insert sample data into range 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

# Create the first conditional formatting rule: values greater than 800 displayed 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()

# Create the second conditional formatting rule: values less than 300 displayed in green
xcfs2 = sheet.ConditionalFormats.Add()
xcfs2.AddRange(sheet.AllocatedRange)
format2 = xcfs2.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("CellValueFormatting.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Result document:

In this example, ConditionalFormatType.CellValue is used to create cell value-based conditional formatting. The threshold is specified by setting FirstFormula, the comparison operator (such as Greater, Less, etc.) is defined using Operator, and then font color and background color are configured. This approach intuitively highlights data that falls outside the normal range.

Highlighting Duplicate and Unique Values

During data analysis, it is often necessary to identify duplicates or unique items within a dataset. Conditional formatting provides specialized functionality to accomplish this.

The following example demonstrates how to highlight duplicate and unique values:

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

# Create a workbook
workbook = Workbook()
sheet = workbook.Worksheets[0]

# ===== 1. Write sample data =====
data = [
    ["ID", "Name", "Value"],
    [1, "A", 10],
    [2, "B", 20],
    [3, "C", 10],
    [4, "D", 30],
    [5, "E", 40],
    [6, "F", 20],
    [7, "G", 50],
    [8, "H", 60],
    [9, "I", 70],
]

for r, row in enumerate(data, start=1):
    for c, value in enumerate(row, start=1):
        sheet.Range[r, c].Text = str(value)

# ===== 2. Set column width (optional, for clearer demonstration) =====
sheet.Columns[0].ColumnWidth = 15
sheet.Columns[1].ColumnWidth = 20
sheet.Columns[2].ColumnWidth = 15

# ===== 3. Conditional formatting: duplicate values in column C (Value column) =====
xcfs = sheet.ConditionalFormats.Add()
xcfs.AddRange(sheet.Range["C2:C10"])
format1 = xcfs.AddCondition()
format1.FormatType = ConditionalFormatType.DuplicateValues
format1.BackColor = Color.get_IndianRed()

# ===== 4. Conditional formatting: unique values in column C =====
xcfs1 = sheet.ConditionalFormats.Add()
xcfs1.AddRange(sheet.Range["C2:C10"])
format2 = xcfs1.AddCondition()
format2.FormatType = ConditionalFormatType.UniqueValues
format2.BackColor = Color.get_Yellow()

# ===== 5. Save =====
workbook.SaveToFile("HighlightDuplicateUnique.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Result document:

Two special conditional formatting types are used here: ConditionalFormatType.DuplicateValues to identify duplicate values and ConditionalFormatType.UniqueValues to identify unique values. This functionality is particularly useful in data cleaning and validation scenarios, enabling quick detection of data entry errors or anomalous records.

Applying Data Bar Visualization

Data bars provide an intuitive visualization method by displaying bar charts within cells to represent the relative magnitude of values. Larger values result in longer bars.

The following is an example code for applying data bars:

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

# Create a workbook
workbook = Workbook()
sheet = workbook.Worksheets[0]

# Insert sample data
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 cell dimensions
sheet.AllocatedRange.RowHeight = 15
sheet.AllocatedRange.ColumnWidth = 17

# Add data bars
xcfs = sheet.ConditionalFormats.Add()
xcfs.AddRange(sheet.AllocatedRange)
format = xcfs.AddCondition()
format.FormatType = ConditionalFormatType.DataBar
format.DataBar.BarColor = Color.get_CadetBlue()

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

Result document:

The data bar functionality is implemented through the ConditionalFormatType.DataBar type. The bar color can be customized via the DataBar.BarColor property. This visualization approach is particularly suitable for quickly comparing the magnitude relationships within a set of values without creating separate charts.

Applying Icon Sets

Icon sets are another powerful conditional formatting tool that uses predefined icons (such as arrows, traffic lights, flags, etc.) to represent data status or ratings.

The following example demonstrates how to apply a three-color traffic light icon set:

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

# Create a workbook
workbook = Workbook()
sheet = workbook.Worksheets[0]

# Insert sample data
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 cell dimensions
sheet.AllocatedRange.RowHeight = 15
sheet.AllocatedRange.ColumnWidth = 17

# Add icon set (three-color traffic lights)
xcfs = sheet.ConditionalFormats.Add()
xcfs.AddRange(sheet.AllocatedRange)
format = xcfs.AddCondition()
format.FormatType = ConditionalFormatType.IconSet
format.IconSet.IconSetType = IconSetType.ThreeTrafficLights1

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

Result document:

Icon sets are implemented through the ConditionalFormatType.IconSet type. The IconSetType enumeration provides various icon style options, such as ThreeTrafficLights1 (three-color traffic lights), ThreeArrows (three-way arrows), and more. Icon sets are commonly used to represent performance indicators, status assessments, or rating classifications, making data more intuitive and easier to understand.

Practical Tips

In practical applications, multiple conditional formatting types can be combined to achieve more complex data visualization requirements:

• Combined Usage: Multiple conditional formatting rules can be applied to the same data region, with each rule targeting different conditions
• Dynamic Ranges: Using AllocatedRange automatically adapts to the size of the data region, avoiding hardcoded cell ranges
• Color Selection: Choose color combinations with obvious contrast to ensure formatted data remains easy to read
• Performance Considerations: For large datasets, conditional formatting should be used judiciously to avoid excessive rules affecting file opening speed

Summary

This article introduced several main methods for applying conditional formatting in Excel using Python, including cell value-based formatting, duplicate and unique value highlighting, data bar visualization, and icon set application. Through these techniques, data readability and analysis efficiency can be significantly enhanced.

Conditional formatting can be applied not only to simple numeric comparisons but also extended to various data types such as dates and text. Combined with Python's automation capabilities, batch data processing and report generation can be easily achieved, providing strong support for data analysis and business decision-making.