Python Excel AutomationPractical guides for developers
Start learning
Guide
Formatting And Charting Excel Reports With PythonDeep dive

Add a Chart to an Excel File with xlsxwriter

Insert a real Excel chart from Python: series built from row counts, axis and label formatting, a combined column-and-line chart on a secondary axis, and placement that survives changing data.

An Excel chart written by xlsxwriter is a real chart object: it references cells, recalculates when someone edits them, and can be restyled by hand afterwards. That is the difference between it and a rendered image pasted into a sheet — and it is why a chart built from Python is worth the extra ten lines over exporting a picture.

This guide, part of Building Excel Reports with xlsxwriter, covers series ranges that follow the data, axis and label formatting that make the chart readable, and the combined chart with a secondary axis that most monthly reports eventually need.

How a chart series points back at the worksheet A series has three parts. Categories point at the label column, values point at the number column, and the name is a single header cell. Each is given as a list of sheet name, first row, first column, last row and last column, computed from the number of rows written rather than typed. the worksheet Region Amount South 274.75 North 150.25 East 75.00 "name": ["Summary", 0, 1] — one header cell "categories": ["Summary", 1, 0, n, 0] — the labels "values": ["Summary", 1, 1, n, 1] — the numbers n comes from len(df), never from a typed row number

Prerequisites

Bash
pip install xlsxwriter pandas

No Excel installation is needed — xlsxwriter writes the chart definition into the file directly, and Excel or LibreOffice renders it on open.

Step 1: Write the data the chart will reference

A chart plots cells, so the data has to exist in the sheet first:

Python
import pandas as pd
import xlsxwriter

df = pd.DataFrame({
    "month":  ["Jan", "Feb", "Mar", "Apr", "May", "Jun"],
    "amount": [412.0, 388.5, 501.25, 466.0, 590.75, 623.4],
    "margin": [0.31, 0.29, 0.34, 0.33, 0.36, 0.38],
})

wb = xlsxwriter.Workbook("charted.xlsx")
ws = wb.add_worksheet("Summary")

header = wb.add_format({"bold": True, "bg_color": "#1F4E78",
                        "font_color": "white", "border": 1})
money = wb.add_format({"num_format": '#,##0.00', "border": 1})
pct = wb.add_format({"num_format": "0.0%", "border": 1})

ws.write_row(0, 0, ["Month", "Amount", "Margin"], header)
for i, row in enumerate(df.itertuples(index=False), start=1):
    ws.write_string(i, 0, row.month)
    ws.write_number(i, 1, row.amount, money)
    ws.write_number(i, 2, row.margin, pct)

ws.set_column("A:A", 12)
ws.set_column("B:B", 14)
ws.set_column("C:C", 12)

LAST = len(df)          # zero-based index of the last data row

Keeping LAST as a variable is the single habit that prevents most chart bugs. Every range below is expressed relative to it, so a month added to the DataFrame extends the chart automatically.

Step 2: A column chart with readable axes

Python
chart = wb.add_chart({"type": "column"})
chart.add_series({
    "name":       ["Summary", 0, 1],
    "categories": ["Summary", 1, 0, LAST, 0],
    "values":     ["Summary", 1, 1, LAST, 1],
    "fill":       {"color": "#5B5CF0"},
    "gap":        40,
    "data_labels": {"value": True, "num_format": "#,##0",
                    "font": {"size": 9, "color": "#5B6780"}},
})
chart.set_title({"name": "Monthly amount",
                 "name_font": {"size": 13, "bold": True}})
chart.set_x_axis({"name": "Month", "num_font": {"size": 10}})
chart.set_y_axis({"name": "Amount", "num_format": '#,##0',
                  "major_gridlines": {"visible": True,
                                      "line": {"color": "#E4E8F2"}}})
chart.set_legend({"none": True})
chart.set_size({"width": 640, "height": 340})
ws.insert_chart("E2", chart)

Three of those settings do most of the readability work. num_format on the y-axis stops a currency axis showing 500000 instead of 500,000. set_legend({"none": True}) removes the legend for a single-series chart, where it explains nothing and takes a fifth of the width. And data_labels puts the value on the bar, which for six categories is more useful than making the reader trace back to the axis.

set_size takes pixels; insert_chart also accepts {"x_scale": 1.5, "y_scale": 1.2} if you would rather scale than specify. Anchoring at a cell well to the right of the data means the chart does not cover the table when someone widens a column.

Step 3: Combine two series with different units

Amount in currency and margin in percent cannot share an axis meaningfully. The idiomatic answer is two chart objects combined, with the second on a secondary axis:

Python
column = wb.add_chart({"type": "column"})
column.add_series({
    "name":       ["Summary", 0, 1],
    "categories": ["Summary", 1, 0, LAST, 0],
    "values":     ["Summary", 1, 1, LAST, 1],
    "fill":       {"color": "#5B5CF0"},
})

line = wb.add_chart({"type": "line"})
line.add_series({
    "name":       ["Summary", 0, 2],
    "categories": ["Summary", 1, 0, LAST, 0],
    "values":     ["Summary", 1, 2, LAST, 2],
    "line":       {"color": "#0F766E", "width": 2.25},
    "marker":     {"type": "circle", "size": 6,
                   "fill": {"color": "#0F766E"}},
    "y2_axis":    True,                       # plot against the right-hand axis
})

