Using openpyxl for Excel File Manipulation

openpyxl reads and writes .xlsx and .xlsm files directly, without Excel installed. It works on the Office Open XML format, so the same script runs on Windows, macOS, and Linux. It is one of the core libraries covered in Getting Started with Python Excel Automation: where reading Excel files with pandas hands you whole DataFrames, openpyxl gives you cell-level control — number formats, fonts, borders, column widths, formulas, and images. This guide builds that control up one capability at a time. Every snippet is runnable — the first step creates a sample workbook so you can paste each example in order and watch it work.

Step 1: Install openpyxl

pip install openpyxl

openpyxl supports .xlsx, .xlsm, and .xltx/.xltm templates. It does not read the legacy binary .xls format — convert those to .xlsx first, or read them with pandas plus xlrd==1.2.0. Embedding images (Step 8) additionally needs Pillow: pip install Pillow.

Step 2: Create a workbook from scratch

A new Workbook() starts with one empty sheet, reachable through wb.active. Rename it, add headers, and append rows. ws.append() takes a list and writes it to the first empty row, mapping items to columns A, B, C, and so on:

from openpyxl import Workbook

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

# Header row, then data rows
ws.append(["Date", "Region", "Units", "Revenue"])
ws.append(["2024-01-05", "North", 12, 2399.88])
ws.append(["2024-01-06", "South", 5, 1247.50])
ws.append(["2024-01-07", "West", 8, 1599.20])

wb.save("report.xlsx")
print("Saved", ws.max_row, "rows across", ws.max_column, "columns")

Step 3: Load an existing workbook and navigate sheets

load_workbook() opens a file in read/write mode by default. Reach a sheet by exact name with wb["Sales"], list every tab with wb.sheetnames, or grab the active one with wb.active:

from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
print("Sheets:", wb.sheetnames)

ws = wb["Sales"]            # access by exact name
print("Active sheet:", wb.active.title)
print("Dimensions:", ws.dimensions)   # e.g. A1:D4

Sheet names are case-sensitive and keep trailing whitespace, so wb["sales"] or wb["Sales "] raises KeyError. Look the name up against wb.sheetnames if it comes from user input.

Step 4: Read cell values

Read a single cell by its coordinate (ws["A1"]) or by row/column number (ws.cell(row=1, column=1)) — both return a Cell, and .value holds its contents. To scan rows, iter_rows(values_only=True) yields plain tuples, which is the fastest way to pull data out:

wb = load_workbook("report.xlsx")
ws = wb["Sales"]

print("A1 by coordinate:", ws["A1"].value)
print("A1 by index:     ", ws.cell(row=1, column=1).value)

# Iterate data rows (skip the header with min_row=2)
for date, region, units, revenue in ws.iter_rows(min_row=2, values_only=True):
    print(f"{date} | {region} | {units} units | ${revenue:,.2f}")

A robust pattern for templates with shifting columns is to map header names to column indices from row 1, then look up each value by name instead of hardcoding positions:

header = next(ws.iter_rows(min_row=1, max_row=1, values_only=True))
col = {name: idx for idx, name in enumerate(header)}

total = 0.0
for row in ws.iter_rows(min_row=2, values_only=True):
    total += row[col["Revenue"]]
print(f"Total revenue: ${total:,.2f}")

Step 5: Write and edit cells

Assign to .value to set a cell, whether the cell exists or not. Writing past the current bounds extends the sheet automatically:

wb = load_workbook("report.xlsx")
ws = wb["Sales"]

# Add a totals row below the data
last = ws.max_row + 1
ws.cell(row=last, column=1, value="Total")
ws.cell(row=last, column=3, value=f"=SUM(C2:C{last - 1})")
ws.cell(row=last, column=4, value=f"=SUM(D2:D{last - 1})")

wb.save("report.xlsx")
print("Wrote totals to row", last)

The formulas use last - 1 (not ws.max_row) because writing any cell in the totals row updates ws.max_row to last, so referencing ws.max_row inside the same block would create a formula that includes the totals cell itself — a circular reference.

openpyxl writes formulas as strings; it does not evaluate them. Excel calculates the result when the file is opened. To read a previously cached result instead of the formula text, load with data_only=True — but note that a file written by openpyxl and never opened in Excel has no cached value yet, so data_only=True returns None for those cells.

Step 6: Apply number formats and styles

