Create a Bar Chart in Excel with openpyxl

Build a native, editable Excel bar chart from Python with openpyxl: write sample data, bind it with Reference, set categories and titles, pick a style, and anchor it.

This page builds one thing end to end: a vertical bar chart written into an .xlsx file with openpyxl, fully editable when you open it in Excel. You will write a small category/value table, bind it to a BarChart with Reference, attach category labels, label the axes, apply a style, and anchor the chart to a cell. It is the focused walkthrough for the parent cluster, Creating Charts in Excel with openpyxl, which covers the chart model the steps below assume.

Prerequisites

Bash

pip install openpyxl

openpyxl 2.5+ has the modern openpyxl.chart API used here; any current release works. No other dependencies — the data is generated inline.

Step 1 — Write sample category/value data

A bar chart needs two columns: a category label and a numeric value. Put a header in row 1 so the series can take its name from it.

Python

# pip install openpyxl
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Sales"

ws.append(["Product", "Units Sold"])          # row 1: header
for row in [("Widget", 340), ("Gadget", 280), ("Gizmo", 410), ("Doohickey", 195)]:
    ws.append(row)                              # rows 2-5: data

wb.save("bar_step1.xlsx")
print("Data written: 4 products in rows 2-5, header in row 1")

The data now occupies columns A and B, rows 1 through 5. Hold that layout in mind — every Reference below refers to it.

Step 2 — Build the BarChart and bind the values

Create the chart, then build a Reference for the value column. Include the header row (min_row=1) and pass titles_from_data=True so the series is named "Units Sold" from cell B1.

Python

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

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

chart = BarChart()
chart.type = "col"   # "col" = vertical bars; "bar" = horizontal

# Value range: column B, header (row 1) through last data row (row 5).
data = Reference(ws, min_col=2, min_row=1, max_row=5)
chart.add_data(data, titles_from_data=True)

print("series:", len(chart.series), "| name:", chart.series[0].tx.strRef.f)
wb.save("bar_step2.xlsx")

titles_from_data=True is what reads B1; drop it and the series is named "Series 1" instead. Pass from_rows=True to add_data only if your series run across rows rather than down columns — for this column layout, leave it off.

Step 3 — Set the category labels

Without categories, the x-axis is labeled 1, 2, 3, 4. Bind the Product column — and exclude the header, because the header is a column title, not a category.

Python

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

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

chart = BarChart()
chart.type = "col"
chart.add_data(Reference(ws, min_col=2, min_row=1, max_row=5), titles_from_data=True)

# Category range: column A, data rows only (2-5) — NOT the header.
cats = Reference(ws, min_col=1, min_row=2, max_row=5)
chart.set_categories(cats)

wb.save("bar_step3.xlsx")
print("Categories bound to A2:A5 (Widget, Gadget, Gizmo, Doohickey)")

The asymmetry is the part people trip on: the value Reference starts at row 1 (header included), the category Reference starts at row 2 (header excluded).

Step 4 — Title, axes, style, and anchor

Finish with labels and a style preset, then drop the chart onto the sheet. ws.add_chart(chart, "D2") puts the chart's top-left corner at D2.

Python

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

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

chart = BarChart()
chart.type = "col"
chart.title = "Units Sold by Product"
chart.x_axis.title = "Product"
chart.y_axis.title = "Units"
chart.style = 12        # built-in color preset, 1-48
chart.legend = None     # single series, so no legend needed

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, "D2")
wb.save("bar_chart_final.xlsx")
print("Saved bar_chart_final.xlsx — open and click the chart to edit it")

Open bar_chart_final.xlsx in Excel or LibreOffice: it is a real chart, so you can drag it, change its colors, or extend its data range from the UI.

Common pitfalls

Symptom	Cause	Fix
Chart is missing the last bar	`max_row` is one too low (off by one)	With a header in row 1 and N data rows, `max_row` is `N + 1`. Four products → `max_row=5`.
Series is named "Series 1"	Header not included or `titles_from_data` omitted	Start the value `Reference` at `min_row=1` and pass `titles_from_data=True`.
An extra bar appears at the front	Header row included in the category range	Categories must start at `min_row=2`; the header is not a category.
X-axis shows 1, 2, 3, 4	`set_categories` was never called	Bind the label column with `set_categories(Reference(...))`.
Bars and labels are swapped/empty	Value and category ranges point at the wrong columns	Value range = numeric column; category range = label column. Don't cross them.
Bars run horizontally, not vertically	`chart.type` left at default `"bar"`	Set `chart.type = "col"` for vertical columns.

Performance and scale

A bar chart is bound to a cell range, so its cost is independent of how many points it shows — Excel renders it, openpyxl only writes the definition. Writing the underlying data is the only thing that scales with size, and even tens of thousands of rows write in well under a second. Bar charts become unreadable long before they become slow: past roughly 20–30 categories, switch to a different visualization rather than a wall of bars. If you are writing many large sheets, open the workbook with write_only=True for the data and add the chart on a normal worksheet.

Frequently asked questions

How do I make horizontal bars instead of columns? Set chart.type = "bar". The default in this guide is "col" (vertical); "bar" rotates them to horizontal.

Can I stack or group multiple series? Yes. Add more value columns to the Reference (widen max_col), and set chart.grouping = "stacked" or "clustered". Add chart.overlap = 100 for true stacking.

Why does my chart look fine but show no data when opened? The Reference rows don't match where the data actually sits. Re-check that min_row/max_row cover exactly your header-plus-data block.

Can I show the value on each bar? Yes — from openpyxl.chart.label import DataLabelList, then chart.dataLabels = DataLabelList() and chart.dataLabels.showVal = True.

Conclusion

A bar chart in openpyxl is four steps: write a header-plus-values table, bind the value column with Reference and titles_from_data=True, bind the label column with set_categories (header excluded), then label and anchor it. The one rule that prevents most bugs is the row asymmetry — values include the header row, categories don't — and getting max_row right (data rows plus one for the header).

Where to go next

Up to the cluster: Creating Charts in Excel with openpyxl
The sibling guide: Add a Line Chart to an Excel Report with Python
Related: Building Multi-Sheet Excel Dashboards