Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Python XlsxWriter Charts and Conditional Formatting

Use Python XlsxWriter to add Excel charts and conditional formatting to reports. Covers cell rules, data bars, formula rules, chart series, and combining both features.

~4 min readPython
#XlsxWriter#Excel automation#conditional formatting#data visualization
Stylized spreadsheet with green and red highlighted cells next to a blue column chart, representing XlsxWriter conditional formatting and charts.

XlsxWriter is a Python library for writing Excel 2007+ XLSX files without requiring Excel to be installed. Charts and conditional formatting are two features that often appear together in reporting scripts: conditional formatting highlights cells based on their values, and charts visualize the same underlying data. This article shows how to combine them in one workbook with XlsxWriter, from basic rules to a report that uses both.

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.

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

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

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

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

python
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

XlsxWriter writes conditional formatting rules in the order you add them, and Excel evaluates them in that priority order. A matching rule does not automatically stop later rules unless stop_if_true is enabled. Rule order matters when rules set conflicting styles: a broad rule added before a narrow one can mask the narrow rule.

For non-contiguous ranges, use the multi_range option:

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

This applies the rule to both B2:B5 and D2:D5 in one call. The base range passed to conditional_format() should appear in multi_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.

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

For categories and values, use range-reference formulas such as =Sales!$B$2:$B$5. For name, use either a plain string such as "Q1 Sales by Region" or a reference like =Sales!$B$1. The worksheet name in any reference must match the worksheet you created.

Multiple Series in One Chart

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

python
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

Chart typetype valueTypical use
ColumncolumnComparing categories
BarbarHorizontal category comparison
LinelineTrends over time
PiepiePart-to-whole proportions
ScatterscatterXY relationships
DoughnutdoughnutPart-to-whole with multiple series

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.

python
# 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!$A$2", "categories": "=Sales!$B$1:$E$1", "values": "=Sales!$B$2:$E$2", }) 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 File Size Considerations

The main practical 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 by Excel and increase 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, use aggregate values in charts 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 gained additional 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.

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.