【Python×Excel】Conditional Formatting(DataBar, IconSet, ColorScale) in openpyxl 【part.1】

Japanese | English

This article describes the “openpyxl” library, which manipulates Excel in Python.

Since Excel is equipped with many functions, it is not possible to give a concise explanation in one article. This article is written as a series of articles divided by major topic.

In previous articles in this series, we have covered the essentials of using openpyxl.Please refer to the related articles as well, as some prior knowledge is required to read the following explanations.

For information on creating and saving a workbook, adding a worksheet, and then getting and manipulating rows, columns, cells, and objects, please refer to .

【Python×Excel】Basic Usage of "openpy-xl" (Install ~ Book, Sheet, Cells)

This is an illustrated explanation of openpyxl, a library for manipulating Excel in Python. From how to install the library to "creating a new Workbook/Worksheet" and "manipulating rows, columns, and cells" are condensed in this one article.

www.shibutan-bloomers.com

2022.03.05

For cell formatting (fonts, borders, and other decorations), click here.

【Python×Excel】Formatting Cells(Font, Fill, Border) in "openpyxl"

The openpyxl library allows you to apply formatting (style) settings such as fonts, hatching, and borders to cells. Each format is defined and managed by a dedicated object. The detailed specifications of the classes and illustrations will be provided.

www.shibutan-bloomers.com

2022.03.12

Now, in this article, the fifth in the series, we will show you how to manipulate “Conditional Formatting of Cells” in openpyxl.

One of the most important functions of Excel is data analysis.This “conditional formatting of cells” is part of that data analysis functionality.I will leave the details to specialized Excel books and websites, but it is easy to extract cells that contain data that meet certain criteria and to discover regularities.

Both “Python” and “Excel” excel at data analysis, and we will aim to improve the quality and efficiency of our work by using the features and useful functions of each.

In addition, please refer to the content structure of this series 【Python x Excel】 as shown here.Due to the extensive of the explanation, this article on “Conditional Formatting of Cells” will also be divided into two parts, the first part (this article) and the second part.

Please stay with us until the end of this article, as you will be able to “do and understand” the following

We will now explain the procedures for applying “conditional formatting to cells” starting in the next section.

1. Set “Conditional Formatting” for Cells

I will now explain the procedure for setting conditional formatting on cells with “openpyxl”.

In this section, we will first give an overview of what conditional formatting is, and then explain the general “formatting flow (procedure)”.Then, I would like to organize the classes and objects needed to define the extraction conditions.

1.1 What’s Conditional Formatting ?

Conditional formatting is one of the data analysis functions provided by Excel.From the Excel menu, the settings window can be accessed via “Home Tab -> Style Group -> Conditional Formatting”.The settings window contains a variety of analysis items as shown in Fig2.

The analysis functions can be broadly classified into “data extraction and statistics” and “visualization of data distribution“.

The former is further divided into “Highlighting Rules” which extract cells that satisfy certain conditions, and “Upper and Lower Rules” which seek statistical rankings within a cell range.

(In the official documentation, they are classified as “Standard/Custom formats.)

The latter, on the other hand, consists of “Data Bar” “Icon Set” and “Color Scale” depending on the means of visualizing data distribution.(In the official documentation, they are classified as “Builtin formats.)

This article explains openpyxl, so this is the extent of the explanation of Excel functions.For other data analysis methods using Excel, please refer to the respective specialized books and websites.

1.2 How to apply Conditional Formatting

Defining and applying conditional formatting programmatically is a bit more complicated.

The core procedure for defining conditional formatting is to create a Rule object that summarizes the “what” and “how” conditions, such as cell selection and distribution.

The method of creating this Rule object must be selected appropriately, as the class to be used and its argument specifications are determined by the desired format.

In the following sections, we will first summarize the definition procedure flow, followed by a detailed explanation of the individual conditional formats.

Fig3 below shows the steps (flow) to apply conditional formatting to a cell.

Thus, the procedure for generating Rule objects can be classified into three patterns, which must be selected appropriately according to the purpose.

