Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Create a Stacked Column Chart with openpyxl

Build stacked and 100% stacked column charts in Excel with openpyxl — grouping and overlap settings, series colours, value labels inside each segment, a total label above every stack using an invisible line series, hiding its legend entry, and horizontal stacked bars.
Charts & imagesopenpyxl

A stacked column chart answers two questions in one picture: how big is each total, and what is it made of. Quarterly sales by region, headcount by department, costs by category — each column is a period, each coloured layer a component. openpyxl builds it from the ordinary BarChart with a few settings that are easy to miss, and a small trick adds the one thing readers always ask for: the total on top of every stack. This guide, part of Creating Charts in Excel with openpyxl, builds the chart step by step and checks the result by rendering it.

Quarterly sales stacked by region Four stacked columns for Q1 to Q4. North contributes 120, 140, 110 and 160, South 80, 90, 100 and 95, and East 40, 55, 70 and 80, giving totals of 240, 285, 280 and 335 shown above each stack. North South East 0 100 200 300 400 Q1 Q2 Q3 Q4 240 285 280 335

Prerequisites

Bash
pip install openpyxl

The data, with a total column that the chart will use for its labels:

Python
from openpyxl import Workbook
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.chart.label import DataLabelList

wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(["Quarter", "North", "South", "East", "Total"])
for row, (q, north, south, east) in enumerate(
        [("Q1", 120, 80, 40), ("Q2", 140, 90, 55), ("Q3", 110, 100, 70), ("Q4", 160, 95, 80)], start=2):
    ws.append([q, north, south, east, f"=SUM(B{row}:D{row})"])

Step 1 — The stacked chart

A stacked column chart is a BarChart with three settings: type="col" for vertical columns, grouping="stacked" to stack the series, and overlap=100 so the series share one position instead of sitting side by side:

Python
chart = BarChart()
chart.x_axis.delete = False  # Excel hides axes unless this is set
chart.y_axis.delete = False
chart.type = "col"
chart.grouping = "stacked"
chart.overlap = 100
chart.gapWidth = 60
data = Reference(ws, min_col=2, max_col=4, min_row=1, max_row=5)
chart.add_data(data, titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=5))
chart.title = "Sales by region"
chart.height, chart.width = 8, 14
print(len(chart.series))              # 3 — one layer per region

titles_from_data=True takes each series name from the header row, which is why the data reference starts at row 1. Series are stacked in the order they are added, first at the bottom, so put the most important or most stable component first — it then sits on the common baseline, where lengths are easiest to compare. gapWidth is the space between stacks as a percentage of a column's width; 60 gives chunkier columns than the default 150.

Step 2 — Colours and labels inside each segment

Give each series a fill and a value label centred in its segment. The labels need the other label parts switched off, or Excel may show series and category names as well:

Python
COLOURS = ["A5B4FC", "5EEAD4", "FCD34D"]

def value_labels(position):
    labels = DataLabelList()
    labels.showVal = True
    labels.position = position
    for part in ("showSerName", "showCatName", "showLegendKey", "showPercent"):
        setattr(labels, part, False)
    return labels

for series, colour in zip(chart.series, COLOURS):
    series.graphicalProperties.solidFill = colour
    series.graphicalProperties.line.solidFill = colour
    series.dLbls = value_labels("ctr")

Light fills keep the default black label text readable; with dark fills, the labels need a white font, which means setting the label text properties as well. Choosing and styling chart colours is covered further in Style Chart Colours and Data Labels with openpyxl.

Step 3 — A total above every stack

Stacked charts have no built-in total label. The standard trick is to plot the total column as a line series on the same chart, hide the line, and label its points from above. Because the total equals the height of the stack, each label lands just on top of its column:

Python
totals = LineChart()
totals.x_axis.delete = False
totals.y_axis.delete = False
totals.add_data(Reference(ws, min_col=5, min_row=1, max_row=5), titles_from_data=True)
line = totals.series[0]
line.graphicalProperties.line.noFill = True
line.marker.symbol = "none"
line.dLbls = value_labels("t")
chart += totals

chart += totals combines the two charts on the same axes. The line series draws nothing — no line, no markers — and only its labels remain. The total column in the sheet is a formula, so the labels stay correct if a regional figure is edited.

The layers that make one labelled stack The stack for Q4 is built from a North segment of 160, a South segment of 95 and an East segment of 80, each labelled in the centre, with an invisible line series whose point sits at the stack's total of 335 and carries the label above it. Total line series (invisible) label 335 above the point East: 80 label centred South: 95 label centred North: 160 on the baseline

Step 4 — Hide the total from the legend

The invisible series still appears in the legend as "Total" with no symbol. Delete its legend entry by index — the fourth series, index 3:

Python
from openpyxl.chart.legend import LegendEntry

chart.legend.position = "b"
chart.legend.legendEntry = [LegendEntry(idx=3, delete=True)]
ws.add_chart(chart, "G2")
wb.save("stacked.xlsx")
print([e.idx for e in chart.legend.legendEntry])      # [3]

Rendering the saved workbook with LibreOffice shows four stacks with centred values — 120, 80 and 40 in Q1 — and totals of 240, 285, 280 and 335 above them. Put the legend at the bottom ("b") so the plot area keeps its full width.

Step 5 — Percent stacks and horizontal bars

