Python XlsxWriter Charts and Conditional Formatting

XlsxWriter is a Python library for writing Excel 2007+ XLSX files without needing Excel installed. Two features that frequently appear together in reporting scripts are charts and conditional formatting: conditional formatting highlights cells based on their values, and charts visualize the same underlying data. This article shows how to use python xlsxwriter charts and conditional formatting together in one workbook, from basic rules to combined reports.

Creating a Workbook with Data

Every XlsxWriter script follows the same skeleton: create a workbook, add a worksheet, write data, and close the workbook. The close() call is required; it finalizes the XLSX package and flushes any pending content to disk.

import xlsxwriter

workbook = xlsxwriter.Workbook("sales_report.xlsx")
worksheet = workbook.add_worksheet("Sales")

headers = ["Region", "Q1", "Q2", "Q3", "Q4"]
data = [
    ["North", 120, 145, 132, 158],
    ["South", 98, 110, 121, 134],
    ["East", 145, 138, 152, 149],
    ["West", 112, 129, 141, 155],
]

worksheet.write_row("A1", headers)
for row_idx, row in enumerate(data, start=1):
    worksheet.write_row(row_idx, 0, row)

workbook.close()

The data occupies A1:E5: headers in row 1, and four regions with quarterly values in rows 2 through 5. This layout is the base for both conditional formatting and charts.

Adding Conditional Formatting Rules

Conditional formatting is applied with worksheet.conditional_format(cell_range, options). The type key in the options dictionary selects the rule type, and the remaining keys configure its behavior.

Cell Value Rules

The cell type compares each cell against a value using a criteria operator. You typically pair it with a Format object created by workbook.add_format() so the highlighted cells are visually distinct.

format_high = workbook.add_format({"bg_color": "#C6EFCE", "font_color": "#006100"})
format_low = workbook.add_format({"bg_color": "#FFC7CE", "font_color": "#9C0006"})

worksheet.conditional_format("B2:E5", {
    "type": "cell",
    "criteria": ">=",
    "value": 140,
    "format": format_high,
})

worksheet.conditional_format("B2:E5", {
    "type": "cell",
    "criteria": "<",
    "value": 110,
    "format": format_low,
})

Valid criteria for cell include >, >=, <, <=, ==, and !=. The value can be a number, a string, or a formula expression.

Data Bars and Color Scales

Data bars and color scales require no format object; they are self-contained visual rules.

worksheet.conditional_format("B2:E5", {"type": "data_bar"})
worksheet.conditional_format("B2:E5", {"type": "2_color_scale"})

For color scales, you can control the gradient endpoints with min_color and max_color:

worksheet.conditional_format("B2:E5", {
    "type": "2_color_scale",
    "min_color": "#F8696B",
    "max_color": "#63BE7B",
})

Formula-Based Rules

When the built-in rule types do not cover the logic you need, use type: "formula". The criteria string is an Excel formula evaluated relative to the top-left cell of the range.

worksheet.conditional_format("B2:E5", {
    "type": "formula",
    "criteria": "=B2>AVERAGE($B$2:$E$5)",
    "format": format_high,
})

This highlights every cell that exceeds the average of the entire range. The relative reference B2 is adjusted for each cell in the range, while the absolute reference $B$2:$E$5 stays fixed.

Applying Conditional Formatting to Ranges

When multiple rules target the same range, Excel evaluates them in the order they were added and stops at the first match. This means rule order matters: a broad rule added before a narrow one can mask the narrow rule.

For non-contiguous ranges, pass a space-separated range string:

worksheet.conditional_format("B2:B5 D2:D5", {
    "type": "cell",
    "criteria": ">=",
    "value": 140,
    "format": format_high,
})

This applies the rule to both B2:B5 and D2:D5 in one call. When a formula-based rule is used with multiple ranges, the formula still references the first cell of the first range.

Creating Charts from Worksheet Data

Charts are created independently of worksheets and then inserted into a worksheet at a specific cell position. The chart type is set at creation time.

chart = workbook.add_chart({"type": "column"})

chart.add_series({
    "name": "=Sales!$B$1",
    "categories": "=Sales!$A$2:$A$5",
    "values": "=Sales!$B$2:$B$5",
})

chart.set_title({"name": "Q1 Sales by Region"})
chart.set_x_axis({"name": "Region"})
chart.set_y_axis({"name": "Revenue"})

worksheet.insert_chart("G2", chart)

The name, categories, and values keys accept Excel range strings or Sheet1!$A$1:$A$5 style references. The worksheet name must match the worksheet you created, including the sheet name.

Multiple Series in One Chart

To chart all four quarters, loop over the columns and build a series for each:

for col in range(1, 5):
    col_letter = xlsxwriter.utility.xl_col_to_name(col)
    chart.add_series({
        "name": f"=Sales!${col_letter}$1",
        "categories": "=Sales!$A$2:$A$5",
        "values": f"=Sales!${col_letter}$2:${col_letter}$5",
    })

xl_col_to_name() converts a zero-based column index to its letter equivalent, so 1 becomes B, 2 becomes C, and so on.

Chart Types

The chart type is fixed when the chart is created; you cannot change it after adding series.

Combining Charts and Conditional Formatting

Conditional formatting and charts operate on the same cell data but serve different purposes. The chart reads the underlying cell values, not the conditional formatting colors. So you can highlight outliers in the worksheet while the chart still plots the raw values.

# Reuse the earlier data setup
worksheet.conditional_format("B2:E5", {
    "type": "2_color_scale",
    "min_color": "#F8696B",
    "max_color": "#63BE7B",
})

chart = workbook.add_chart({"type": "line"})
chart.add_series({
    "name": "=Sales!$B$1",
    "categories": "=Sales!$A$2:$A$5",
    "values": "=Sales!$B$2:$B$5",
})
worksheet.insert_chart("G2", chart)

This is the typical reporting pattern: the worksheet draws attention to problem cells with color, and the chart gives the reader a quick overview of the trend. Both features can reference the same range without interfering with each other.

Performance and Memory Considerations

XlsxWriter streams content to disk rather than building the entire XLSX file in memory, so memory usage stays low even for large worksheets. The main cost of conditional formatting is file size: every rule is stored as XML inside the worksheet, and a large number of rules over big ranges can slow down Excel's rendering when the file is opened.

Keep rules scoped to the smallest range that covers your data. Overlapping rules on the same cells are evaluated in order and increase the rendering cost without adding value.

Charts also increase file size because XlsxWriter stores a cached copy of the series data inside the chart XML. For very large datasets, chart aggregated values rather than every row. A chart of quarterly totals is more readable and produces a smaller file than a chart of thousands of individual data points.

Compatibility Notes

Conditional formatting features vary by Excel version. Data bars, color scales, and icon sets were introduced in Excel 2007 and received expanded options in Excel 2010. When the workbook is opened in LibreOffice or Google Sheets, the rendering of both conditional formatting and charts may differ from Excel, especially for data bars and icon sets.

Chart types also have compatibility constraints. Stock charts require multiple series and are typically used for financial data. Radar charts and some scatter chart subtypes render differently across spreadsheet applications. If the workbook will be consumed by a specific tool, verify the output in that tool rather than assuming Excel-compatible rendering everywhere.