Quick Reference · read & write Excel .xlsx / .xlsm from Python

openpyxl 3.1.5

openpyxl is a file translator, not Excel. An .xlsx is a zip of XML; openpyxl unpacks it into three nested objects — Workbook → Worksheet → Cell — and writes them back. Everything else (styles, charts, validation) hangs off one of those three. It never calculates anything, and that one fact explains most of the surprises below.

setup & file I/O workbook & sheets cells & values ranges & iteration styling & formats charts, tables & extras gotcha / unsupported ★ most common

Distilled & cross-checked against: openpyxl.readthedocs.io (Tutorial · Simple usage · Optimised Modes · Performance · Rich Text) · openpyxl.pages.heptapod.net (3.1.5 build) · foss.heptapod.net/openpyxl · PyPI · pandas.pydata.org IO docs · ECMA-376 (OOXML)

The object tree & the three ways to open a file
A · ON DISK IN MEMORY — THREE NESTED OBJECTS report.xlsx a ZIP of XML parts sheet1.xml styles.xml sharedStrings.xml Workbook wb.sheetnames · wb.save() Worksheet wb.active · wb["Data"] Cell ws["A1"] · ws.cell(1, 1) holds holds everything else attaches to one of those three Font · PatternFill · Border · Alignment · number_format · Comment → Cell  ·  charts · images · tables · validation · merges → Worksheet load_workbook() wb.save() no Excel, no calculation engine anywhere B · ONE CALL, THREE VERY DIFFERENT RESULTS load_workbook(path, …) default everything loaded, editable cell.value → "=SUM(B2:B9)" the formula, as a plain string data_only=True read Excel's cached result cell.value → 1250 … or None None if Excel never opened + saved it read_only=True lazy, near-constant memory ReadOnlyCell — no styling, no edits and you must call wb.close() the two flags are independent and can be combined
quickstart — write a formatted, charted sheet in 20 lines
# pip install openpyxl          # + pillow for images, lxml for speed
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.chart import BarChart, Reference
from openpyxl.utils import get_column_letter

wb = Workbook()                              # always has one sheet
ws = wb.active
ws.title = "Sales"

ws.append(["Region", "Q1", "Q2"])            # append = next empty row
for row in [("North", 120, 140), ("South", 95, 130)]:
    ws.append(row)

for c in ws[1]:                             # style the header row
    c.font      = Font(bold=True, color="FFFFFF")
    c.fill      = PatternFill("solid", fgColor="217346")
    c.alignment = Alignment(horizontal="center")

ws["D1"] = "Total"
ws["D2"] = "=SUM(B2:C2)"                     # stored as TEXT, not computed
ws["D2"].number_format = "#,##0"
ws.column_dimensions[get_column_letter(1)].width = 14
ws.freeze_panes = "A2"

chart = BarChart()
chart.add_data(Reference(ws, min_col=2, max_col=3, min_row=1, max_row=3), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=3))
ws.add_chart(chart, "F2")

wb.save("sales.xlsx")                          # overwrites without warning
01Install & Importsoptional extras matter
02Create, Load & Savethe file round trip
03load_workbook() flagsread them once, save hours
04Worksheetswb is dict-like
05Sheet Appearancetabs, view, state
06Cells: Read & Writetwo addressing styles
07Cell Attributes & Typeswhat a Cell knows
08Coordinates & Utilsopenpyxl.utils
09Ranges & Slicingtuples of tuples
10Iterationthe reading workhorse
11Bulk Writingappend is king
12Insert, Delete & Movestructural edits
13Fonts & Fillsopenpyxl.styles
14Borders & AlignmentSide is per-edge
15Number Formatsdisplay, not value
16Named & Built-in Stylesreuse, don't repeat
17Conditional Formattingrules Excel evaluates
18Widths, Merge & Freezelayout
19Formulasstored, never solved
20ChartsReference + add_data
21Images & Rich Textneeds Pillow
22Tables & Auto-filterstructured ranges
23Validation & Protectionguard the sheet
24Print & Page Setupwhat comes out of the printer
25Optimised Modesfor files that don't fit
26pandas & NumPythe usual pairing
27Limits & Not Supportedknow before you commit

Four pictures that prevent most openpyxl bugs

The coordinate system, the formula lifecycle, what a style actually is, and which mode to open in.

1 · the grid starts at 1

Two ways to name the same cell. The off-by-one that bites everyone: ws.cell() counts rows and columns from 1, while the Python lists you feed it start at 0.