Styling lives in the openpyxl.styles module. Set cell.number_format for display formatting (dates, currency, percentages) and assign Font, Alignment, Border, and PatternFill objects for appearance:

from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side

wb = load_workbook("report.xlsx")
ws = wb["Sales"]

# Bold, white-on-blue header row
header_fill = PatternFill("solid", fgColor="305496")
for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")

# Currency format on the Revenue column (column D)
for cell in ws["D"][1:]:          # skip the header cell
    cell.number_format = "#,##0.00"

# Thin border around every used cell
thin = Side(style="thin")
box = Border(left=thin, right=thin, top=thin, bottom=thin)
for row in ws.iter_rows():
    for cell in row:
        cell.border = box

wb.save("report.xlsx")
print("Applied header, currency, and border styles")

Style objects are immutable and can be reused across many cells, which keeps memory low on large sheets. Set them once and assign the same object repeatedly. For a deeper treatment of fonts, fills, borders, and alignment see styling Excel cells with openpyxl, and for the currency, date, and percentage codes that go in number_format see applying number and date formats.

Step 7: Set column widths and freeze the header

Column widths are stored on ws.column_dimensions[<letter>].width in character units. Auto-sizing means measuring the longest value in each column. Freezing panes keeps the header visible while scrolling:

wb = load_workbook("report.xlsx")
ws = wb["Sales"]

# Auto-fit each column to its longest value
for column_cells in ws.columns:
    longest = max(len(str(cell.value)) for cell in column_cells if cell.value is not None)
    letter = column_cells[0].column_letter
    ws.column_dimensions[letter].width = longest + 2

ws.freeze_panes = "A2"   # rows above row 2 stay pinned
wb.save("report.xlsx")
print("Adjusted widths and froze the header row")

Step 8: Embed an image

Reports often need a logo or chart image. openpyxl.drawing.image.Image anchors a picture to a cell — the same mechanism covered in depth in inserting images and logos into Excel. This example generates a tiny PNG with the standard library so it runs without any external file:

import struct, zlib
from openpyxl import load_workbook
from openpyxl.drawing.image import Image

# Write a minimal 1x1 PNG so the example is self-contained
def _png_chunk(tag, data):
    return (struct.pack(">I", len(data)) + tag + data
            + struct.pack(">I", zlib.crc32(tag + data) & 0xffffffff))

raw = b"\x00" + bytes((48, 84, 150))          # one blue pixel
png = (b"\x89PNG\r\n\x1a\n"
       + _png_chunk(b"IHDR", struct.pack(">IIBBBBB", 1, 1, 8, 2, 0, 0, 0))
       + _png_chunk(b"IDAT", zlib.compress(raw))
       + _png_chunk(b"IEND", b""))
with open("logo.png", "wb") as f:
    f.write(png)

wb = load_workbook("report.xlsx")
ws = wb["Sales"]
img = Image("logo.png")
img.width, img.height = 120, 40        # scale in pixels
ws.add_image(img, "F1")                # anchor to cell F1
wb.save("report.xlsx")
print("Embedded image anchored at F1")

Working with large workbooks

Standard mode loads the whole workbook into memory. For very large files, switch modes:

• load_workbook("big.xlsx", read_only=True) streams rows instead of building the full object tree, dramatically cutting memory use for reads. Read-only worksheets do not support append() or cell writes.
• Workbook(write_only=True) streams rows out as you append() them, ideal for generating large files. Write-only workbooks cannot be read back or styled cell-by-cell after the fact.

A common split is to generate bulk data in write-only mode, then reopen the file in standard mode to apply styling to the comparatively small header and summary regions.

Common errors and fixes

Moving around a sheet without hardcoding

Cell references written by hand are correct exactly once. Anything that runs monthly should derive its positions from the sheet, and openpyxl gives three tools for it:

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter, column_index_from_string

wb = load_workbook("orders.xlsx")
ws = wb["Orders"]

# 1. Find a column by its header rather than assuming a letter
headers = {cell.value: cell.column for cell in ws[1]}
revenue_col = headers["Revenue"]
revenue_letter = get_column_letter(revenue_col)

# 2. Find the last row that actually has data in a key column
last_row = max(
    (cell.row for cell in ws[get_column_letter(headers["Order_ID"])] if cell.value is not None),
    default=1,
)

# 3. Iterate a rectangle, values only
total = 0.0
for (value,) in ws.iter_rows(min_row=2, max_row=last_row,
                             min_col=revenue_col, max_col=revenue_col, values_only=True):
    total += value or 0

print(f"{revenue_letter}2:{revenue_letter}{last_row} sums to {total:,.2f}")

The header map is the important habit. A source system that inserts a column at position two breaks every hardcoded letter in the script, silently and in a way that produces plausible numbers — while a lookup by name simply keeps working.

Deriving last_row from a key column rather than from ws.max_row matters on any sheet that has been through Excel: max_row counts rows that Excel considers used, including ones that hold only a border or a stray format, so it routinely overshoots the real data by hundreds of rows.

Insert, delete and move without breaking things

Structural edits are where in-place editing gets interesting, because everything below a change moves:

from openpyxl import load_workbook

wb = load_workbook("orders.xlsx")
ws = wb["Orders"]

ws.insert_rows(1)                       # make room for a title
ws["A1"] = "Monthly order report"

ws.insert_cols(3)                       # a new column between B and C
ws.cell(row=2, column=3, value="Segment")

ws.delete_rows(ws.max_row)              # drop a trailing blank row
ws.move_range("E2:E10", rows=0, cols=1) # shift a block one column right

wb.save("orders_restructured.xlsx")

Three caveats travel with these methods, and knowing them saves a confusing afternoon. Formulas are not rewritten — a =SUM(D2:D10) still points at D after a column insert, even though the data it summed is now in E. Merged ranges and conditional formatting are likewise not adjusted. And move_range moves values and styles but leaves any formula referring to the old location pointing where it always did.

For anything beyond a cosmetic tweak, rebuilding the sheet from data is usually safer than editing it structurally: write the frame fresh, then re-apply the formatting, which is the same "data first, decoration second" ordering the rest of this guide relies on.

Copy a sheet, and know what comes with it

Duplicating a template tab is a common building block for per-region reports:

from openpyxl import load_workbook

wb = load_workbook("template.xlsx")
template = wb["Template"]

for region in ["North", "South", "West"]:
    copy = wb.copy_worksheet(template)
    copy.title = region
    copy["B1"] = region

wb.save("regional_from_template.xlsx")
print(wb.sheetnames)

copy_worksheet carries values, styles, dimensions and merged cells, and it deliberately does not carry images, charts or data validation rules. For a template that relies on those, either re-apply them per copy or build each sheet from scratch — silently losing a dropdown that the original tab had is the kind of difference nobody notices until a form comes back full of typos.

Reading a workbook you did not write

Files that arrive from elsewhere need inspecting before they can be trusted. A short survey answers the questions that decide how the rest of the script should work:

from openpyxl import load_workbook

wb = load_workbook("supplier.xlsx", data_only=True)

print("sheets:", wb.sheetnames)
for ws in wb.worksheets:
    merged = len(ws.merged_cells.ranges)
    validations = len(ws.data_validations.dataValidation)
    tables = list(ws.tables)
    print(
        f"{ws.title:16} {ws.max_row:>6} rows x {ws.max_column:>3} cols  "
        f"merged={merged:<3} validations={validations:<3} tables={tables}"
    )
    print("   header:", [c.value for c in ws[1][:8]])

Three of those numbers change how you write the rest. Merged ranges mean blank cells that a reader sees as filled. Data validation means the file is a form, so it probably comes back edited and the rules should survive your rewrite. Tables mean a range Excel maintains, which your appended rows will fall outside of unless you extend it.

data_only=True is chosen deliberately here: on a file from a supplier the cached results exist, because Excel saved it, so the survey shows the numbers a reader sees rather than formula text.

Editing in place without losing what you did not touch

The most common openpyxl job is a small edit to an existing workbook — update three cells, add a row, keep everything else exactly as it was. Loading and saving with the default settings preserves styles, widths, merged cells, images and charts, which is why openpyxl rather than pandas is the right tool for it:

from openpyxl import load_workbook

wb = load_workbook("monthly_template.xlsx")     # NOT data_only — keeps formulas
ws = wb["Summary"]

ws["B2"] = 48_211                                # this month's figures
ws["B3"] = 1_284_600.00
ws["B4"] = "=B3/B2"                              # a formula, left for Excel to evaluate
ws["B3"].number_format = "#,##0.00"

wb.save("monthly_2026_03.xlsx")                  # save under a new name

Two habits make this safe. Load without data_only whenever the file contains formulas you intend to keep, because saving from a data_only handle replaces every formula with whatever number was cached in it. And save to a new filename, so the template survives a mistake — overwriting the source is the one error that cannot be undone by re-running.

Merged cells, and why they complicate everything

Merges look like formatting and behave like structure. Only the top-left cell of a merged range holds a value; the rest are read-only MergedCell objects, and writing to one raises:

from openpyxl import load_workbook
from openpyxl.utils import range_boundaries

wb = load_workbook("regional.xlsx")
ws = wb["Report"]

for merged in list(ws.merged_cells.ranges):
    min_col, min_row, max_col, max_row = range_boundaries(str(merged))
    anchor = ws.cell(row=min_row, column=min_col)
    print(f"{merged} holds {anchor.value!r}")

ws.unmerge_cells("A2:A6")                 # flatten a grouping column before processing
for row in range(2, 7):
    ws.cell(row=row, column=1, value="North")

wb.save("regional_flat.xlsx")

Unmerging and filling the label down is usually the right move before any programmatic processing, because it turns a presentational grouping into ordinary data. Re-merge at the end if the layout matters to the reader — presentation last, as always.

Save cost and when to batch it

wb.save() serialises the whole workbook every time, so saving inside a loop is the classic reason a script that "should take seconds" takes minutes. Make every change, then save once:

from openpyxl import load_workbook

wb = load_workbook("orders.xlsx")
ws = wb["Orders"]

for row in range(2, ws.max_row + 1):              # thousands of edits
    ws.cell(row=row, column=6, value=f"=C{row}*D{row}")

wb.save("orders_with_formulas.xlsx")              # exactly one save

The exception is a long-running job where an interruption would lose hours of work — there, saving periodically to a temporary file is worth the cost, provided the final result is renamed into place rather than written over the target directly.

Frequently asked questions

Why does data_only=True return None for my formula cells?data_only=True reads Excel's cached result, not the formula text. A file written by openpyxl and never opened in Excel has no cached value yet, so those cells read back as None until Excel recalculates and saves them.

Can openpyxl open a legacy .xls file? No. openpyxl handles only .xlsx, .xlsm, and .xltx/.xltm. Opening a .xls raises InvalidFileException — convert it to .xlsx first, or read it with pandas plus xlrd==1.2.0.

Why does wb["sales"] raise a KeyError when the tab clearly exists? Sheet lookups are case-sensitive and preserve trailing whitespace, so "sales" or "Sales " will not match "Sales". Compare against wb.sheetnames and strip the name before looking it up.

Do my style changes save automatically? No. openpyxl holds all edits in memory until you call wb.save(). Save after each logical group of changes, or nothing is written to disk.

How do I keep memory low on a very large workbook? Open with load_workbook(path, read_only=True) to stream rows for reads, or use Workbook(write_only=True) to stream rows out when generating files. Read-only sheets can't be written and write-only sheets can't be styled cell-by-cell after the fact.

Key takeaways

• openpyxl edits the Office Open XML layer directly — the same XML Excel reads and writes — so no Excel install is needed and the same script runs on Windows, macOS, and Linux.
• The object model is Workbook → Worksheet → Cell; the everyday loop is load_workbook(), edit each .value, then wb.save().
• Nothing reaches disk until you call wb.save(). Save after every logical group of changes, not once at the very end and hope.
• Formulas are stored as strings and evaluated by Excel, not by openpyxl; data_only=True returns a value only after Excel has opened the file and cached a result.
• Sheet names are case-sensitive and keep trailing whitespace — look them up against wb.sheetnames.
• For very large workbooks, stream with read_only=True for reads or write_only=True for generation, then reopen in standard mode to style the small header and summary regions.

Related

• Getting Started with Python Excel Automation — the parent overview of the full toolchain and when to pick each library.
• openpyxl: Append Data to an Existing Excel Sheet — safe incremental updates to a populated workbook.
• Read a Cell Value from Excel with openpyxl — single cells, ranges, and cached formula results.
• Reading Excel Files with pandas — load tabular data into DataFrames when you think in rows, columns, and aggregations.
• Writing DataFrames to Excel with Pandas — for bulk numeric data, use pandas for the ETL and hand the file to openpyxl for styling.
• Working with Multiple Excel Sheets in Python — read, merge, and write multi-tab workbooks across pandas and openpyxl.
• Styling Excel Cells with openpyxl — go further with fonts, fills, borders, and conditional appearance.