Combine Chart Types and Chartsheets with XlsxWriter

Two numbers that belong together often have different scales: revenue in hundreds of thousands and margin as a percentage, or orders in units and average order value in currency. Plotting both on one axis flattens one of them into a line along the bottom. A combination chart — columns for one series and a line for the other, the line on its own axis on the right — shows both at once, and it is one of the most requested charts in management packs. Another frequent request is a chart that fills a whole tab, sized to the window and ready to print, rather than floating over cells. XlsxWriter supports both: chart.combine() merges two chart types, and add_chartsheet() creates a sheet that holds a single chart. This page builds a column-and-line chart with a secondary axis, a stacked column chart with a total line, places them on chartsheets, and checks the generated chart definitions. It belongs to Building Excel Reports with xlsxwriter.

Prerequisites

pip install xlsxwriter pandas

The data is twelve months of revenue and gross margin per product line, summarised with pandas. Charts in Excel always read their data from cells, so the summary is written to a data sheet first and every chart refers to it.

Step 1 — Summarise and write the chart data

import numpy as np
import pandas as pd
import xlsxwriter

rng = np.random.default_rng(8)
months = pd.period_range("2025-10", "2026-09", freq="M").strftime("%b %y")
lines = ["Hardware", "Services", "Software"]
revenue = pd.DataFrame({ln: (base * (1 + 0.02 * np.arange(12)) * rng.uniform(0.9, 1.1, 12)).round(-2)
                        for ln, base in zip(lines, [180_000, 95_000, 60_000])}, index=months)
margin = pd.Series((0.31 + 0.03 * np.sin(np.arange(12) / 2) + rng.normal(0, 0.008, 12)).round(3), index=months, name="Margin")
summary = revenue.assign(Total=revenue.sum(axis=1), Margin=margin)

wb = xlsxwriter.Workbook("charts.xlsx")
data = wb.add_worksheet("Data")
bold = wb.add_format({"bold": True})
data.write_row(0, 0, ["Month", *summary.columns], bold)
for r, (month, row) in enumerate(summary.iterrows(), start=1):
    data.write(r, 0, month)
    data.write_row(r, 1, row.tolist())
data.set_column(0, 0, 9)
data.set_column(1, 4, 11, wb.add_format({"num_format": "#,##0"}))
data.set_column(5, 5, 9, wb.add_format({"num_format": "0.0%"}))
n = len(summary)
print(summary.head(3).to_string())

The months are written as text labels (Oct 25) rather than dates, so the chart uses them as categories exactly as written. Columns B to D hold revenue by product line, E the total and F the margin percentage. Every chart below refers to these cells by sheet name, row and column, so if the data changes the charts follow.

Step 2 — Columns and a line on a secondary axis

Create a column chart and a line chart separately, put the line on the secondary axis with y2_axis, and merge them with combine():

col = wb.add_chart({"type": "column"})
col.add_series({"name": ["Data", 0, 4], "categories": ["Data", 1, 0, n, 0], "values": ["Data", 1, 4, n, 4],
                "fill": {"color": "#1F6FEB"}, "gap": 60})
line = wb.add_chart({"type": "line"})
line.add_series({"name": ["Data", 0, 5], "categories": ["Data", 1, 0, n, 0], "values": ["Data", 1, 5, n, 5],
                 "y2_axis": True, "line": {"color": "#D4A72C", "width": 2.25}, "marker": {"type": "circle", "size": 6}})
col.combine(line)
col.set_title({"name": "Revenue and gross margin"})
col.set_y_axis({"name": "Revenue", "num_format": "#,##0,\"k\"", "major_gridlines": {"visible": True, "line": {"color": "#E5E7EB"}}})
line.set_y2_axis({"name": "Margin", "num_format": "0%", "min": 0.2, "max": 0.4})
col.set_legend({"position": "bottom"})

The primary chart (the columns) owns the title, the category axis and the legend; the secondary chart's series join it. y2_axis: True on the line series is what moves it to its own scale on the right, and the secondary axis is configured on the line chart with set_y2_axis(). Fixing that axis to 20–40% stops Excel from starting it at zero, which would flatten a margin that moves between 28% and 35% into an almost straight line — and that movement is the story here: revenue grew steadily from 317k to 391k while margin peaked at 34.7% in February and slid to 28% by July. The #,##0,"k" format on the primary axis shows revenue in thousands — the trailing comma divides by 1,000. The openpyxl version of the same chart is in Add a Combo Chart with a Secondary Axis in openpyxl.

Step 3 — Stacked columns with a total line

A stacked column chart shows how each product line contributes; adding the total as a line on the same axis puts a marker on top of each stack:

stack = wb.add_chart({"type": "column", "subtype": "stacked"})
colours = ["#1F6FEB", "#3FB950", "#A371F7"]
for c, colour in zip(range(1, 4), colours):
    stack.add_series({"name": ["Data", 0, c], "categories": ["Data", 1, 0, n, 0], "values": ["Data", 1, c, n, c],
                      "fill": {"color": colour}, "gap": 50})