Two variations use the same code. grouping="percentStacked" scales every stack to 100% and shows each region's share, at the cost of hiding the totals — drop the total series for this version. type="bar" turns the columns into horizontal bars, which read better when category labels are long:

Python
share = BarChart()
share.x_axis.delete = False
share.y_axis.delete = False
share.type = "col"
share.grouping = "percentStacked"
share.overlap = 100
share.add_data(data, titles_from_data=True)
share.set_categories(Reference(ws, min_col=1, min_row=2, max_row=5))
share.title = "Share of sales by region"
share.y_axis.number_format = "0%"
ws.add_chart(share, "G20")

bars = BarChart()
bars.x_axis.delete = False
bars.y_axis.delete = False
bars.type = "bar"
bars.grouping = "stacked"
bars.overlap = 100
bars.add_data(data, titles_from_data=True)
bars.set_categories(Reference(ws, min_col=1, min_row=2, max_row=5))
ws.add_chart(bars, "G38")
wb.save("stacked.xlsx")
print(share.grouping, bars.type)     # percentStacked bar

With horizontal bars the first category is drawn at the bottom; set bars.y_axis.scaling.orientation = "maxMin" on the category axis if Q1 should appear at the top, as it does in the table.

Stacked or 100% stacked A stacked chart shows absolute values, so both the totals and the components can be compared, but only the bottom segment sits on a common baseline. A 100 percent stacked chart shows each component's share of every total, making mix changes visible, but hides whether the totals grew or shrank. grouping = stacked totals are visible components in real units upper segments hard to compare total labels make sense grouping = percentStacked shares easy to compare mix changes stand out totals are hidden format the axis as 0%

Step 6 — From long data in pandas to a stacked chart

Sales data usually arrives long — one row per quarter and region — while a stacked chart needs one column per component. Pivot first, add the total, write the table, then point the chart at it:

Python
import pandas as pd

long = pd.DataFrame({
    "quarter": ["Q1", "Q1", "Q1", "Q2", "Q2", "Q2"],
    "region": ["North", "South", "East"] * 2,
    "sales": [120, 80, 40, 140, 90, 55],
})
wide = long.pivot_table(index="quarter", columns="region", values="sales", aggfunc="sum")
wide = wide[["North", "South", "East"]]                  # stacking order, bottom first
wide["Total"] = wide.sum(axis=1)

with pd.ExcelWriter("stacked_pd.xlsx", engine="openpyxl") as writer:
    wide.to_excel(writer, sheet_name="Sales")
    sheet = writer.sheets["Sales"]
    rows = len(wide) + 1
    pd_chart = BarChart()
    pd_chart.x_axis.delete = False
    pd_chart.y_axis.delete = False
    pd_chart.type, pd_chart.grouping, pd_chart.overlap = "col", "stacked", 100
    pd_chart.add_data(Reference(sheet, min_col=2, max_col=4, min_row=1, max_row=rows), titles_from_data=True)
    pd_chart.set_categories(Reference(sheet, min_col=1, min_row=2, max_row=rows))
    sheet.add_chart(pd_chart, "G2")
print(wide.to_dict("index")["Q2"])      # {'North': 140, 'South': 90, 'East': 55, 'Total': 285}

Selecting the columns explicitly after the pivot fixes the stacking order; a pivot would otherwise sort regions alphabetically and put East at the bottom. Writing the total as a value here is fine because the table is regenerated each run; in a workbook people edit, write it as a SUM formula as in the first example. Pivoting is covered in more depth in Create a Pivot Table from Excel with pandas.

Common pitfalls

  • Colours that change between charts. If North is blue in one chart and green in the next, readers compare the wrong layers. Fix one colour per component across the whole report, as the COLOURS list does.
  • Too many layers. Beyond four or five series a stack becomes unreadable. Group small components into "Other".
  • Negative values. Stacked columns with negative components stack below the axis separately from the positive ones, which is rarely what readers expect. Use a clustered chart or a waterfall for movements; see Build a Waterfall Chart in Excel with Python.
  • Totals from a different range. The total series must cover the same rows as the stacked data, or the labels float at the wrong heights.

Conclusion

A stacked column chart in openpyxl is a BarChart with type="col", grouping="stacked" and overlap=100, one series per component in the order they should stack. Colour each series, label segments with centred values, add totals above each stack with an invisible line series that has top-positioned labels, and delete that series' legend entry. Switch to percentStacked for shares or type="bar" for long category names.

Frequently asked questions

How do I make a stacked column chart with openpyxl? Create a BarChart, set type to "col", grouping to "stacked" and overlap to 100, then add the data columns as series. Each series becomes one layer of every stack.

Why set overlap to 100 for a stacked chart? Excel itself writes an overlap of 100 for stacked charts, meaning the series share one column position. Setting it explicitly matches what Excel expects; LibreOffice ignores the setting, so a chart can look right there and still be drawn differently in Excel without it.

How do I show the total on top of each stack? Add a total column to the sheet, plot it as a line series on the same chart, hide the line and turn on its data labels positioned above the points. The labels then sit on top of each stack.

What is the difference between stacked and percentStacked? stacked shows absolute values, so taller stacks mean bigger totals. percentStacked scales every stack to 100 percent, showing each series' share while hiding the totals.

More on charts & images

More on charts & images →