Creating Charts in Excel with openpyxl

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. With openpyxl you can write charts directly into the .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 cluster is the charting half of Formatting and Charting Excel Reports with Python; the formatting clusters 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.

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.

# 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.

# 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.

# 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.

# 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.

# 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.

# 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")

Choosing a chart type

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

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

• Up to the pillar: Formatting and Charting Excel Reports with Python
• This cluster's guides: Create a Bar Chart in Excel with openpyxl and Add a Line Chart to an Excel Report with Python
• Sibling clusters: Styling Excel Cells with openpyxl, Applying Number and Date Formats in Excel, and Inserting Images and Logos into Excel
• Related: Using openpyxl for Excel File Manipulation and Building Multi-Sheet Excel Dashboards