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

Add a Combo Chart with a Secondary Axis in openpyxl

Plot revenue as bars and margin as a line on their own axis — combining chart objects, the y_axis.axId and crosses settings, and when a second axis misleads.

Revenue in thousands and margin as a percentage cannot share a scale: plot them on one axis and the margin line lies flat along the bottom. A secondary axis solves it — bars on the left scale, a line on the right — and openpyxl builds one by combining two chart objects with a couple of axis settings that are easy to get wrong. This guide covers the construction, the two settings that decide whether the second series appears at all, and the cases where a secondary axis actively misleads. It extends Creating Charts in Excel with openpyxl.

Bars on the left scale, a line on the right A combo chart over six months. Revenue is drawn as indigo bars measured against a left axis running from zero to six thousand. Margin is drawn as a teal line measured against a right axis running from zero to thirty per cent. Because each series has its own scale, both are legible; plotted on a single axis the margin line would sit flat along the bottom of the chart. revenue and margin, each on its own scale 6,000 3,000 0 30% 15% 0% Mar Apr May Jun Jul Aug revenue (left) margin (right)

Prerequisites

Bash
pip install openpyxl pandas

Data with two very different scales:

Python
import pandas as pd

monthly = pd.DataFrame({
    "month": ["Mar", "Apr", "May", "Jun", "Jul", "Aug"],
    "revenue": [5150, 4820, 3980, 5402, 6100, 4560],
    "margin": [0.152, 0.181, 0.118, 0.205, 0.228, 0.163],
})
monthly.to_excel("monthly.xlsx", index=False)

Revenue runs in the thousands, margin between 0.11 and 0.23. On one axis the margin series is invisible.

Step 1 — Build the primary chart

The bar chart owns the axes, the title and the plot area:

Python
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference

wb = load_workbook("monthly.xlsx")
ws = wb.active

categories = Reference(ws, min_col=1, min_row=2, max_row=7)

bars = BarChart()
bars.type = "col"
bars.title = "Revenue and margin by month"
bars.y_axis.title = "Revenue"
bars.x_axis.title = "Month"
bars.height, bars.width = 9, 18

bars.add_data(Reference(ws, min_col=2, min_row=1, max_row=7), titles_from_data=True)
bars.set_categories(categories)

titles_from_data=True with min_row=1 takes the series name from the header cell, which is why the legend reads "revenue" rather than "Series 1".

Step 2 — Build the secondary chart and set its axis

The line chart contributes only its series and a second scale. Three settings make that work:

Python
from openpyxl.chart import LineChart

line = LineChart()
line.add_data(Reference(ws, min_col=3, min_row=1, max_row=7), titles_from_data=True)

# 1. A distinct axis id — this is what makes it a SECOND axis.
line.y_axis.axId = 200
line.y_axis.title = "Margin"
line.y_axis.numFmt = "0.0%"

# 2. Cross at the far end so it draws on the right.
line.y_axis.crosses = "max"

# 3. Suppress the duplicate category axis.
line.x_axis.delete = True

Each of the three is load-bearing:

SettingWithout it
y_axis.axId = 200Both charts share one axis; the line flattens
y_axis.crosses = "max"The second axis draws on the left, overlapping the first
x_axis.delete = TrueTwo category axes render on top of each other

The axId value is arbitrary — it just has to differ from the primary chart's, which defaults to 100. Using 200 is a convention, not a requirement.

Step 3 — Combine and anchor

Which chart survives the combine Two chart objects are merged. The bar chart on the left of the operator keeps its title, its axis titles, its plot area and its size — it owns the resulting chart. The line chart on the right contributes only its data series and its secondary axis; its own title and axis labels are discarded. Combining in the opposite order would silently lose the settings applied to the bar chart. BarChart title · axis titles size · plot area all of this survives += LineChart series + secondary axis its own title discarded contributes data only one combo chart bars, line, two axes reverse the operands and the title and axis labels you set are silently lost

