Apply Conditional Formatting in Excel with Python without Hassle

Conditional formatting is a powerful feature in Excel that makes key data stand out through visual cues. It’s widely used for tasks like highlighting values above average, marking duplicates, or showing project status with color. While manually applying these rules can be tedious and error-prone, using Python allows you to automate the process efficiently. Whether you're analyzing large datasets or building dashboards, applying conditional formatting in Excel through Python can save time and improve accuracy.

Conditional Formatting in Excel with Python: Highlight Alternate Rows

Zebra striping makes it easier to scan and track data in large spreadsheets. They not only improve readability but also give your Excel tables a cleaner, more professional appearance. This effect can be easily achieved with Python by applying conditional formatting to highlight alternate rows. In this series, I’ll use the Spire.XLS library, one of the most popular and versatile Python libraries for editing Excel files, including conditional formatting.

Steps to apply conditional formatting and highlight alternate rows in Excel files:

• Create an object of the Workbook class.

• Load an Excel source file using the Workbook.LoadFromFile() method.

• Access a specified worksheet through the Workbook.Worksheets[] property.

• Add a conditional formatting to the sheet with the Worksheet.ConditionalFormats.Add() method.

• Customize the cell range to apply the conditional format using the XlsConditionalFormats.AddRange() method.

• Add a condition to change the color of even rows through the XlsConditionalFormats.AddCondition() method, then add another condition for odd rows.

• Save the updated Excel file with the XlsConditionalFormats.AddCondition() method.

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

# Create a Workbook object
workbook = Workbook()

