Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

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.

~5 min readPython
#openpyxl#Excel automation#data visualization#conditional formatting
A spreadsheet grid with a bar chart and color-highlighted cells illustrating openpyxl chart and conditional formatting output.

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.

python
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.

python
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.

python
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:

python
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.

python
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:A100000 on 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 tools

Small utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.

All Python guides

More in Python

All Python guides

The guides filed next to this one in Python, in library order.