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

Creating Charts in Excel with openpyxl

Build native, editable Excel charts from Python with openpyxl: bind data with Reference and Series, make bar, line, and pie charts, add titles and legends, and anchor them.

A report that ships a table of numbers makes the reader do the work of spotting the trend. A chart does that work up front. Once you have written your pandas output to a workbook, openpyxl lets you write charts directly into the same .xlsx file, and the result is a real Excel chart object — not a pasted image. Open the workbook in Excel or LibreOffice and you can click the chart, edit its data range, change its colors, and resize it like any chart you drew by hand. This set of guides is the charting half of Formatting and Charting Excel Reports with Python; the formatting guides handle how cells look, and these pages handle how the numbers are visualized.

Everything here builds on openpyxl's two core ideas: a chart object describes the chart, and Reference/Series objects bind that chart to cell ranges on a worksheet. Get those two right and every chart type follows the same shape.

Worksheet range bound by Reference into a native Excel chart A range of worksheet cells is bound by Reference and Series, then rendered as a native Excel chart that can be a bar, line, or pie. Worksheet range Cat Val North 26 South 19 East 31 Reference + Series Native Excel chart Bar Line Pie

How openpyxl charts actually work

An openpyxl chart is a small declarative object stored inside the .xlsx package. It does not contain the data — it contains pointers to worksheet cells. When Excel opens the file, it reads those pointers, reads the live cell values, and renders the chart. That has one important consequence: change a number in the bound range and the chart updates, exactly as a manually drawn chart would.

Python
# pip install openpyxl
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active
ws.append(["Quarter", "Revenue"])
for row in [("Q1", 120), ("Q2", 145), ("Q3", 138), ("Q4", 162)]:
    ws.append(row)

chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=5)   # includes header
cats = Reference(ws, min_col=1, min_row=2, max_row=5)    # excludes header
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.title = "Quarterly Revenue"

ws.add_chart(chart, "D2")
wb.save("chart_basics.xlsx")
print("Saved chart_basics.xlsx — open it in Excel and click the chart")

The chart is editable because it is described, not drawn. There is no pixel data to round-trip — Excel does the drawing.

Binding data with Reference and Series

Reference defines a rectangular block of cells. You pass it the worksheet plus the bounding rows and columns, and it becomes the data or the categories for a chart. The single most common mistake is mixing up which Reference is the values and which is the categories: values normally include the header row (so titles_from_data=True can read the series name), while categories never do.

The value Reference and the category Reference over one worksheet block The same three-column worksheet block carries two Reference objects: the value Reference spans the Signups and Churn columns including the header row so titles_from_data reads each series name, while the category Reference spans only the Month data rows and never the header. One worksheet block, two Reference objects Month Signups Churn Jan 90 12 Feb 110 15 Mar 130 11 Value Reference Value columns including the header row, so titles_from_data reads each series name. Category Reference Data rows only — never the header.
Python
# pip install openpyxl
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference, Series

wb = Workbook()
ws = wb.active
ws.append(["Month", "Signups", "Churn"])
for row in [("Jan", 90, 12), ("Feb", 110, 15), ("Mar", 130, 11)]:
    ws.append(row)

chart = LineChart()
# Two value columns -> two series. Header row supplies the series names.
data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=4)
chart.add_data(data, titles_from_data=True)
cats = Reference(ws, min_col=1, min_row=2, max_row=4)
chart.set_categories(cats)

print("series count:", len(chart.series))   # -> 2
wb.save("two_series.xlsx")

add_data builds one Series per column (or per row, with from_rows=True). When you need fine control — a custom series title, or pulling columns that aren't adjacent — build a Series object yourself and append it to chart.series.

A bar chart

Bar charts compare discrete categories. Build the chart, add a value Reference with titles_from_data=True, attach the category labels, then anchor it.

Python
# pip install openpyxl
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active
ws.append(["Product", "Units"])
for row in [("Widget", 340), ("Gadget", 280), ("Gizmo", 410)]:
    ws.append(row)

