Use Multi-Level Category Labels in an Excel Chart

A column chart of seven quarters labelled 2025 Q1, 2025 Q2, … 2026 Q3 crowds the axis with repeated years and makes the reader parse every label. Excel can do better: a multi-level category axis shows the quarters along the bottom and groups them under a second row of labels, one 2025 centred under the first four bars and one 2026 under the rest, with divider lines between the groups. The same layout works for months under quarters, products under regions or weeks under months. Excel builds this automatically when the chart's categories span two or more columns laid out in a particular way, and openpyxl can write such a chart — but the layout of the source cells matters, and one common shortcut silently breaks it. This page builds a two-level axis from a pandas table, shows what goes wrong with repeated labels, and gives the XlsxWriter version. It belongs to Creating Charts in Excel with openpyxl.

Prerequisites

pip install openpyxl pandas

LibreOffice is used to render the charts for checking; it draws multi-level axes the same way Excel does.

Step 1 — Lay out the label columns with blanks

import pandas as pd

df = pd.DataFrame({
    "Year": [2025] * 4 + [2026] * 3,
    "Quarter": ["Q1", "Q2", "Q3", "Q4", "Q1", "Q2", "Q3"],
    "Revenue": [120, 135, 128, 150, 141, 158, 149],
    "Costs": [96, 104, 101, 112, 108, 117, 113],
})
labels = df.assign(Year=df["Year"].astype("string").mask(df["Year"].duplicated()))
print(labels.to_string(index=False))

Excel reads a multi-level category range column by column, from the outermost (left) to the innermost (right). A label in the outer column starts a new group, and the group continues through the following rows while the outer cell is empty. So the year must appear only on the first row of its group and the rows beneath must be blank. pandas' duplicated() marks every repeat of a year after its first appearance, and mask blanks those cells — the year column becomes 2025, blank, blank, blank, 2026, blank, blank. Converting the year to a string first matters too: category labels are text, and a number in the outer column can be treated as a value rather than a label by some readers.

Step 2 — Point the categories at both columns

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.chart.data_source import AxDataSource, MultiLevelStrRef

wb = Workbook()
ws = wb.active
ws.title = "Quarterly"
ws.append(list(labels.columns))
for row in labels.itertuples(index=False):
    ws.append([None if pd.isna(v) else v for v in row])
last = len(labels) + 1

chart = BarChart()
chart.type = "col"
chart.add_data(Reference(ws, min_col=3, max_col=4, min_row=1, max_row=last), titles_from_data=True)
categories = AxDataSource(multiLvlStrRef=MultiLevelStrRef(f=f"'{ws.title}'!$A$2:$B${last}"))
for series in chart.series:
    series.cat = categories
chart.title = "Revenue and costs by quarter"
chart.y_axis.title = "£k"
chart.x_axis.delete = chart.y_axis.delete = False
chart.legend.position = "b"
chart.width, chart.height = 16, 8
ws.add_chart(chart, "A11")
wb.save("quarterly_multilevel.xlsx")
print("categories:", chart.series[0].cat.multiLvlStrRef.f)

The usual set_categories call works with one column. For two, each series gets its categories as a MultiLevelStrRef covering both label columns — A2:B8 here — which is the element Excel itself writes when you select a two-column category range in its chart dialog. Every series in the chart must use the same reference, so it is assigned in a loop. The rest of the chart is ordinary: two series, revenue and costs, clustered side by side within each quarter. A single Reference over two columns passed to set_categories happens to render in some applications too, but openpyxl then writes it as a numeric reference, which is not what Excel expects for text labels; the explicit multi-level reference avoids relying on that.

Step 3 — Render it, and see what repeated labels do

repeated = Workbook()
rs = repeated.active
rs.title = "Quarterly"
rs.append(list(df.columns))
for row in df.astype({"Year": "string"}).itertuples(index=False):
    rs.append(list(row))
bad = BarChart()
bad.type = "col"
bad.add_data(Reference(rs, min_col=3, max_col=4, min_row=1, max_row=last), titles_from_data=True)
for series in bad.series:
    series.cat = AxDataSource(multiLvlStrRef=MultiLevelStrRef(f=f"'Quarterly'!$A$2:$B${last}"))
bad.title = "Repeated years: no grouping"
bad.x_axis.delete = bad.y_axis.delete = False
bad.width, bad.height = 16, 8
rs.add_chart(bad, "A11")
repeated.save("quarterly_repeated.xlsx")

