Adding Conditional Formatting to Excel Using Python Openpyxl

Adding conditional formatting using openpyxl is an easy and straightforward process. While working with Excel files, conditional formatting is useful for the visualization of trends in the data, highlighting crucial data points, and making data more meaningful and understandable. In this article, we will give a detailed step-by-step explanation for adding conditional formatting using openpyxl.

Introduction to Conditional Formatting in OpenPyxl

Let's start with understanding some important concepts related to conditional formatting in openpyxl before jumping into the code implementation example.

Conditional Formatting Rules

The Python library openpyxl allows us to define various types of conditional formatting rules, following are the rules:

• CellIsRule: This rule indicates that we have to format cells based on the comparison between cell values and a specific condition, for example, greater than, less than, and more.
• ColorScaleRule: This rule indicates that we can apply a color scale to a range of cells, where the color represents the relative value of the data.
• FormulaRule: This rule involves applying formatting based on the result of a formula.
• IconSetRule: This rule involves displaying icons in the cells based on the values of the cells.

Data Bars

• The data bars are used to visually represent the value of a particular cell with a bar, where the length of the data bar corresponds the value of the cell compared to the rest of the cells in the range.

Color Scales

• The colors scales allows the application of different colors to the cells, by basis of their values. This usually involves a gradient of colors where high values are of one colors, and low values are of another color.

Cell Comparision

• We have already discussed about the CellIsRule, with this rule, we can set conditions like equal to, greater than, or less than, or between, and apply formatting when those conditions are met.

Step-By-Step Implementation of Conditional Formatting in Openpyxl

Step 1 - Install openpyxl:

Run the following command in command line or terminal to install openpyxl if not already installed, Use pip for windows and pip3 for Mac/Linux.

pip/pip3 install openpyxl

Step 2 - Create a Workbook:

Next start by creating an excel workbook by using the Workbook() function, followed by creating an active worksheet using wb.active().

Read: Create a workbook using openpyxl

from openpyxl import Workbook

# Create a workbook and select the active sheet
wb = Workbook()
ws = wb.active()

Step 3 - Add Sample Data:

Now start by adding the sample data to the sheet using the function ws.append(), which appends each row of the data to the worksheet.

# ...

# Add some sample data
data = [
    [10, 20, 30, 40],
    [15, 25, 35, 45],
    [50, 60, 70, 80],
    [5, 10, 15, 20]
]

for row in data:
    ws.append(row)

Step 4 - Apply Conditional Formatting

Let's first import necessary classes:

# ...
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule, ColorScaleRule, FormulaRule

# ...

Example 1: The CellIsRule is used to highlight cells greater than 40 in red. The PatternFill object defines the fill color, and the rule is applied to the range A1:D4.

# ...

# Example 1: Highlight cells greater than 40 in red
red_fill = PatternFill(start_color="FFEE1111",
                       end_color="FFEE1111", fill_type="solid")
ws.conditional_formatting.add('A1:D4', CellIsRule(
    operator='greaterThan', formula=['40'], stopIfTrue=True, fill=red_fill))

Example 2: The ColorScaleRule creates a color gradient where the minimum value (10) is associated with a light yellow color, and the maximum value (80) is associated with blue. This is applied to the same range.

# ...

# Example 2: Apply a 2-color scale between 10 and 80
ws.conditional_formatting.add('A1:D4', ColorScaleRule(start_type='num', start_value=10, start_color='FFFFEDA0',
                                                      end_type='num', end_value=80, end_color='FF00BFFF'))

Example 3: The FormulaRule highlights even numbers using a blue fill. The formula MOD(A1,2)=0 checks if a number is even, and the fill is applied when the formula returns True.

# Example 3: Formula-based conditional formatting (highlight even numbers in blue)
blue_fill = PatternFill(start_color="FF0000FF",
                        end_color="FF0000FF", fill_type="solid")
ws.conditional_formatting.add('A1:D4', FormulaRule(
    formula=['MOD(A1,2)=0'], fill=blue_fill))

Step 5 - Save the Workbook:

Finally, save the excel file using the function wb.save(), this will create an excel file with the previously applied conditional formatting as desired.

# Save the workbook
wb.save('conditional_formatting_example.xlsx')

Following is the code combining all the above discussed steps, The output for the following code will generate an Excel file named conditional_formatting_example.xlsx after the desired conditional formatting is applied:

Complete Code:

# Importing openpyxl library
from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule, ColorScaleRule, FormulaRule

# Create a workbook and select the active sheet
wb = Workbook()
ws = wb.active

# Add some sample data
data = [
    [10, 20, 30, 40],
    [15, 25, 35, 45],
    [50, 60, 70, 80],
    [5, 10, 15, 20]
]

for row in data:
    ws.append(row)

# Apply conditional formatting based on cell values
# Example 1: Highlight cells greater than 40 in red
red_fill = PatternFill(start_color="FFEE1111",
                       end_color="FFEE1111", fill_type="solid")
ws.conditional_formatting.add('A1:D4', CellIsRule(
    operator='greaterThan', formula=['40'], stopIfTrue=True, fill=red_fill))

# Example 2: Apply a 2-color scale between 10 and 80
ws.conditional_formatting.add('A1:D4', ColorScaleRule(start_type='num', start_value=10, start_color='FFFFEDA0',
                                                      end_type='num', end_value=80, end_color='FF00BFFF'))

# Example 3: Formula-based conditional formatting (highlight even numbers in blue)
blue_fill = PatternFill(start_color="FF0000FF",
                        end_color="FF0000FF", fill_type="solid")
ws.conditional_formatting.add('A1:D4', FormulaRule(
    formula=['MOD(A1,2)=0'], fill=blue_fill))

# Save the workbook
wb.save('conditional_formatting_example.xlsx')

Output:

Conclusion

Using the openpyxl python library to add conditional formatting is a very easy process and not at all confusing. After this tutorial we will be able to perform conditional formatting in our excel files, In this article's code implementation example, we have discussed three major conditional formatting techniques like cell based rules, color scales, and formula based rules. Feel free to refer the article multiple times for in-depth understanding.

sai_teja_anantha

Follow

Improve

Article Tags :

• Python
• Python-Library

Practice Tags :

• python