chart = BarChart()
chart.type = "col"          # vertical columns; "bar" gives horizontal
chart.title = "Units Sold by Product"
chart.x_axis.title = "Product"
chart.y_axis.title = "Units"

data = Reference(ws, min_col=2, min_row=1, max_row=4)
chart.add_data(data, titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=4))

ws.add_chart(chart, "D2")
wb.save("bar_chart.xlsx")
print("Saved bar_chart.xlsx")

The dedicated walkthrough — including the off-by-one row traps — is in Create a Bar Chart in Excel with openpyxl.

A line chart

Line charts show a metric changing across an ordered axis, usually time. The mechanics match the bar chart; the difference is intent and the option to add markers.

Python
# pip install openpyxl
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference

wb = Workbook()
ws = wb.active
ws.append(["Week", "Active Users"])
for row in [("W1", 1200), ("W2", 1340), ("W3", 1290), ("W4", 1510)]:
    ws.append(row)

chart = LineChart()
chart.title = "Weekly Active Users"
chart.y_axis.title = "Users"
chart.x_axis.title = "Week"

data = Reference(ws, min_col=2, 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.series[0].smooth = False   # straight segments between points

ws.add_chart(chart, "D2")
wb.save("line_chart.xlsx")
print("Saved line_chart.xlsx")

Time-series specifics — multiple series, markers, and date axes — are covered in Add a Line Chart to an Excel Report with Python.

A pie chart

Pie charts show parts of a single whole, so they take exactly one data series and one set of category labels.

Python
# pip install openpyxl
from openpyxl import Workbook
from openpyxl.chart import PieChart, Reference

wb = Workbook()
ws = wb.active
ws.append(["Channel", "Sessions"])
for row in [("Organic", 540), ("Paid", 230), ("Referral", 130), ("Direct", 100)]:
    ws.append(row)

chart = PieChart()
chart.title = "Traffic by Channel"

data = Reference(ws, min_col=2, 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))

ws.add_chart(chart, "D2")
wb.save("pie_chart.xlsx")
print("Saved pie_chart.xlsx")

Titles, axis labels, legend, and position

A few properties cover almost every labeling need, and they are the same across chart types:

  • chart.title — the chart heading.
  • chart.x_axis.title / chart.y_axis.title — axis captions (ignored by pie charts).
  • chart.legend — set chart.legend = None to drop the legend; it shows automatically when there is more than one series.
  • chart.style — an integer 1–48 selecting a built-in Excel color/style preset.
  • chart.height / chart.width — size in centimeters.

Position is set when you anchor the chart: ws.add_chart(chart, "E2") places the chart's top-left corner at cell E2. The cell is an anchor only — the chart floats above the grid and does not resize with the column.

Python
# pip install openpyxl
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active
ws.append(["Region", "Sales"])
for row in [("North", 220), ("South", 310), ("East", 190), ("West", 275)]:
    ws.append(row)

chart = BarChart()
chart.title = "Sales by Region"
chart.x_axis.title = "Region"
chart.y_axis.title = "Sales ($000s)"
chart.style = 10
chart.height = 7      # cm
chart.width = 14      # cm
chart.legend = None   # single series — legend adds nothing

chart.add_data(Reference(ws, min_col=2, min_row=1, max_row=5), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=5))

ws.add_chart(chart, "E2")
wb.save("labeled_chart.xlsx")
print("Saved labeled_chart.xlsx at anchor E2")

Plotting several series: grouping and stacking

Real reports rarely plot one number. Add more value columns to the Reference (widen max_col) and each column becomes its own series, named from its header. Two properties then decide the layout: grouping ("clustered" puts the series side by side, "stacked" piles them into one bar) and overlap (set it to 100 so a stack actually shares a single x-position instead of drawing offset).

Python
# pip install openpyxl
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active
ws.append(["Quarter", "Online", "Retail"])
for row in [("Q1", 120, 80), ("Q2", 145, 92), ("Q3", 138, 101), ("Q4", 162, 110)]:
    ws.append(row)

chart = BarChart()
chart.type = "col"
chart.grouping = "stacked"   # "clustered" draws the two series side by side
chart.overlap = 100          # required for a true stack — bars share one x slot
chart.title = "Revenue by Channel"

data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=5)  # two columns -> two series
chart.add_data(data, titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=5))

ws.add_chart(chart, "E2")
wb.save("stacked_bar.xlsx")
print("Saved stacked_bar.xlsx")

With more than one series the legend appears automatically, so each series name (read from the header) tells the reader which bar is which.

Styling series colors and data labels

The default palette is fine for a draft, but a branded report usually wants specific colors and the value printed on each bar. Reach into series.graphicalProperties.solidFill with a six-digit hex string to recolor a series, and attach a DataLabelList to show values. Matching these colors to the fills you set on the cells themselves keeps the whole report on one palette.

Python
# pip install openpyxl
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.chart.label import DataLabelList

wb = Workbook()
ws = wb.active
ws.append(["Region", "Sales"])
for row in [("North", 220), ("South", 310), ("East", 190)]:
    ws.append(row)

chart = BarChart()
chart.title = "Sales by Region"
chart.add_data(Reference(ws, min_col=2, min_row=1, max_row=4), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=4))

# Recolor the single series with a solid brand hex (no leading '#')
chart.series[0].graphicalProperties.solidFill = "5B5CF0"

# Print the value on top of every bar
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True

ws.add_chart(chart, "D2")
wb.save("styled_series.xlsx")
print("Saved styled_series.xlsx")

For multi-series charts, index chart.series[0], chart.series[1], and so on, and set a solidFill on each.

Rendering a chart to an image or PDF

openpyxl writes the chart definition but never draws it — there is no rendering engine in the library, which is why a chart looks empty until Excel or LibreOffice opens the file. To turn a chart into a picture for a slide deck or an emailed summary, hand the workbook to a spreadsheet application and let it render. Headless LibreOffice does this on a server with no display:

Python
# Requires libreoffice installed on the machine
import subprocess
from pathlib import Path

subprocess.run([
    "libreoffice", "--headless", "--convert-to", "pdf",
    "--outdir", "out", "bar_chart.xlsx",
], check=True)
print("Rendered:", Path("out/bar_chart.pdf"))

The same headless conversion is the backbone of converting a finished Excel report to PDF, and the rendered file is what you attach when you email the report to recipients. Keep the .xlsx as the source of truth; treat the PDF or image as a disposable snapshot.

Choosing a chart type

Chart typeopenpyxl classUse whenSeries supported
Column / barBarChartComparing values across discrete categoriesOne or many
LineLineChartA metric trending over an ordered axis (usually time)One or many
PiePieChartParts of a single 100% wholeExactly one
ScatterScatterChartRelationship between two numeric variablesOne or many
AreaAreaChartCumulative magnitude over time, stacked totalsOne or many

When in doubt: compare → bar, trend → line, composition → pie (and only for a handful of slices).

References decide what the chart shows

Every openpyxl chart is built from Reference objects pointing at cells, and two arguments control almost everything about the result:

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

wb = load_workbook("report.xlsx")
ws = wb["Orders"]
last = ws.max_row

data = Reference(ws, min_col=4, min_row=1, max_row=last)     # include row 1
categories = Reference(ws, min_col=2, min_row=2, max_row=last)  # exclude row 1

chart = BarChart()
chart.add_data(data, titles_from_data=True)                  # row 1 becomes the series name
chart.set_categories(categories)
chart.title = "Revenue by region"
chart.y_axis.title = "Revenue"
chart.x_axis.title = "Region"
chart.height, chart.width = 8, 16                             # centimetres

ws.add_chart(chart, "H2")
wb.save("report_chart.xlsx")

