Create an Area Chart in Excel with openpyxl

An area chart is a line chart with the space below each line filled in. The fill turns the chart from "how did each value move?" into "how big is it, and what is it made of?" — which is why area charts suit totals that build up from parts: revenue by region over a year, hours by project over a quarter, inventory by warehouse. Stacked, the layers show both the total and its composition; stacked to 100%, they show how the mix changes. This page, part of Creating Charts in Excel with openpyxl, builds all three with openpyxl, including the one setting without which Excel shows the chart with no axes.

Prerequisites

pip install openpyxl

The examples use twelve months of revenue for three regions, written to the sheet first so the chart can reference it.

Step 1 — Write the data and a standard area chart

Charts in openpyxl reference cells, so the data goes on the sheet first — one column per series, with the series names in the header row and the categories in the first column:

from openpyxl import Workbook
from openpyxl.chart import AreaChart, Reference

months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
north = [42, 45, 48, 51, 55, 58, 54, 52, 60, 63, 66, 72]
south = [30, 31, 35, 34, 38, 41, 44, 47, 45, 49, 52, 55]
east = [12, 14, 15, 18, 20, 22, 25, 27, 30, 33, 35, 40]

wb = Workbook(); ws = wb.active; ws.title = "Revenue"
ws.append(["Month", "North", "South", "East"])
for row in zip(months, north, south, east):
    ws.append(row)

def area_chart(title, grouping="standard"):
    chart = AreaChart()
    chart.title = title
    chart.grouping = grouping
    if grouping != "standard":
        chart.overlap = 100
    chart.add_data(Reference(ws, min_col=2, max_col=4, min_row=1, max_row=13), titles_from_data=True)
    chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=13))
    chart.y_axis.title = "Revenue (k)"
    chart.x_axis.delete = False
    chart.y_axis.delete = False
    chart.width, chart.height = 18, 9
    return chart

standard = area_chart("Revenue by region")
ws.add_chart(standard, "F2")
print(len(standard.series), "series,", standard.grouping)

titles_from_data=True takes each series name from the header row, which is why the data reference starts at row 1. The two delete = False lines matter more than they look: openpyxl 3.1 leaves out the flag that marks an axis as visible, and Excel treats a missing flag as "hidden", so without them the chart appears in Excel with no axes at all. LibreOffice shows axes either way, which makes the problem easy to miss.

Step 2 — Stack the areas

In a standard area chart every series starts from zero, so the areas overlap, and a larger area can cover a smaller one completely — in this data, North's area hides South and East altogether. Stacking puts each series on top of the previous one: the layers show each region, and the top edge is the total. overlap = 100 makes the layers sit exactly on each other:

stacked = area_chart("Revenue by region — stacked", grouping="stacked")
ws.add_chart(stacked, "F21")
totals = [n + s + e for n, s, e in zip(north, south, east)]
print("total from", totals[0], "in Jan to", totals[-1], "in Dec")

The stacked chart's top edge rises from 84 in January to 167 in December, and East's growth from 12 to 40 is visible as a thickening layer. Put the steadiest series at the bottom — the first column — so the layers above it are easier to read; a jagged bottom layer makes every layer above it jagged too.

Step 3 — Show the mix with 100% stacking

When the question is "what share does each region contribute?" rather than "how big is the total?", stack to 100%. Every month becomes a full-height column of shares, so changes in the mix are visible even while the total grows:

share = area_chart("Share of revenue by region", grouping="percentStacked")
share.y_axis.title = "Share of revenue"
share.y_axis.number_format = "0%"
ws.add_chart(share, "F40")
print(f"East share: {east[0] / totals[0]:.0%} in Jan, {east[-1] / totals[-1]:.0%} in Dec")

East's share grows from 14% to 24% of the total — a change that is easy to miss in the stacked chart, where every layer is growing. The axis format 0% labels the scale as percentages; openpyxl does the scaling itself, so no percentage columns are needed in the data.

Step 4 — Make overlapping areas see-through

A standard area chart can still work for two or three series if the fills are partly transparent, so a smaller series shows through a larger one. openpyxl accepts transparency on scheme colours — the workbook theme's accent colours — through an alpha value in thousandths of a percent:

from openpyxl.drawing.colors import ColorChoice, SchemeColor