THE SAME CELL, TWO NAMES A B C D 1 2 3 B3 ws["B3"] ws.cell(3, 2) (row, column) column 2 = "B" — get_column_letter(2) and column_index_from_string("B") .coordinate "B3" · .row 3 · .column 2 · .column_letter "B" enumerate(data, start=2) when writing rows under a header.

2 · the formula lifecycle

The single biggest source of "openpyxl returned None". A formula is text until a real spreadsheet application computes it and caches the answer in the file.

PATH A — STRAIGHT OUT OF OPENPYXL ws["D2"] = "=SUM(B2:C2)" saved .xlsx formula, NO cached value data_only=True None PATH B — AFTER A REAL SPREADSHEET APP TOUCHES IT saved .xlsx formula only Excel opens + saves cache now written data_only=True 1250 Default load → the formula string. data_only=True → the cache. Never both at once. Saving a data_only workbook writes the numbers back OVER your formulas. Need real answers headlessly? LibreOffice --convert-to, or the formulas / pycel packages.

3 · what a "style" actually is

Five independent objects hanging off one Cell. They are immutable — you replace them wholesale, you never edit them in place.

ONE CELL · FIVE SEPARATE OBJECTS 1,250.00 .font bold, size, color .fill .border Border(top=Side()) .alignment horizontal="right" .number_format "#,##0.00" the stored value is still the float 1250.0 ✗ silently does nothing c.font.bold = True styles are immutable + shared ✓ correct f = copy(c.font); f.bold = True c.font = f

4 · which mode do you open in?

Normal mode holds the whole workbook in memory at roughly 50× the file size. The two optimised modes trade features for near-constant memory.

CAPABILITY MATRIX normal read_only write_only read cells ✓ ✓ ✗ write cells ✓ ✗ append() only random access ✓ forward only ✗ styles, charts, images ✓ ✗ limited memory ~50× file size near constant near constant wb.close() required no YES YES write_only saves exactly once — set freeze_panes and dimensions before any cells.

Appendix — picking the right Excel tool

Most "openpyxl is slow / can't do that" problems are really tool-choice problems. These three cover almost everything, and they compose.

pandasdata in, data out

✚ reach for it when

  • You want a table in or out and don't care how it looks
  • pd.read_excel() / df.to_excel() is one line vs. twenty
  • You need to reshape, join or aggregate before writing
  • Multiple sheets via pd.read_excel(f, sheet_name=None)

⚠ it can't

  • Style individual cells, add charts, set validation
  • Preserve anything in an existing file it didn't write
  • Escape openpyxl — it is the engine underneath for .xlsx
openpyxlread AND write

✚ reach for it when

  • You must read an existing .xlsx — this is the only one that can
  • You need to edit a file in place: fill a template, patch a column
  • You want cell-level control: fonts, fills, number formats, merges
  • Charts, images, tables, data validation, conditional formatting
  • Huge files, via read_only=True / write_only=True

⚠ it can't

  • Evaluate a single formula
  • Keep charts, images or shapes through a load → save round trip
  • Open .xls, or anything password-encrypted
  • Match XlsxWriter on raw write throughput
XlsxWriterwrite only, but fast

✚ reach for it when

  • You are generating a brand-new file from scratch, every time
  • You want sparklines, rich charts or constant_memory streaming
  • Formatting throughput matters more than the ability to re-read
  • Also usable through pd.ExcelWriter(engine="xlsxwriter")

⚠ it can't

  • Read a file. At all. Write-only by design
  • Modify or append to an existing workbook
  • Be swapped in when you later need to edit that template

Worth memorizing

openpyxl ≠ Excelit moves XML around; it never calculates anything
rows & cols start at 1not 0 — enumerate(data, start=2) under a header
data_only=Truereads Excel's cache; None if nothing ever computed it
never save data_onlyit writes the cached numbers over your formulas
save() overwritessilently, no prompt, no backup
styles are immutablec.font.bold = True does nothing — assign a new Font()
PatternFillneeds fill_type="solid" or the colour never shows
colours are aRGB hex"FF0000" — no leading #
merged cellsonly the top-left keeps a value; the rest read None
freeze_panes="A2"freezes everything above and left of that cell
max_row liesit counts formatted-but-empty rows too
append() is fastestand values_only=True is the fastest read
touching a cell creates iteven ws.cell(r, c) with no value costs memory
read_only → wb.close()mandatory; write_only saves exactly once
memory ≈ 50× file sizea 50 MB xlsx wants ~2.5 GB in normal mode
charts & imagesnot read back — they vanish on load + save
Table displayNamemust be unique and contain no spaces, or Excel calls it corrupt
.xls is not .xlsxdifferent binary format — convert before opening