total = wb.add_chart({"type": "line"})
total.add_series({"name": ["Data", 0, 4], "categories": ["Data", 1, 0, n, 0], "values": ["Data", 1, 4, n, 4],
                  "line": {"none": True}, "marker": {"type": "diamond", "size": 7, "fill": {"color": "#24292F"}},
                  "data_labels": {"value": True, "position": "above", "num_format": "#,##0,\"k\""}})
stack.combine(total)
stack.set_title({"name": "Revenue by product line"})
stack.set_y_axis({"num_format": "#,##0,\"k\""})
stack.set_legend({"position": "bottom"})

Here the line series has no y2_axis, so it shares the columns' scale and each diamond sits exactly on top of its stack; hiding the line itself ("line": {"none": True}) leaves just the markers with their labels. This is the standard way to label stack totals, which Excel's stacked column chart does not do on its own.

Step 4 — Chartsheets, and checking the result

A chartsheet is a tab that contains only a chart, sized to fill the window and the printed page:

for name, chart in [("Revenue & margin", col), ("By product line", stack)]:
    cs = wb.add_chartsheet(name)
    cs.set_chart(chart)
    cs.set_landscape()
wb.close()

A chartsheet has no cells, so it cannot hold the chart's data; the chart keeps reading from the Data sheet, which is why that sheet must stay in the workbook even if it is hidden. Each chart can be placed only once — either inserted into a worksheet with insert_chart() or set on a chartsheet — so a chart object belongs to exactly one location. Chartsheets appear as tabs in the order they are added; adding the data sheet first, as here, keeps it as the first tab, and many reports then hide it with data.hide() so readers see only the charts. Charts are XML definitions that Excel renders when the file is opened; nothing in Python renders them, so check the definitions themselves:

import html, re, zipfile

with zipfile.ZipFile("charts.xlsx") as z:
    names = z.namelist()
    sheets = [html.unescape(t) for t in re.findall(r'<sheet name="([^"]+)"', z.read("xl/workbook.xml").decode())]
    charts = sorted(n for n in names if re.match(r"xl/charts/chart\d+\.xml", n))
    print("tabs:", sheets, "| chartsheets:", sum(n.startswith("xl/chartsheets/sheet") for n in names))
    for c in charts:
        xml = z.read(c).decode()
        kinds = re.findall(r"<c:(barChart|lineChart)>", xml)
        print(c, kinds, "| axes:", len(re.findall(r"<c:valAx>", xml)), "| refs:", sorted(set(re.findall(r"Data!\$[A-F]\$", xml))))

The workbook has three tabs — the data and two chartsheets. The first chart contains a bar (column) chart and a line chart with two value axes, the second a bar chart and a line chart sharing one; the references show which data columns each chart reads. A check like this in a test catches the mistakes that otherwise show up only when someone opens the file: a series pointing at the wrong column, a secondary axis that was never created, a chart placed nowhere. For opening the file on Linux to look at it, export the chartsheets to PDF as in Convert Excel to PDF on Linux with LibreOffice.

Common pitfalls

• Configuring the secondary axis on the primary chart. Call set_y2_axis() on the chart that was combined in.
• Percentages on the revenue axis. Put them on y2_axis with their own number format.
• A secondary axis starting at zero. Set min and max so the line's movement is visible.
• Using one chart twice. Each chart object can be inserted or set on a chartsheet once.
• Expecting Python to draw the chart. Validate the XML or render with an office application.

Conclusion

To combine chart types with XlsxWriter, build each chart separately, mark a series with y2_axis: True when it needs its own scale, configure that axis with set_y2_axis() on the secondary chart, and merge with primary.combine(secondary). Keep a total line on the shared axis to label stacked columns, place finished charts on their own tabs with add_chartsheet() and set_chart(), and check the chart XML in tests because nothing in Python renders the charts.

Frequently asked questions

How do I combine a column and a line chart in XlsxWriter? Create a column chart and a line chart, add their series, then call column_chart.combine(line_chart) and insert only the column chart.

How do I put a series on a secondary axis in XlsxWriter? Add y2_axis set to True in the series options and configure the axis with set_y2_axis on the chart that owns the series.

How do I create a chartsheet with XlsxWriter? Call workbook.add_chartsheet(), then chartsheet.set_chart(chart) with a chart created by workbook.add_chart().

How can I label the totals of a stacked column chart? Combine a line chart of the totals on the same axis, hide its line and show markers with value data labels above each stack.

Related

• Up one level: Building Excel Reports with xlsxwriter — reports with XlsxWriter.
• Add a Chart to an Excel File with xlsxwriter — chart basics.
• Put a Chart on Its Own Chartsheet with openpyxl — the openpyxl way.
• Label Chart Points from Cell Values with xlsxwriter — custom data labels.