# Load a file from disk
workbook.LoadFromFile("/Population.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add a conditional format to the worksheet
conditionalForamt = sheet.ConditionalFormats.Add()

# Specify the range where the conditional format will be applied
conditionalForamt .AddRange(sheet.Range[2, 1, 11, 5])

# Specify the first condition and format the cells that match the condition
condition1 = conditionalForamt .AddCondition()
condition1.FirstFormula = "=MOD(ROW(),2)=0"
condition1.FormatType = ConditionalFormatType.Formula
condition1.BackColor = Color.get_White()

# Specify the second condition and format the cells that match the condition
condition2 = conditionalForamt.AddCondition()
condition2.FirstFormula = "=MOD(ROW(),2)=1"
condition2.FormatType = ConditionalFormatType.Formula
condition2.BackColor = Color.get_LightGray()

# Save to file
workbook.SaveToFile("/AlternateRowColors.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Conditional Formatting in Excel with Python: Highlight Top or Bottom Values

Highlighting the highest or lowest values is a common use case for conditional formatting. Whether you're identifying the top-performing employee based on sales or pinpointing the item with the lowest stock for restocking, this task can be automated in Python. By using the Worksheet.ConditionalFormats.Add() and XlsConditionalFormats.AddCondition() methods, you can quickly highlight key data points without manual effort.

Steps to highlight the top or bottom values using conditional formatting in Python:

• Create an instance of the Workbook class.

• Read an Excel file using the Workbook.LoadFromFile() method.

• Get an Excel worksheet through the Workbook.Worksheets[] property.

• Add a conditional formatting to the worksheet with the Worksheet.ConditionalFormats.Add() method.

• Specify the cell range to apply the conditional format using the XlsConditionalFormats.AddRange() method.

• Add a condition to change the color of cells that rank as the top or the bottom with the XlsConditionalFormats.AddCondition() method.

• Save the modified Excel workbook.

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

# Create a Workbook object
workbook = Workbook()

# Load a file from disk
workbook.LoadFromFile("/Population.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add a conditional format to the worksheet
conditionalForamt = sheet.ConditionalFormats.Add()

# Specify the range where the conditional format will be applied
conditionalForamt.AddRange(sheet.Range["C2:C11"])

# Add a condition to highlight the top 3 ranked values
condition1 = conditionalForamt.AddTopBottomCondition(TopBottomType.Top, 3)
condition1.BackColor = Color.get_Red()

# Add a condition to highlight the bottom 3 ranked values
# condition2 = conditionalForamt.AddTopBottomCondition(TopBottomType.Bottom, 3)
# condition2.BackColor = Color.get_Green()

# Save the workbook to an Excel file
workbook.SaveToFile("/TopOrBottomValues.xlsx", ExcelVersion.Version2016)

Conditional Formatting in Excel with Python: Highlight Values Above/Below Average

Using Python to apply conditional formatting for highlighting cells above or below the average follows a similar process to the previous examples. The main difference lies in the condition specified when using the XlsConditionalFormats.AddCondition() method. Below are the detailed steps and code examples to help you implement this formatting with ease.

Steps to highlight values above or below the average with conditional formatting in Excel:

• Instantiate a Workbook object, and read an Excel spreadsheet from files.

• Retrieve an Excel worksheet through the Workbook.Worksheets[] property.

• Add a conditional formatting to the worksheet with the Worksheet.ConditionalFormats.Add() method.

• Specify the cell range to apply the conditional format using the XlsConditionalFormats.AddRange() method.

• Highlight values above the average with the XlsConditionalFormats.AddAverageCondition() method.

• Save the updated Excel workbook as a new one.

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

# Create a Workbook object
workbook = Workbook()

# Load a file from disk
workbook.LoadFromFile("/Population.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add a conditional format to the worksheet
conditionalForamt = sheet.ConditionalFormats.Add()

# Specify the range where the conditional format will be applied
conditionalForamt.AddRange(sheet.Range["C2:C11"])

# Add a condition to highlight the values above average
condition1 = conditionalForamt.AddAverageCondition(AverageType.Above)
condition1.BackColor = Color.get_Yellow()

# Add a condition to highlight the values below average
# condition2 = conditionalForamt.AddAverageCondition(AverageType.Below)
# condition2.BackColor = Color.get_DarkGray()

# Save the workbook to an Excel file
workbook.SaveToFile("/AboveOrBelowAverage.xlsx", ExcelVersion.Version2016)

Conditional Formatting in Excel with Python: Highlight Values Within a Range

Highlighting values within a specific range can help you quickly identify normal or target values in your dataset. For example, you might want to highlight customers aged between 25 and 35 for user profiling, or monitor whether appliance temperatures stay within a safe operating range. This type of conditional formatting is especially useful when you need to focus on values that fall within defined limits.

Steps to highlight values within a range using conditional formatting:

• Create a Workbook instance, and read a source Excel spreadsheet from files.

• Retrieve an Excel worksheet through the Workbook.Worksheets[] property.

• Add a conditional formatting to the specified worksheet with the Worksheet.ConditionalFormats.Add() method.

• Specify the cell range to apply the conditional format using the XlsConditionalFormats.AddRange() method.

• Highlight cells within a certain value range through the XlsConditionalFormats.AddCondition() method.

• Save the modified Excel file as a new one.

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

# Create a Workbook object
workbook = Workbook()

# Load a file from disk
workbook.LoadFromFile("Population.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add a conditional format to the worksheet
conditionalForamt = sheet.ConditionalFormats.Add()

# Specify the range where the conditional format will be applied
conditionalForamt.AddRange(sheet.Range["C2:C11"])

# Create a condition and format the cells that meet the condition
condition = conditionalForamt.AddCondition()
condition.FormatType = ConditionalFormatType.CellValue
condition.Operator = ComparisonOperatorType.Between
condition.FirstFormula = "1000000"
condition.SecondFormula = "8000000"
condition.BackColor = Color.get_Red()

# Save the workbook to an Excel file
workbook.SaveToFile(/ValuesBetweenTwoNumbers.xlsx", ExcelVersion.Version2016

The Conclusion

Conditional formatting is a powerful tool that makes your Excel data more intuitive and easier to interpret. In this guide, we explored how to apply different types of conditional formatting in Excel using Python—from highlighting alternate rows to emphasizing top/bottom values, averages, and values within a specific range. By automating these tasks, you can improve both efficiency and accuracy in data analysis.