The += operator merges the second chart's series into the first:

Python
bars += line
ws.add_chart(bars, "F2")
wb.save("monthly_combo.xlsx")

Order matters. The chart on the left of the += keeps its title, axes and plot area; the one on the right contributes series only. Combining the other way round would give you a line chart with bars added, and the title and axis labels you set on the bar chart would be lost.

The complete function:

Python
from openpyxl import load_workbook
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.chart.marker import Marker

def add_combo_chart(ws, anchor="F2", last_row=7,
                    bar_col=2, line_col=3, category_col=1,
                    title="Revenue and margin by month",
                    line_format="0.0%"):
    """Bars on the primary axis, a line on a secondary axis."""
    categories = Reference(ws, min_col=category_col, min_row=2, max_row=last_row)

    bars = BarChart()
    bars.type = "col"
    bars.title = title
    bars.y_axis.title = ws.cell(row=1, column=bar_col).value
    bars.x_axis.title = ws.cell(row=1, column=category_col).value
    bars.y_axis.numFmt = "#,##0"
    bars.height, bars.width = 9, 18
    bars.add_data(Reference(ws, min_col=bar_col, min_row=1, max_row=last_row),
                  titles_from_data=True)
    bars.set_categories(categories)

    line = LineChart()
    line.add_data(Reference(ws, min_col=line_col, min_row=1, max_row=last_row),
                  titles_from_data=True)
    line.y_axis.axId = 200
    line.y_axis.title = ws.cell(row=1, column=line_col).value
    line.y_axis.numFmt = line_format
    line.y_axis.crosses = "max"
    line.x_axis.delete = True

    series = line.series[0]
    series.smooth = False                       # straight segments read honestly
    series.marker = Marker(symbol="circle", size=7)
    series.graphicalProperties.line.width = 28_000   # EMU, roughly 2.2pt

    bars += line
    ws.add_chart(bars, anchor)
    return bars

Two details in the series styling. smooth = False matters more than it looks — a smoothed line curves between points and implies values that were never measured, which is misleading on monthly data. And line width is set in EMU, English Metric Units, where 12,700 EMU is one point; 28,000 is a little over two points.

Step 4 — Know when a second axis misleads

A secondary axis is a genuine solution to a scale problem and a genuine way to mislead, because the relationship it appears to show depends entirely on where you put the scales.

The same data, two secondary-axis ranges, two impressions The identical revenue bars and margin line drawn twice. On the left the secondary axis runs from zero to thirty per cent and the line sits above the bars, suggesting margin outpaces revenue. On the right the same data with the secondary axis running from zero to sixty per cent puts the line below the bar tops, suggesting the opposite. Nothing about the data changed; only the scale did. That is why a secondary axis should be used to make a series legible, never to imply a relationship. right axis 0–30% right axis 0–60% · same data the line rides above the bars "margin is outpacing revenue" the line sits inside the bars "margin is lagging revenue"

The defensible uses are narrow: the two series measure genuinely different quantities, and the reader needs to see them against the same time axis. The indefensible use is implying a correlation, because with two free scales you can make any two series appear to move together or apart.

Two alternatives are usually better when the goal is comparison. Stack two charts sharing a category axis, so each has an honest zero-based scale:

Python
from openpyxl.chart import BarChart, LineChart, Reference

revenue = BarChart()
revenue.type = "col"
revenue.title = "Revenue"
revenue.add_data(Reference(ws, min_col=2, min_row=1, max_row=7),
                 titles_from_data=True)
revenue.set_categories(categories)
revenue.height, revenue.width = 6, 18
ws.add_chart(revenue, "F2")

margin = LineChart()
margin.title = "Margin"
margin.y_axis.numFmt = "0.0%"
margin.add_data(Reference(ws, min_col=3, min_row=1, max_row=7),
                titles_from_data=True)