＜Three patterns for defining Rule Object＞

Now, we will add an explanation for each definition pattern.(Please note that ”the designation Pattern〇” is for the author’s explanation and is not an official expression.)

「Pattern 1」Flow to create an object (constructor) by setting the extraction conditions directly in the argument of the Rule class (see below)

「Pattern 2」Flow to generate an object from a dedicated class prepared to define extraction conditions for Builtin formats (e.g. Data Bar) and set it as an argument of the Rule class (the most complicated)

「Pattern 3」This is a hybrid of Pattern 1 and Pattern 2.

The “generation of extraction condition definition objects” and “generation of Rule objects” can be handled together in a single class.However, the number of classes that can be supported is limited. (3 Builtin formats + others)

These are three different patterns.

Now, Fig4 summarizes the criteria for using “which patterns can define the analysis items of conditional formatting.

Builtin formats such as data bars have two ways of defining extraction conditions, “Pattern 2” and “Pattern 3,” as shown in Figure 4.Each requires a different class and has a different procedure for creating Rule objects.

Both can do the same things, so it is better to choose the one that is easier to use.

For analysis items classified into Standard/Custom formats, Rule objects are defined in “Pattern 1“.In some exceptional cases, only “cell values” are also provided with the “Pattern 3” procedure (dedicated class).

From the next section, we will explain how to handle each class (object) concretely according to the procedure shown in Figure 3.

2. Required Class for “Conditional Formatting”

This section describes the class needed to set “Conditional Formatting”.

To obtain a “Rule object,” there are various classes, depending on the type of format.

2.1 Define Extraction Condition(Format Object)

Pattern2 requires an object called “FormatObject” that manages data attributes (values, type names, and bounds).This attribute information is used to set “decision thresholds” for data distribution and visualization.

For example, Figure 5 below is an excerpt from the “Icon Set” formatting dialog, in which objects of the FormatObject class define thresholds such as “green” if 67% or more, “yellow” if 33% or more, and red otherwise.

A FormatObject object is defined by specifying the following arguments to the class.Obtains objects for the number of data required for the decision conditions to be extracted.

In the example in Fig5, there may be two or three objects, depending on the type of icon.

The setting value for each argument is selected from the following

The arg:type selects the type of data. The arg:val sets the value, but not if min/max is specified for the data type.The arg:gte specifies whether the threshold bounds are included (True) or not included (False).

2.2 Define Format Type(Rule Object)

As explained in Section 1.2<Fig.3,4>,the “Rule object” defines the conditional part of “Conditional Formatting”.There were three patterns for obtaining this “Rule object” depending on the format type.

Here, we organize the classes required for each pattern.

【Pattern１】(Standard/Custom [Specific String/Date/Upper, Lower])

The required information is passed to the argument of the Rule class, and the object is created.(Fig6) The details of argument specification are explained in the next article <Article 6>.

【Pattern２】(Built-in [Data Bar, Icon Set, Color Scale])

Pattern2 is a procedure specific to Built-in format types.Define a Rule object by passing the “built-in format object” for each type, including the “FormatObject (extracted data attribute)” for <Section 2.1>, to the argument of the Rule class.There are three classes of built-in formats. (Fig7)

【Pattern３】(Cell Value, Data Bar, Icon Set, Color Scale)

In Pattern 2, three classes (objects) must be combined to obtain a Rule object.This can be said to make the program more complicated.openpyxl also provides a way to simplify the procedure and create a Rule object with only one class.

The supported format types are three Built-in types and “cell values” classified as the Standard/Custom (Fig8)

Both Patterns 2 and 3 can do the same thing. Please choose according to the programmer’s preference.

The details of the various classes required for defining Rule objects are explained in Section3.

2.3 Apply Conditional Format to Cells

Next, the Rule object defined in <Section 2.2> is actually applied to the cell.The “Cell Range” to which conditional formatting is applied is managed per worksheet (Worksheet object).

Of course, you can have more than one in the same sheet, so they are grouped into an iterable object called ConditionalFormattingList.