for f in quarterly_multilevel quarterly_repeated; do
  soffice --headless --convert-to pdf "$f.xlsx" > /dev/null 2>&1
  pdftoppm -r 70 -f 1 -l 1 -png "$f.pdf" "$f"
done
ls *.png

Rendering both workbooks shows the difference. With blank repeats, the axis has the quarters along the bottom and two group labels underneath — 2025 centred under four bars, 2026 under three — separated by a divider. With the year repeated on every row, each cell in the outer column starts a new group of one, so the axis shows seven tiny groups each labelled with its own year: technically two levels, but no grouping at all, and no less crowded than single labels. This is the mistake to look for when a multi-level axis "does nothing". It is also the layout pandas produces by default, since a DataFrame repeats the year on every row, so the blanking step is needed almost every time the source is a tidy table.

Sort first, then blank

The blanking rule has a consequence that is easy to miss: the rows must be sorted by the outer level before the repeats are removed. Excel does not merge groups that happen to share a label; it only looks at whether the outer cell is empty. If the data arrives sorted by quarter rather than by year — 2025 Q1, 2026 Q1, 2025 Q2, 2026 Q2 — then blanking duplicates leaves a group for 2025 containing one quarter, a group for 2026 containing one quarter, and then unlabelled bars that silently belong to whichever year came last. Sort by the outer column, then the inner one, then blank the repeats — in pandas, sort_values(["Year", "Quarter"]) before the mask step. The printed table in step 1 shows the result before it is written: pandas displays the blanked cells as <NA>, and the loop that writes the rows turns them into None, which openpyxl leaves as truly empty cells. Writing an empty string instead would also work in Excel, but a real empty cell is the unambiguous choice.

The XlsxWriter version

XlsxWriter supports multi-level categories directly: pass a two-column range as the categories, for example "categories": "=Quarterly!$A$2:$B$8", and it writes the multi-level reference itself. The layout rule is the same — outer labels once, blanks below — because it is a rule of how Excel reads the range, not of the library. When the data comes from a pandas DataFrame with a MultiIndex, writing it with to_excel(merge_cells=True) produces merged outer cells, which Excel reads as a label followed by blanks; that can save the masking step, though merged cells have other drawbacks in data sheets, so the explicit blank layout is usually the cleaner source for a chart.

When to use more levels

Two levels cover most reports. A third — products under regions under years, say — is possible by adding another column on the left of the label range, with the same rule applied at each level: each label once, blanks below until the group changes. Beyond that the axis takes more space than the bars, and separate charts per group, or a table, communicate better. Multi-level labels also suit line charts with many periods, such as months under years; the axis groups the months and the years become natural reading anchors.

Common pitfalls

• Repeated outer labels. Write each outer label once and leave the cells below blank.
• set_categories over two columns. Use a MultiLevelStrRef so Excel gets text labels.
• Numbers as outer labels. Convert years to strings in the label column.
• Different category references per series. Give every series the same multi-level reference.
• Too many levels. More than three levels crowd the axis; split the chart instead.

Conclusion

A multi-level category axis groups detailed labels under broader ones without repeating them under every bar. Lay out the label columns from outermost to innermost, write each outer label only on the first row of its group with blanks below, and give every series an AxDataSource(multiLvlStrRef=MultiLevelStrRef(f=...)) covering all the label columns. Render the workbook once to check the grouping, and remember that a repeated outer label turns every row into a group of its own.

Frequently asked questions

How do I make a two-level category axis in an Excel chart with openpyxl? Set each series' cat to AxDataSource(multiLvlStrRef=MultiLevelStrRef(f="'Sheet'!$A$2:$B$8")), where column A holds the outer labels and column B the inner ones.

Why does my multi-level axis not group the labels? The outer labels are probably repeated on every row; Excel starts a new group at every non-empty outer cell, so leave the cells below each outer label blank.

Does XlsxWriter support multi-level categories? Yes; pass a range that spans two or more columns as the series' categories.

Can I use multi-level labels with a line chart? Yes; any chart with a category axis, including line and area charts, can use multi-level labels.

Related

• Up one level: Creating Charts in Excel with openpyxl — native Excel charts from Python.
• Create a Bar Chart in Excel with openpyxl — bar and column basics.
• Format Chart Axes with openpyxl — axis titles, scales and labels.
• Use a Date Axis in an Excel Chart with openpyxl — when categories are dates.