see_through = area_chart("Revenue by region — overlapping")
for series, accent in zip(see_through.series, ["accent1", "accent2", "accent3"]):
    series.graphicalProperties.solidFill = ColorChoice(schemeClr=SchemeColor(val=accent, alpha=55000))
    series.graphicalProperties.line.noFill = True
ws.add_chart(see_through, "F59")
wb.save("area_charts.xlsx")
print("saved", len(ws._charts), "charts")

alpha=55000 means 55% opaque. Theme accents keep the chart consistent with the workbook's colours; a plain hex fill such as "4F81BD" works too but cannot carry transparency in openpyxl. Removing the outline with line.noFill avoids a hard edge around each translucent area. Style Chart Colors and Data Labels with openpyxl covers series styling in general.

Chart data from a pandas pivot

Transaction data is long — one row per sale — while a chart needs one column per series. Pivot it first, write the wide table, and point the chart at the columns the pivot created:

import pandas as pd

sales = pd.DataFrame({"Month": months * 3, "Region": ["North"] * 12 + ["South"] * 12 + ["East"] * 12,
                      "Revenue": north + south + east})
wide = sales.pivot_table(index="Month", columns="Region", values="Revenue", aggfunc="sum", sort=False)[["North", "South", "East"]]
print(wide.head(3))

sort=False keeps the months in calendar order rather than alphabetical, which would put April first. Selecting the columns in a fixed order decides the stacking order — the first column is the bottom layer — so the steadiest series can be placed there deliberately. Write wide with to_excel, then build the Reference from its position on the sheet as in Step 1.

Gaps in the data

A month with no value leaves an empty cell. By default Excel drops an area chart to zero there, which draws a misleading valley. chart.display_blanks = "span" joins the points either side instead, and "gap" leaves a break; choose span when the value is missing rather than genuinely zero. If the blanks come from the data pipeline, fill them deliberately first — Fill Missing Dates in a Time Series with pandas covers the choice between zero and interpolation.

Common pitfalls

• No axes in Excel. Set x_axis.delete = False and y_axis.delete = False.
• Stacked without overlap. Set overlap = 100 for stacked groupings.
• Too many layers. Beyond five or six series, a stacked area becomes unreadable; group the small ones as "Other".
• Volatile bottom layer. Put the steadiest series first so upper layers are not distorted.
• Stacking values that do not add up. Stacking averages or rates produces a meaningless total; stack only additive measures.
• Blank cells drawn as zero. Set display_blanks or fill the gaps deliberately.

Performance notes

A chart is a small XML part referencing cells, so it adds almost nothing to the file whatever the size of the data. Excel draws charts with thousands of points slowly; aggregate to the level the chart is read at — months rather than days — before charting.

Conclusion

Write one column per series with names in the header row, create an AreaChart, and add the data with titles_from_data=True and the categories with set_categories. Use grouping = "stacked" with overlap = 100 to show a total and its parts, "percentStacked" with a 0% axis to show the mix, and translucent scheme-colour fills for a standard chart with a few overlapping series. Always set both axes' delete to False so Excel shows them.

Frequently asked questions

How do I create an area chart with openpyxl? Create an AreaChart, add the data with add_data(Reference(...), titles_from_data=True), set the categories with set_categories, and place it with ws.add_chart(chart, "E2"). Set chart.x_axis.delete and chart.y_axis.delete to False so Excel shows the axes.

How do I make a stacked area chart in openpyxl? Set chart.grouping = "stacked" and chart.overlap = 100. For a chart where every period adds up to 100%, use grouping = "percentStacked" with the same overlap.

Why does my openpyxl chart have no axes in Excel? openpyxl 3.1 does not write the element that tells Excel an axis is visible, and Excel then hides it. Set chart.x_axis.delete = False and chart.y_axis.delete = False before saving. LibreOffice shows the axes either way, so check in Excel.

How do I make overlapping areas transparent? Give each series a solid fill made from a theme colour with an alpha value — a ColorChoice holding SchemeColor(val="accent1", alpha=55000) — where 55000 means 55% opaque. openpyxl cannot attach transparency to a plain hex colour.

Related

• Up one level: Creating Charts in Excel with openpyxl — every chart type.
• Create a Stacked Column Chart with openpyxl — composition by period with bars.
• Add a Line Chart to an Excel Report with Python — the unfilled version.
• Format Chart Axes with openpyxl — scales, labels and hidden axes.