To apply a Rule object to a cell, use the add() method under the ConditionalFormattingList object in the following format.

Specify the target cell range in the arg:range_string as a string, such as ‘A1:C3’.Set the condition definition (Rule object) to the arg:cfRule.

The above is an overview of the classes required to define conditional formatting. The next section will provide concrete examples.

3. Built-in Format(DataBar, IconSet, ColorScale)

<Section 2.2> gave an overview of the class types of “Rule objects” that define extraction conditions. This section introduces concrete class formats and practical examples using sample code.

There are many conditional format types provided by Excel. This article shows examples of the application of “Data Bar” “Icon Set” and “Color Scales” which are classified as “Built-in formats”.

3.1 DataBar

There are two procedures for defining a Rule object for a DataBar. Section 1.2(Fig3, Fig4)

There are two patterns: one is to combine the FormatObject/DataBar/Rule classes, and the other is to define the DataBarRule class by itself.

3.1.1 DataBar by Pattern2

Since the FormatObject object was explained in <Section 2.1>, this section explains the format of the DataBar class.

The correspondence between the Excel settings window and each argument is shown in Fig9.

The arg:cfvo is a list of two data attribute information (FormatObject) <Section 2.1>. Other arguments are as in the format.

As a supplementary note, as shown in Figure 9 and the following comments in the official document, there are some restrictions such as “the color of the bar can only be selected as a gradation with white” “the border cannot be set” and “the direction of the bar cannot be set“.

On the other hand, there are some differences from the Excel specification, such as the addition of a setting to adjust the length of the left and right ends of the bar with the arg:minLength/maxLength.

Currently, openpyxl supports the DataBars as defined in the original specification. Borders and directions were added in a later extension.

openpyxl Official Documents (About the format of the data bar)

Next, obtain a Rule object. The object sets the Rule class to the following format.To set the conditional format to ‘DataBar‘ specify the arg:type as ‘dataBar‘.

Then, then DataBar object is set to arg:dataBar.

【SAMPLE (1)】

Now, let’s see how to handle these classes (objects) with sample code.

The code summary is as follows: apply a “Data Bar” to numeric data entered in two cell ranges (“A1:A10”, “C1:F10”).The bar should be based on the minimum value of each cell range to the left and the maximum value to the right.

Files (.xlsx) read by this program can be downloaded from

from openpyxl import load_workbook

# Import classes needed for DataBar formatting
from openpyxl.formatting.rule import DataBar, FormatObject
from openpyxl.formatting.rule import Rule

wb = load_workbook('Conditional_Formatting.xlsx')
ws = wb.active


# [A]-----------------------------------------------------------------------------------
# Generating FormatObject and DataBar Objects

# Attribute (type) of the reference data in the Data Bar as min and max value
first = FormatObject(type='min')
second = FormatObject(type='max')

# Generates an object by setting the reference data in list format to the arg:dfvo of the DataBar class.
data_bar = DataBar(cfvo=[first, second], color="638EC6", showValue=None, minLength=None, maxLength=None)

print(type(data_bar))
# >> <class 'openpyxl.formatting.rule.DataBar'>


# [B]-----------------------------------------------------------------------------------
# Generating Rule Objects

# Specify 'dataBar' for the arg:type and a DataBar object for the arg:dataBar.
rule = Rule(type='dataBar', dataBar=data_bar)

# [C]-----------------------------------------------------------------------------------
# Apply conditional formatting (set Rule object)

ws.conditional_formatting.add("A1:A10", rule)
ws.conditional_formatting.add("C1:F10", rule)

wb.save('CF_DataBar_apply_En.xlsx')

Now, let me explain the key points of the code.

The results of the execution are shown in Fig11 that follows.

3.1.2 DataBar by Pattern3

The format for obtaining a Rule object from the DataBarRule class (Pattern 3) is as follows.

The starting point (left end) information of the bar is specified directly in the arg:start_value, arg:start_type and the ending point (right end) information in the arg:end_value, arg:end_type.

The correspondence between the Excel settings window and each argument is shown in Fig10.