column.combine(line)
column.set_title({"name": "Amount and margin by month"})
column.set_y_axis({"name": "Amount", "num_format": '#,##0'})
column.set_y2_axis({"name": "Margin", "num_format": "0%",
                    "min": 0, "max": 0.5})
column.set_legend({"position": "bottom"})
column.set_size({"width": 720, "height": 360})
ws.insert_chart("E20", column)

combine() is called on the primary chart and takes the secondary one; only the primary chart is inserted. Fixing min and max on the secondary axis is worth doing deliberately — an auto-scaled percentage axis rescales every month as the data moves, which makes two consecutive reports look different when nothing changed. Here the legend earns its place, because there are now two series to tell apart.

A combined chart with a fixed secondary axis Columns are plotted against the left axis in currency and a line against a right-hand axis in percent. The secondary axis is pinned from zero to fifty percent, so the line's position means the same thing in this month's report as it did in last month's. Amount and margin by month 700 350 0 50% 25% 0% Jan Feb Mar Apr May Jun Amount (left) Margin (right, fixed 0–50%)

Step 3b: Place it where it will not be in the way

Anchoring looks trivial until a reader widens a column and the chart lands on top of the table. insert_chart takes the same positioning options as an image, and the third one is the setting worth choosing deliberately:

Three anchoring behaviours when rows and columns are resized Object position one moves and sizes the chart with the cells beneath it, so widening a column stretches it. Position two moves it but keeps its size, which is usually what a reader expects. Position three pins it in place, so the chart stays put while the table grows underneath it. position 1 moves and sizes with the cells a widened column stretches the chart position 2 moves, keeps its size the usual choice stays beside its table without distorting position 3 pinned in place ignores the cells right for a dashboard laid out by hand
Python
ws.insert_chart("E2", chart, {"x_offset": 8, "y_offset": 4,
                              "object_position": 2})

The offsets nudge the chart a few pixels off the cell's corner so it does not sit flush against the gridline, which reads as deliberate placement rather than a dropped object. Anchor at a column beyond the last one the data uses — E2 for a three-column table — so no plausible column resize can put the chart over the numbers.

Step 4: Give a headline chart its own tab

For a chart that is the point of the report rather than an accompaniment, a chartsheet is a full-page tab with no cells:

Python
cs = wb.add_chartsheet("Trend")
cs.set_chart(column)          # one chart, full page
cs.activate()                 # the tab the workbook opens on
wb.close()

A chartsheet holds exactly one chart and cannot contain data, which is precisely why it works for a headline: nobody can accidentally type into it. activate() decides which tab is selected when the file opens — worth setting on any multi-sheet report, because the default is whichever sheet was added first.

Common pitfalls and gotchas

SymptomCauseFix
Chart has empty space on the rightRange extends past the written rowsBuild ranges from len(df)
Chart is emptySheet name in the range does not matchUse the exact add_worksheet name
Series name shows as "Series 1"No name givenPoint name at the header cell
Percentages plot as a flat lineSharing the currency axisPut them on y2_axis
Axis rescales every monthAuto min and maxFix min and max explicitly
Legend covers a fifth of the chartDefault legend on a single seriesset_legend({"none": True})
Chart sits on top of the dataAnchored at a cell inside the tableInsert well to the right of the last column
Data labels unreadableDefault size on a dense chartSet font size and colour in data_labels

Performance and scale notes

Charts are cheap to write — the definition is a few kilobytes regardless of how many points it references — but they are not cheap to render. A scatter or line chart over 50,000 points makes Excel slow to open and slower to scroll, and the picture is unreadable anyway. Aggregate before charting: plot the monthly total, not every transaction, and keep the detail on its own sheet for anyone who wants to filter it.

Where a chart genuinely needs tens of thousands of points, consider rendering it as an image with matplotlib and inserting that instead. The result is not interactive, but it opens instantly; Insert an Image into Excel covers the placement and anchoring.

Conclusion

A chart in xlsxwriter is three ranges and a handful of styling decisions. Compute the ranges from the row count so the chart follows the data, format the axes so the numbers read the way the table does, drop the legend when there is one series and keep it when there are two, and fix the secondary axis so consecutive reports are comparable. When the chart is the report, put it on a chartsheet and make that the tab the workbook opens on.

Frequently asked questions

Why is part of my chart blank? The series range extends past the rows that were actually written. Build the range from len(df) rather than typing an end row, and Excel will plot exactly what exists.

Can I combine a column chart and a line chart? Yes. Create both chart objects, then call combine() on the primary one. Put the line on a secondary axis with y2_axis when the two series use different units.

Is this a real Excel chart or an image? A real chart object. It recalculates when a reader edits the data, and it can be restyled in Excel like any hand-made chart — unlike a matplotlib image, which is a picture.

How do I put the chart on its own tab? Use add_chartsheet instead of insert_chart. A chartsheet holds one full-page chart and no cells, which suits a headline visual in a multi-sheet report.

Up to the parent guide:

Related guides: