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

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 expands on the main guide, Creating Charts in Excel with openpyxl, which covers the Reference-and-categories chart model the steps below assume, and it shares that model with the sibling line chart walkthrough.

A product/value column bound by Reference into a bar chart anchored at D2 Columns A and B hold product categories and unit values; the value column (with its header) plus the category column bind through a Reference to a BarChart that is anchored at cell D2 on the worksheet. Data in A:B Product Units Sold Widget 340 Gadget 280 Gizmo 410 Doohickey 195 Reference D2 anchor Widget Gadget Gizmo Doohickey

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. To control how the axis numbers read — thousands separators, currency, or a fixed number of decimals — set chart.y_axis.numFmt with the same format codes used when applying number and date formats in Excel to worksheet cells.

Common pitfalls

SymptomCauseFix
Chart is missing the last barmax_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 omittedStart the value Reference at min_row=1 and pass titles_from_data=True.
An extra bar appears at the frontHeader row included in the category rangeCategories must start at min_row=2; the header is not a category.
X-axis shows 1, 2, 3, 4set_categories was never calledBind the label column with set_categories(Reference(...)).
Bars and labels are swapped/emptyValue and category ranges point at the wrong columnsValue range = numeric column; category range = label column. Don't cross them.
Bars run horizontally, not verticallychart.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.

Grouped, stacked or one bar per row

Three bar layouts and the question each answers A single series compares categories directly. A grouped chart compares several series side by side within each category. A stacked chart shows composition, where the total matters more than the parts. one series revenue by region the clearest comparison no legend needed grouped this month vs last side-by-side bars up to about four series stacked composition of a total parts add to the whole hard to compare parts
Python
from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.type = "col"                       # "col" for vertical, "bar" for horizontal
chart.grouping = "clustered"             # or "stacked" / "percentStacked"
chart.add_data(Reference(ws, min_col=3, max_col=4, min_row=1, max_row=ws.max_row),
               titles_from_data=True)
chart.set_categories(Reference(ws, min_col=2, min_row=2, max_row=ws.max_row))
chart.title = "Revenue by region — this month and last"
ws.add_chart(chart, "H2")

Switching between the three is one attribute, and the choice should follow the question rather than taste. Stacked bars make totals easy and individual segments hard to compare, which is the right trade only when the total is the point. Beyond about four series a grouped chart becomes a picket fence, and a small multiple — several charts of one series each — communicates far more.

Horizontal bars (type="bar") are worth remembering for long category labels: rotated text on a vertical chart is the usual reason a chart is hard to read.

Sort the bars before you plot them

Bar charts are read top to bottom or left to right, and an unsorted category axis wastes that ordering. Sorting the underlying rows by the measure — descending for a leaderboard, by a business sequence where one exists — costs one sort_values call before the write and does more for readability than any styling choice available afterwards.

Give the axis units

A bar chart's value axis should say what it measures — revenue in pounds, units, orders. Without it a reader has to infer the unit from the surrounding table, and a chart that has been copied into a slide has lost that context entirely.

Presentation comes after the data

Any write replaces what it covers, so formatting, filters, images and charts belong in a single finishing pass that runs after the last value has been written. Splitting the job that way — build the frame, write it, then decorate the finished sheet — is what stops a style disappearing the month someone adds a to_excel call in the middle. It also gives a report one obvious place to change when the house style moves, instead of a dozen scattered blocks that have to be found first.

Vertical or horizontal

Column charts compared with bar charts Column charts run vertically and suit short category labels. Bar charts run horizontally and give long labels room, which avoids rotated text. type="col" — vertical short labels time on the x axis the common default type="bar" — horizontal long category names no rotated text rankings read well

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