【SAMPLE (2)】

Let’s check out a real example of the DataBarRule class with sample code.<List2>

The outline of the code is the same as in the previous , with the only difference being the use of the DataBarRule class (Line.14).The code is simplified because the creation of the Rule object can be done in a single line.

Files (.xlsx) read by this program can be downloaded from

from openpyxl import load_workbook

# Import classes needed for DataBar formatting (one class)
from openpyxl.formatting.rule import DataBarRule


wb = load_workbook('Conditional_Formatting.xlsx')
ws = wb.active

# [A]-----------------------------------------------------------------------------------
# Generate Rule(DataBarRule) object

# Specify data attributes (type, value) directly as 
# arguments and even generate Rule objects at once
rule = DataBarRule(start_type='min', start_value=None, end_type='max', end_value=None,
                   color="FF638EC6", showValue="None", minLength=None, maxLength=None)

# [B]-----------------------------------------------------------------------------------
# Apply conditional formatting (set Rule object)

ws.conditional_formatting.add("A1:A10", rule)
ws.conditional_formatting.add("C1:F10", rule)

wb.save('CF_DataBar_apply_En.xlsx')

The results of running List1 and List2 are as follows. Data bars based on minimum and maximum values could be applied to each cell.

The results of <List1>/<List2> can be downloaded from

3.2 IconSet

The procedure for creating IconSet Rule object is similar.

There are two patterns: one is to combine FormatObject/ IconSet /Rule classes, and the other is to define the IconSetRule class by itself.

3.2.1 IconSet by Pattern2

IconSet objects are obtained by specifying arguments in the following format.

Setting data attribute(FormatObject) in arg:cfvo is the same as in the previous ”Data Bar”. But the number of objects required(number of elements in the list) depends on the type of icon specified in the arg:iconSet.

For example, ‘3Symbols2’ will prepare FormatObjects for 3 elements, and ‘4ArrowsGray’ will prepare FormatObjects for 4 elements.

The correspondence between the Excel settings window and each argument is shown in Fig12.

The arg:iconSet sets the icon type.Excel provides the following icons, so specify the default option constants as strings (enclosed in ”).

Next, obtains an object by specifying ”iconSet” in the arg:type of the Rule class. Then, set the arg:iconSet to the “IconSet object” defined earlier.

【SAMPLE (3)】

Now, let’s see how to handle these classes (objects) with sample code.<List3>

The code summary is to apply “3TrafficLights1” (signal icon) to a range of cells containing numerical data (percentages).

Files (.xlsx) read by this program can be downloaded from

from openpyxl import load_workbook

# Import classes needed for IconSet formatting
from openpyxl.formatting.rule import IconSet, FormatObject
from openpyxl.formatting.rule import Rule

wb = load_workbook('Conditional_Formatting.xlsx')
ws = wb.active


# [A]-----------------------------------------------------------------------------------
# Generationof FormatObject and IconSet objects

# Attributes (type and value) of the reference data of the icon set as "%" respectively
first = FormatObject(type='percent', val=0)
second = FormatObject(type='percent', val=33)
third = FormatObject(type='percent', val=67)

# Generate an object by setting the icon of a traffic light to the arg:iconSet of the IconSet class
# and setting a list of criteria data in the arg:cfvo.
iconset = IconSet(iconSet='3TrafficLights1', cfvo=[first, second, third], showValue=None, reverse=None)

print(type(iconset))
# Confirmation of type　>><class 'openpyxl.formatting.rule.IconSet'>


# [B]-----------------------------------------------------------------------------------
# Generating Rule Objects

# Specify 'iconSet' for the arg:type and IconSet object for the arg:iconSet
rule = Rule(type='iconSet', iconSet=iconset)

print(type(rule))
# Confirmation of type　>><class 'openpyxl.formatting.rule.Rule'>


# [C]-----------------------------------------------------------------------------------
# Apply conditional formatting (set Rule object)

ws.conditional_formatting.add("A1:A10", rule)
ws.conditional_formatting.add("C1:F10", rule)