The asymmetry is deliberate and is the single most common source of confusion: the data reference starts at the header row so titles_from_data=True has a name to use, while the category reference starts below it so the header does not become a category. Getting it backwards produces a chart with a series called "Series1" and a category labelled with the column name.

Why the data and category references start on different rows The data reference includes row one so the header text can become the series name. The category reference starts at row two so the header does not appear as a category label on the axis. data: rows 1 to n includes the header titles_from_data=True series named from it categories: rows 2 to n excludes the header one label per data row axis labels

Charts read values, not formulas

A chart series pointing at cells that hold uncalculated formulas plots nothing, because there is no cached number for openpyxl to reference and nothing for Excel to draw until it recalculates. On a workbook generated entirely in Python, the reliable approach is to write computed values into the cells the chart references and keep formulas for figures a reader may change.

The same applies to filtered data: a chart plots the range it points at, including rows a filter has hidden. Where a chart should follow a filter, plot a small summary block that a SUBTOTAL formula maintains rather than the detail rows themselves.

Keeping charts readable

Three settings do more for legibility than any styling: a title that states the measure and the period, axis titles with units, and a size proportionate to the data. A twelve-category bar chart in a chart eight centimetres wide is unreadable regardless of colours, and the fix is one line rather than a palette change.

Python
chart.title = "Revenue by region — March 2026"
chart.y_axis.title = "Revenue (£)"
chart.style = 10                       # a built-in colour set, consistent across a workbook
chart.legend = None                    # a single series needs no legend

Removing the legend on a single-series chart is the smallest of these and the most reliably appreciated: the legend restates the axis title, takes a fifth of the plot area, and tells the reader nothing they did not already know.

A chart is a claim

Every chart argues something — that one region leads, that a trend is rising, that a total is made up of these parts. Choosing the type from that claim rather than from habit is what makes a report readable: bars for comparison, lines for change over time, stacked bars only when the total genuinely matters more than its parts. A chart whose type does not match its claim is harder to read than the table it replaced.

Anchor charts where they will be read

A chart placed at H2 beside its data reads as commentary on that table; the same chart on its own sheet reads as a headline. Both are valid, and choosing deliberately — rather than dropping every chart at the first free cell — is what makes a multi-chart workbook feel composed.

Fail where the cause is

The most useful place for a check is as close as possible to the thing that can go wrong: the sheet name at the read, the column list before the transform, the row count before the write, the file size before delivery. Each of those turns a confusing downstream error into a message naming the actual problem. Checks placed late still catch the failure, but they describe a symptom — and a symptom three stages from its cause is what makes a simple mistake take an afternoon.

Frequently asked questions

Are openpyxl charts images or real charts? Real Excel charts. They are stored as chart definitions that point at worksheet cells, so they stay fully editable in Excel and update when the underlying cells change.

Can openpyxl render a chart to a PNG? No. openpyxl writes the chart definition but does not draw it — only a spreadsheet application (Excel or LibreOffice) renders it. To export an image you must open the file in one of those applications, for example by automating LibreOffice headless conversion.

Why is my chart empty or missing a series? Almost always a Reference bounds problem: the value range didn't include the rows you expected, or the header row was left out so titles_from_data had nothing to read. Double-check min_row/max_row against the data.

Do I include the header row in the Reference? Include it in the value Reference when you use titles_from_data=True, so the series name is read from the header. Never include it in the category Reference.

Can I put several charts on one sheet? Yes. Build each chart and anchor it at a different cell — ws.add_chart(c1, "E2"), ws.add_chart(c2, "E20"). Multiple charts coexist on a sheet without conflict.

Conclusion

openpyxl charts are native Excel objects bound to cell ranges through Reference and Series. Once you understand that the value range usually carries the header (for the series name) and the category range never does, every chart type — bar, line, pie, scatter, area — is the same three steps: build the chart, add data, set categories, then anchor it with ws.add_chart. The one limitation to remember is rendering: openpyxl defines the chart but relies on Excel or LibreOffice to draw it.

Where to go next