Python Openpyxl Charts and Conditional Formatting

Generating Excel reports with python openpyxl charts and conditional formatting is a common task in automation scripts and internal tools. openpyxl supports both features through the same workbook object, so a single script can produce a file where the data is visualized and the values that need attention are highlighted.

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 the chart type by changing the class. A LineChart, PieChart, or ScatterChart uses the same Reference and add_data pattern, so the code above adapts to a different visualization with one line changed.

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 the conditional formatting rules coexist because openpyxl stores them as separate parts of the worksheet. The chart reads the cell values at render time in Excel, and the conditional formatting rules are evaluated by Excel when the file is opened. This means you can add a chart, apply rules, and reorder the operations without affecting the other.

One detail to watch is the anchor cell. Charts are positioned by their top-left anchor, and overlapping a chart with a conditional-formatted range is fine visually, but the chart will cover the cells beneath it. Place charts in empty columns or below the data block.

Performance Considerations for Large Workbooks

Conditional formatting rules are evaluated by Excel on open, not by openpyxl. If you apply a rule to a large range, such as a full column with tens of thousands of rows, 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 has to render it, and rendering cost grows with the number of data points. For a line chart with 50,000 points, Excel will be noticeably sluggish.

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 instantly, 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 will warn or drop rules when the 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.