print(type(ws.conditional_formatting))
# Confirmation of type >> <class 'openpyxl.formatting.formatting.ConditionalFormattingList'>

wb.save('CF_IconSet_apply_En.xlsx')

Now, let me explain the key points of the code.

The results of the execution are shown in Fig15, which follows.

3.2.2 IconSet by Pattern3

The format for retrieving a Rule object from the IconSetRule class (Pattern 3) is as follows.

The icon type is specified by a string constant (Figure13) in the arg:icon_style. The type of data is specified in the arg:type and the value is specified in the arg:values for the required number of elements in a list format.

The correspondence between the Excel settings window and each argument is shown in Fig14.

【SAMPLE (4)】

Let’s check out a real example of the IconSetRule class with sample code.<List4>

The outline of the code is almost the same as the previous , with the only difference being the use of the IconSetRule class (line 14).The code is simplified because the creation of the Rule object can be done in a single line.

Files (.xlsx) read by this program can be downloaded from

from openpyxl import load_workbook

# Import classes needed for IconSet formatting (one class)
from openpyxl.formatting.rule import IconSetRule

wb = load_workbook('Conditional_Formatting.xlsx')
ws = wb.active

# [A]-----------------------------------------------------------------------------------
# generation of Rule(IconSetRule) object

# Specify data attributes (type, value) directly as arguments and even generate Rule objects at once
# The type of icon is specified(3-color traffic light)
rule = IconSetRule(icon_style='3TrafficLights1', type='percent', values=[0, 33, 67], showValue=None, reverse=None)

print(type(rule))
# 型の確認 >><class 'openpyxl.formatting.rule.Rule'>


# [B]-----------------------------------------------------------------------------------
# Apply conditional formatting (set Rule object)

ws.conditional_formatting.add("A1:A10", rule)
ws.conditional_formatting.add("C1:F10", rule)

print(type(ws.conditional_formatting))
# >> <class 'openpyxl.formatting.formatting.ConditionalFormattingList'>

wb.save('CF_IconSet_apply_En.xlsx')

The results of running List3 and List4 are as follows: a set of icons was applied with three percentage values (%) as thresholds (0%, 33%, 67%).

The results of <List3>/<List4> can be downloaded from

3.3 ColorScale

The procedure for creating a Rule object for ColorScale is similar.

There are two patterns: one is to combine FormatObject/ ColorScale /Rule classes, and the other is to define ColorScaleRule class by itself.

3.3.1 ColorScale by Pattern2

The ColorScale object is obtained by specifying arguments as in the following format.

It is the same as before to set the data threshold (FormatObject) in the arg:cfvo.

The arg:color is a list of color information (Color object etc…) to be paired with the FormatObject (2 or 3 elements in the order of starting point, [middle], and end point).

The correspondence between the Excel settings window and each argument is shown in Fig16.

Next, obtain a Rule object. The arg:type of the Rule class should be ‘colorScale‘. And for the arg:colorScale, specify the “ColorScale object”

【SAMPLE (5)】

Now, let’s see how each classes is handled in a sample program.<List5>

A summary of the code is as follows

In [A], a FormatObject object is generated to serve as the reference data.

In the example, three reference data are prepared, with “max” (maximum) and “min” (minimum) as the starting and ending point types, and “num” (value) as the middle point. Color information for each data is also prepared accordingly.

The object is obtained by passing the next created FormatObject object and the list of color information to the ColorScale class argument.

Then, in [B], a Rule object is created by specifying ‘colorScale’ in the arg:type of the Rule class and the ColorScale object from [A] in the arg:colorScale.

Finally, [C] applies the defined format(Rule object) to the specified cell range (A1:A10, C1:F10).

from openpyxl import load_workbook
from openpyxl.styles import Color

# Import classes needed for ColorScale formatting
from openpyxl.formatting.rule import ColorScale, FormatObject
from openpyxl.formatting.rule import Rule

wb = load_workbook('Conditional_Formatting_num.xlsx')
ws = wb.active