margin.set_categories(categories)
margin.height, margin.width = 6, 18
ws.add_chart(margin, "F16")

Or index both series to a common base, so a single axis shows relative movement:

Python
import pandas as pd

indexed = monthly.copy()
for name in ("revenue", "margin"):
    indexed[name] = indexed[name] / indexed[name].iloc[0] * 100

Then one axis reading "index, first month = 100" carries both series honestly. The line-chart mechanics are covered in adding a line chart to an Excel report.

Common pitfalls and fixes

SymptomCauseFix
Second series missingShared axIdSet line.y_axis.axId = 200.
Both axes on the leftcrosses not setline.y_axis.crosses = "max".
Doubled category labelsTwo x-axes drawnline.x_axis.delete = True.
Title and axis labels lostCombined in the wrong orderPut the chart owning the axes on the left of +=.
Legend reads "Series 1"Header not includedmin_row=1 with titles_from_data=True.
Margin axis shows 0.15No number formatline.y_axis.numFmt = "0.0%".
The line implies values between pointsSmoothing onseries.smooth = False.
Chart is tinyDefault sizeSet height and width in centimetres.

Performance and scale notes

A chart references cell ranges rather than copying values, so its cost is independent of the number of rows — the chart XML is the same size for a hundred points as for ten.

What does matter is how many points Excel has to draw. Beyond a few hundred, a line becomes an unreadable band and rendering slows noticeably; beyond a few thousand it is actively unpleasant to scroll past. Aggregate before charting rather than plotting raw transactions:

Python
import pandas as pd

# Chart the monthly summary, not the 400,000 underlying rows.
monthly = (
    raw.groupby(pd.Grouper(key="date", freq="MS"))
       .agg(revenue=("amount", "sum"), margin=("margin", "mean"))
       .reset_index()
)

That is the same aggregation described in grouping Excel rows by month and quarter, and it improves the chart as well as the file.

Three further notes. Put the chart's source data on its own sheet when it is a summary — a hidden _chartdata sheet keeps the visible report clean while the chart still has a real range to reference, using the hiding technique from hiding sheets, rows and columns.

openpyxl cannot preserve a chart it did not create. Loading a workbook that already contains charts and saving it drops them, so a script that adds a combo chart to an existing report must recreate every chart in that workbook, not just the new one.

Charts cannot be added in write_only mode. A large report needing both streaming and a chart has to be written in two passes: stream the data, then re-open normally and add the chart. Since the chart only references ranges, that second pass touches almost nothing and is cheap.

Conclusion

A combo chart is two openpyxl chart objects merged with +=, and the three settings on the secondary chart are what make it work: a distinct y_axis.axId, crosses = "max" so the scale draws on the right, and x_axis.delete = True so the category axis is not drawn twice. Put the chart that owns the title and axes on the left of the operator. Then be deliberate about whether a second axis is the right answer at all — it makes a small-scale series legible, but with two free scales it can imply any relationship you like, so when the point is comparison, stack two honest charts or index both series to a common base instead.

Frequently asked questions

How do I combine two chart types in openpyxl? Build each chart separately, then add the second to the first with the += operator. The first chart owns the axes and the title; the second contributes only its series and, if configured, a secondary axis.

Why does my secondary series not appear? Almost always the axis identifiers. The secondary chart's y_axis.axId must differ from the primary's, and its x_axis.delete must be True so the two charts do not both draw a category axis over each other.

How do I put the second axis on the right? Set the secondary chart's y_axis.crosses to "max" so it crosses the category axis at the far end. Without that it draws on the left, overlapping the primary scale.

When is a secondary axis a bad idea? Whenever the two scales have no relationship, because the reader can be led to any conclusion by the choice of scale. Prefer two stacked charts sharing a category axis, or index both series to a common base.

Can I do this with xlsxwriter instead? Yes, and its API is arguably clearer: build both charts, call combine on the primary, and set y2_axis on the secondary series. The concepts map directly.