Conditional formatting is a powerful feature in Microsoft Excel that allows users to apply formatting rules to cells based on specific conditions or criteria. It provides a quick and efficient way to visually highlight and analyze data, making it easier to identify trends, patterns, and outliers. With conditional formatting, users can customize the appearance of cells, such as font color, cell background, and borders, to draw attention to specific data points or results. In this article, you will learn how to programmatically apply conditional formatting in Excel in Python using Spire.XLS for Python.

• Alternate Row Colors in Excel Using Conditional Formatting in Python
• Highlight Top or Bottom Ranked Values in Excel in Python
• Highlight Values Below or Above Average in Excel in Python
• Highlight Values Between Two Numbers in Excel in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Alternate Row Colors in Excel Using Conditional Formatting in Python

Apply alternating row colors enhances the readability and visual appeal of a spreadsheet. By using different background colors for adjacent rows, it makes it easier to distinguish and follow individual rows of data. The following are the steps to alternate row colors in Excel using conditional formatting with Spire.XLS for Python.

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet from the workbook through Workbook.Worsheets[index] property.
• Add a conditional format to the worksheet using Worksheet.ConditionalFormats.Add() method and return an object of XlsConditionalFormats class.
• Specify the cell range where the conditional formatting will be applied using XlsConditionalFormats.AddRange() method.
• Add a condition using XlsConditionalFormats.AddCondition() method to change the color of even rows.
• Add another condition to change the color of odd rows.
• Save the workbook to an Excel file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()

# Load a file from disk
workbook.LoadFromFile("C:/Users/Administrator/Desktop/input.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, sheet.LastRow, sheet.LastColumn])

# 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("output/AlternateRowColors.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Highlight Top or Bottom Ranked Values in Excel

Highlighting top-ranked or bottom-ranked values in Excel can quickly identify the highest or the lowest values within a range or dataset, making it easier to analyze and interpret the data. The following are the steps to highlight top or bottom ranked values in Excel using conditional formatting with Spire.XLS for Python.

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet from the workbook through Workbook.Worsheets[index] property.
• Add a conditional format to the worksheet using Worksheet.ConditionalFormats.Add() method and return an object of XlsConditionalFormats class.
• Specify the cell range where the conditional formatting will be applied using XlsConditionalFormats.AddRange() method.
• Add a condition using XlsConditionalFormats.AddCondition() method to change the color of the cells that have top ranked or bottom ranked values.
• Save the workbook to an Excel file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()

# Load a file from disk
workbook.LoadFromFile("C:/Users/Administrator/Desktop/input.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["I2:I15"])

# 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("output/TopOrBottomValues.xlsx", ExcelVersion.Version2016)

Highlight Values Below or Above Average in Excel

To quickly identify data points that are significantly higher or lower than the average, you can use conditional formatting as well. Here are the detailed steps.

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet from the workbook through Workbook.Worsheets[index] property.
• Add a conditional format to the worksheet using Worksheet.ConditionalFormats.Add() method and return an object of XlsConditionalFormats class.
• Specify the cell range where the conditional formatting will be applied using XlsConditionalFormats.AddRange() method.
• Add a condition using XlsConditionalFormats.AddCondition() method to change the color of the cells with values above or below the average.
• Save the workbook to an Excel file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()

# Load a file from disk
workbook.LoadFromFile("C:/Users/Administrator/Desktop/input.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["I2:I15"])

# 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("output/AboveOrBelowAverage.xlsx", ExcelVersion.Version2016)

Highlight Values Between Two Numbers in Excel

Highlighting values between a specific range helps you quickly identify and focus on the relevant data points that fall within the range. The following are the steps to highlight values between two numbers using conditional formatting with Spire.XLS for Python.

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet from the workbook through Workbook.Worsheets[index] property.
• Add a conditional format to the worksheet using Worksheet.ConditionalFormats.Add() method and return an object of XlsConditionalFormats class.
• Specify the cell range where the conditional formatting will be applied using XlsConditionalFormats.AddRange() method.
• Add a condition using XlsConditionalFormats.AddCondition() method to change the color of the cells with values between two specific numbers.
• Save the workbook to an Excel file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()

# Load a file from disk
workbook.LoadFromFile("C:/Users/Administrator/Desktop/input.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["I2:I15"])

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

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

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.