Python Openpyxl Charts and Conditional Formatting
Add charts and conditional formatting to Excel workbooks with Python openpyxl. Covers chart references, common chart types, conditional formatting rules, and performance considerations.

Generating Excel reports with Python openpyxl often means adding charts and conditional formatting to highlight the values that need attention. Both features work through the same workbook object, so a single script can produce a file where the data is visualized and the important cells are marked.
Setting Up the Workbook and Source Data
Start with a workbook and a worksheet that holds the data the report will reference. Both charts and conditional formatting rules operate on cell ranges, so the data layout determines how you configure each feature.
from openpyxl import Workbook
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
ws.title = "Sales"
headers = ["Month", "Revenue", "Target", "Region"]
ws.append(headers)
rows = [
["Jan", 42000, 40000, "North"],
["Feb", 38500, 40000, "North"],
["Mar", 47000, 40000, "North"],
["Apr", 51000, 45000, "North"],
["May", 53000, 45000, "North"],
["Jun", 48000, 45000, "North"],
]
for row in rows:
ws.append(row)
for cell in ws[1]:
cell.font = Font(bold=True)The data is deliberately simple: a month column, two numeric columns, and a region column. The numeric columns are what the chart and the conditional formatting rules will reference.
Adding Charts to a Worksheet
openpyxl provides chart objects that you configure with a Reference and then attach to a worksheet. The Reference points at the cells that contain the data, and the chart object determines the chart type.
from openpyxl.chart import BarChart, Reference
chart = BarChart()
chart.title = "Monthly Revenue vs Target"
chart.y_axis.title = "Amount"
chart.x_axis.title = "Month"
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
categories = Reference(ws, min_col=1, min_row=2, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, "F2")The Reference for the data starts at row 1 because the header row is included; titles_from_data=True tells openpyxl to use the first row as series names. The categories reference starts at row 2 because the month names are data, not headers. When you open the generated file, the chart appears anchored at cell F2.
You can switch between a BarChart, LineChart, and ScatterChart by changing the class; they use the same Reference and add_data pattern. A PieChart also uses add_data, but it is designed for a single series, so for a pie chart you would typically reference one numeric column instead of two.
Applying Conditional Formatting Rules
Conditional formatting in openpyxl is handled through the conditional_formatting collection on a worksheet. You add a rule to a range, and the rule describes the condition plus the style applied when the condition matches.
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill
green_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")
red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
ws.conditional_formatting.add(
"C2:C7",
CellIsRule(operator="greaterThanOrEqual", formula=["B2"], fill=green_fill)
)
ws.conditional_formatting.add(
"C2:C7",
CellIsRule(operator="lessThan", formula=["B2"], fill=red_fill)
)The first rule highlights cells in the Target column when the value is greater than or equal to the corresponding Revenue cell. The second rule highlights the opposite case. Note that the formula uses a relative reference (B2), which openpyxl applies relative to the top-left cell of the range.
For a color scale, which shades cells based on their position between a minimum and maximum value, use ColorScaleRule:
from openpyxl.formatting.rule import ColorScaleRule
ws.conditional_formatting.add(
"B2:B7",
ColorScaleRule(
start_type="min", start_color="F8696B",
mid_type="percentile", mid_value=50, mid_color="FFEB84",
end_type="max", end_color="63BE7B"
)
)ColorScaleRule applies a gradient across the range. The mid point is optional; without it, openpyxl produces a two-color scale.
Combining Charts and Conditional Formatting in a Report
A practical report often needs both features on the same sheet. The chart summarizes the overall pattern, and the conditional formatting marks individual cells that need attention. There is no conflict between the two; they operate on the same ranges independently.
from openpyxl.chart import LineChart
line = LineChart()
line.title = "Revenue Trend"
line_data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=7)
line_cats = Reference(ws, min_col=1, min_row=2, max_row=7)
line.add_data(line_data, titles_from_data=True)
line.set_categories(line_cats)
ws.add_chart(line, "F20")The chart and conditional formatting rules coexist because openpyxl stores them as separate parts of the worksheet. Excel renders the chart from the referenced cells, and it evaluates the conditional formatting rules when the file is opened. This means you can add a chart, apply rules, and reorder those operations without affecting the other.
One detail to watch is the anchor cell. Charts are positioned by their top-left anchor. A chart can overlap a conditionally formatted range without causing an error, but it will cover the cells beneath it, so place charts in empty columns or below the data block.
Performance Considerations for Large Workbooks
Conditional formatting rules are evaluated by Excel, not by openpyxl. If you apply a rule to a large range, such as a full column, Excel must evaluate every cell in that range when the file is opened. This can make the workbook slow to load and slow to respond to edits.
The same applies to charts. A chart that references a large range is not expensive to create, but Excel must render it, and rendering cost grows with the number of data points. A line chart with tens of thousands of points can make Excel noticeably slower to respond.
Two practical mitigations:
- Limit conditional formatting ranges to the rows that actually contain data. A rule applied to
A1:A100000on a sheet with 100 rows forces Excel to evaluate 99,900 empty cells. - Use a chart series with aggregated data rather than raw rows. A monthly summary chart over 12 points renders quickly, while a daily chart over 365 points is heavier.
openpyxl itself does not evaluate the rules or render the charts. Its job is to serialize the definitions into the XLSX package. So the generation time stays low even for large ranges; the cost moves to the reader side.
Compatibility and Version Constraints
Conditional formatting and chart support have changed across openpyxl versions. The API shown here — the conditional_formatting collection and ColorScaleRule — is the modern interface. Older releases used a different module layout, so code written for those versions will not run unchanged on current openpyxl, and code written against the current API will fail on old releases.
Excel also imposes its own limits. A worksheet can hold a bounded number of conditional formatting rules, and Excel may warn about or drop rules when that limit is exceeded. Charts are limited by the number of series and data points that Excel can render reliably.
When you target a specific Excel version, test the generated file in that version. The XLSX format is standardized, but the rendering behavior of charts and conditional formatting is Excel's responsibility, and minor version differences can change how a rule or chart appears.
If you are generating files for a web application where users download the workbook, keep the file size in mind. Charts add a small amount of XML, but conditional formatting rules add up quickly when you apply many rules to many ranges. A few rules on a handful of columns is negligible; hundreds of rules across dozens of columns will bloat the file and slow Excel's parsing.
Try matching browser tools
All toolsSmall utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related Python guides
All Python guidesPython iter(): How the Built-in Function Works
Explains Python's iter() built-in: how it creates iterators, the two-argument sentinel form, custom iterator classes, and common edge cases.
Python Pika: Queues, Exchanges, Routing Keys, and Acknowledgements
Learn how to use Python's Pika client with RabbitMQ: declare queues and exchanges, publish with routing keys, consume messages, and handle acknowledgements reliably.
Python Kafka: kafka-python vs Confluent Client
python kafka python vs confluent kafka: Compare kafka-python and Confluent Kafka Python clients: API differences, performance, feature coverage, and when to choose eac...
Python Kafka Offsets, Partitions, and Manual Commits
Learn how Kafka offsets and partitions work and how to manually commit offsets in Python consumers with kafka-python and confluent-kafka. Covers sync vs async commits, rebalances, and seeking.
More in Python
All Python guidesThe guides filed next to this one in Python, in library order.