In this tutorial, you’ll learn how to add conditional formatting using OpenPyXL and Python.
Step 1: Install OpenPyXL
If you haven’t already, install the library:
pip install openpyxlStep 2: Create a New Excel Workbook
First, we’ll create a workbook and fill it with some sample data.
from openpyxl import Workbook
# Create a new workbook and select the active sheet
workbook = Workbook()
worksheet = workbook.active
# Sample data to write into the worksheet
scores = [10, 55, 30, 75, 25, 90, 45]
# Write each score into column A
for row_number, score in enumerate(scores, start=1):
worksheet[f"A{row_number}"] = scoreStep 3: Define the Conditional Formatting Rule
In OpenPyXL, you can define different types of comparisons using the operator parameter.
We want to highlight any score greater than 50 with a yellow background.
For our example, we’ll use the greaterThan operator.
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill
# Create a yellow fill style
highlight_fill = PatternFill(
start_color="FFFF00", # Light yellow
end_color="FFFF00",
fill_type="solid"
)
# Define a conditional formatting rule: highlight cells greater than 50
highlight_rule = CellIsRule(
operator="greaterThan",
formula=["50"],
fill=highlight_fill
)Here are the available options:
notBetween — highlight cells outside a specific range
equal — highlight cells that are equal to a specific value
notEqual — highlight cells that are not equal to a specific value
greaterThan — highlight cells greater than a specific value
lessThan — highlight cells less than a specific value
greaterThanOrEqual — highlight cells greater than or equal to a value
lessThanOrEqual — highlight cells less than or equal to a value
between — highlight cells between two values
Step 4: Apply the Conditional Formatting to a Range
Now, let’s apply the rule to the range A1:A7 (where our scores are).
# Apply the conditional formatting rule to cells A1 through A7
worksheet.conditional_formatting.add("A1:A7", highlight_rule)Any cell in A1–A7 with a value over 50 will now be highlighted when opened in Excel.
Step 5: Save the Workbook
# Save the workbook to a file
workbook.save("highlighted_scores.xlsx")Full Code Together
from openpyxl import Workbook
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill
# Step 1: Create workbook and worksheet
workbook = Workbook()
worksheet = workbook.active
# Step 2: Fill worksheet with sample data
scores = [10, 55, 30, 75, 25, 90, 45]
for row_number, score in enumerate(scores, start=1):
worksheet[f"A{row_number}"] = score
# Step 3: Define a yellow fill style
highlight_fill = PatternFill(
start_color="FFFF00",
end_color="FFFF00",
fill_type="solid"
)
# Step 4: Create a conditional formatting rule
highlight_rule = CellIsRule(
operator="greaterThan",
formula=["50"],
fill=highlight_fill
)
# Step 5: Apply the rule to a range
worksheet.conditional_formatting.add("A1:A7", highlight_rule)
# Step 6: Save the workbook
workbook.save("highlighted_scores.xlsx")