# [A]-----------------------------------------------------------------------------------
# Generation of FormatObject and ColorScale objects

# Reference data (starting point (min value) and ending point (max value), middle is value) object
first = FormatObject(type='min')
mid = FormatObject(type='num', val=50)
last = FormatObject(type='max')

# Prepare color information (Color object) in list format
# in the order of starting point, intermediate point, and ending point
colors = [Color('FFCC00'), Color('3366FF'), Color('99CC00')]

# Generate objects from ColorScale class with reference
# data and color information as arguments
color_scale = ColorScale(cfvo=[first, mid, last], color=colors)


# [B]-----------------------------------------------------------------------------------
# Generating Rule Objects

# Specify 'colorScale' for the arg:type and a ColorScale object for the arg:colorScale
rule = Rule(type='colorScale', colorScale=color_scale)


# [C]-----------------------------------------------------------------------------------
# Apply conditional formatting (set Rule object)

ws.conditional_formatting.add("A1:A10", rule)
ws.conditional_formatting.add("C1:F10", rule)


wb.save('CF_ColorScale_apply_En.xlsx')

The results of the execution are shown in Fig18, which follows.

3.3.2 ColorScale by Pattern3

The format for obtaining a Rule object from the ColorScaleRule class is as follows. Generates objects directly by specifying data attributes (value, type, color) in the args:(min_, mid_, max_*)

The correspondence between the Excel settings window and each argument is shown in Fig17.

【SAMPLE (6)】

Now, let’s check the usage of the classes in a sample program. <List6>

Unlike Pattern 2 in <2.3.1> above, in [A], the ColorScaleRule class alone generates up to a Rule object at a time. The number of arguments increases, but the overall code is cleaner and more coherent.

Files (.xlsx) read by this program can be downloaded from

from openpyxl import load_workbook

# Import classes needed for ColorScale formatting (one class)
from openpyxl.formatting.rule import ColorScaleRule

wb = load_workbook('Conditional_Formatting_num.xlsx')
ws = wb.active

# [A]-----------------------------------------------------------------------------------
# Creation of Rule(ColorScaleRule) object

# Specify data attributes (type, value) and color information directly as arguments
# and generate Rule objects at once
rule = ColorScaleRule(start_type='min', start_value=None, start_color='FFCC00',
                        mid_type='percentile', mid_value=50, mid_color='3366FF',
                        end_type='max', end_value=None, end_color='99CC00')

# [B]-----------------------------------------------------------------------------------
# Apply conditional formatting (set Rule object)

ws.conditional_formatting.add("A1:A10", rule)
ws.conditional_formatting.add("C1:F10", rule)

wb.save('CF_ColorScale_apply_En.xlsx')

The results of the execution of List5 and List6 are as follows. The “Color Scale” that changes color by three criteria (maximum, 50, minimum) could be applied to the target cell range.

The results of <List5>/<List6> can be downloaded from

4. Summary

How was it?

We have described how to use the external library “openpyxl” to manipulate Excel and set conditional formatting on cells.

Python” and “Excel” are both good at data analysis, so they are an excellent match. Many of you are familiar with Excel analysis tools, and there is a wealth of related information available.

Instead of using Python for all data processing, we can integrate it well with Excel and say, “This process and this function is done in Excel! Data visualization is in Excel!” It may be a good idea to be able to use them in different ways.

I hope this “openpyxl” will be the bridge between the two.

Let me summarize the main points of this article.

We will continue with the second part of Conditional Formatting in the next issue. We’ll be discussing “cell highlighting rules,” “upper and lower limit rules,” and more, so stay tuned!

Click here for the link.

【Python×Excel】Conditional Format (Cell Highlighting, Top/Bottom) in openpyxl【Part.2】

This section explains how to manipulate Excel's conditional formatting in the openpyxl library. This article uses illustrations to show examples of applying the "Cell Highlighting Tool" and "Upper/Lower Rules" as custom formatting.

www.shibutan-bloomers.com

2022.03.26

Thank you for reading to the end.

Japanese | English