Python Excel AutomationPractical guides for developers
Start learning
Guide
Getting Started With Python Excel AutomationDeep dive

openpyxl: Append Data to an Existing Excel Sheet

Add new rows to an existing .xlsx file with openpyxl — using ws.append(), explicit row indexing, dict-based sparse appends, type-safe values, and atomic saves.

You have an existing .xlsx file — a monthly report, a log, a running ledger — and you need to add rows without touching what is already there. The tool for that is openpyxl: open the file with load_workbook(), pick the worksheet, and call ws.append(). The method writes to the first empty row below your data and maps each list item to a column. Because openpyxl edits the Office Open XML directly, appending leaves existing values, formatting, and column widths intact. This guide shows the append patterns you actually need — and the cases where you should index rows explicitly instead. Every example runs: the first step builds the workbook we append to.

ws.append() writes new rows below the existing data An existing sheet holds rows 1 to 3; calling ws.append() twice writes rows 4 and 5 to the first empty rows at the bottom, leaving the existing rows untouched. Existing sheet 1 Date · Server · Status 2 Web-01 · Info 3 Web-02 · Warn 4 Web-03 · Info 5 Web-04 · Error ws.append([...]) writes to the first empty row below new rows

Prerequisites

  • Python 3.8 or newer and openpyxl installed: pip install openpyxl.
  • An existing .xlsx, .xlsm, or .xltx/.xltm file — or run Step 1 below to create a sample one. Legacy .xls files are not supported and must be converted first.
  • The target file must be closed in Excel and any other process. An open workbook holds a lock that makes wb.save() fail.
  • A working knowledge of the openpyxl load–edit–save loop. If it is new to you, start with Using openpyxl for Excel File Manipulation.

Step 1: Create the workbook to append to

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Q3_Data"
ws.append(["Date", "Server", "Status", "Note"])
ws.append(["2024-07-12", "Web-01", "Info", "Deploy 4.2"])
ws.append(["2024-07-14", "DB-02", "Warning", "Slow query"])
wb.save("monthly_report.xlsx")
print("Created with", ws.max_row, "rows")

Step 2: Append a single row

Load the file, select the sheet by name, append a list, and save. The list maps to columns A, B, C, D in order:

Python
from openpyxl import load_workbook

wb = load_workbook("monthly_report.xlsx")
ws = wb["Q3_Data"]

ws.append(["2024-07-15", "Server-04", "Critical", "Memory leak resolved"])

wb.save("monthly_report.xlsx")
print("Now", ws.max_row, "rows")

ws.append() always targets the row after the last one that contains data, so you never compute a position yourself for the common case.

Step 3: Append many rows in a loop

For batches, iterate and append. Accumulate the rows in memory first, then write them in one pass:

Python
wb = load_workbook("monthly_report.xlsx")
ws = wb["Q3_Data"]

batch = [
    ["2024-07-16", "DB-01", "Warning", "Index fragmentation"],
    ["2024-07-17", "Web-09", "Info", "Routine patch applied"],
    ["2024-07-18", "Cache-2", "Info", "TTL increased"],
]
for row in batch:
    ws.append(row)

wb.save("monthly_report.xlsx")
print("Appended", len(batch), "rows; total", ws.max_row)

Step 4: Append into specific columns with a dict

Pass a dict to write only certain columns, keyed by column letter or 1-based index. Unlisted columns stay empty. This is handy when a row only fills a few fields:

Python
wb = load_workbook("monthly_report.xlsx")
ws = wb["Q3_Data"]

# Only Date (A) and Status (C); Server and Note stay blank
ws.append({"A": "2024-07-19", "C": "Resolved"})

# Same idea using column numbers
ws.append({1: "2024-07-20", 3: "Info"})

wb.save("monthly_report.xlsx")
print("Row 8 values:", [c.value for c in ws[8]])

Step 5: Use the right types

Append native Python types, not strings, for anything you will sort, sum, or pivot later. Dates as datetime/date, numbers as int/float. A date stored as text will not aggregate or format like a real date — to control how a real date displays, set a number format on the cell:

Python
from datetime import date

wb = load_workbook("monthly_report.xlsx")
ws = wb["Q3_Data"]

ws.append([date(2024, 7, 21), "Batch-1", "Info", 1500])  # real date + real int
last_date = ws.cell(row=ws.max_row, column=1)
last_date.number_format = "yyyy-mm-dd"

wb.save("monthly_report.xlsx")
print("Appended typed row at", ws.max_row)

Step 6: Explicit row indexing when append misfires

ws.append() relies on ws.max_row, which tracks the largest used row. If a template carries trailing blank rows, a frozen summary block, or formatting that outruns the data — or rows were deleted (deletion does not always shrink max_row until the file is reopened) — append() can land in the wrong place. Write to explicit coordinates to guarantee placement:

Python
wb = load_workbook("monthly_report.xlsx")
ws = wb["Q3_Data"]

target = ws.max_row + 1
ws.cell(row=target, column=1, value="2024-07-22")
ws.cell(row=target, column=2, value="App-12")
ws.cell(row=target, column=3, value="Resolved")

wb.save("monthly_report.xlsx")
print("Wrote explicit row at", target)

Step 7: Save atomically

A crash or lock during wb.save() can corrupt the target file. Write to a temporary path first, then atomically replace the original with os.replace, which is a single filesystem operation:

Write-then-replace atomic save The in-memory workbook is saved to a temporary file with wb.save(), then os.replace swaps that temporary file over the original in one filesystem operation. The original stays intact until the single swap, so a crash while writing only leaves a stray .tmp file and never a half-written target. wb in memory monthly_report.tmp.xlsx write step — safe to fail monthly_report.xlsx target wb.save() os.replace() one atomic step The original is untouched until the swap. A crash mid-write leaves only a stray .tmp — the target is never half-written.
Python
import os
from openpyxl import load_workbook

wb = load_workbook("monthly_report.xlsx")
ws = wb["Q3_Data"]
ws.append(["2024-07-23", "Web-03", "Info", "Health check"])

tmp = "monthly_report.tmp.xlsx"
wb.save(tmp)
os.replace(tmp, "monthly_report.xlsx")   # atomic on the same filesystem
print("Saved atomically;", ws.max_row, "rows total")

Common pitfalls and gotchas

  • append() lands too low. The most common surprise: a stale ws.max_row from trailing blank rows or deleted-but-not-reopened rows. When placement matters, index explicitly as in Step 6 rather than trusting append().
  • Dates and numbers stored as text. Appending "2024-07-21" as a string gives you a label, not a date — it will not sort, sum, or filter as a date. Append native date/datetime and int/float, and apply the number or date format separately.
  • Wrong file format. Only .xlsx, .xlsm, and .xltx/.xltm are supported. A legacy .xls raises InvalidFileException — convert it to .xlsx first.
  • Formulas are not evaluated. append() writes raw values and formula strings verbatim; Excel recalculates formulas when it next opens the file. openpyxl itself never computes them, so reading an appended formula cell back returns the text, not a result.
  • The file is locked. If the workbook is open in Excel or another process, wb.save() raises PermissionError. The atomic-save pattern limits the corruption window but cannot bypass an active lock — close the file first.
  • Saving overwrites, not merges. wb.save() writes the whole in-memory workbook. If two scripts load the same file and each save, the last writer wins and the other's appends are lost. Serialize writes or use a lock.

Performance and scale notes

load_workbook() reads the entire file into memory, so appending a handful of rows to a large workbook still pays the full parse-and-rewrite cost on every run. A few ways to keep that manageable:

  • Batch your appends. Loading, appending one row, and saving in a tight loop reparses the file each pass. Load once, append the whole batch (Step 3), then save once.
  • Stream when generating from scratch. If you are building a large file rather than editing an existing one, Workbook(write_only=True) streams rows straight to disk and keeps memory flat. Write-only sheets cannot be read back or restyled cell-by-cell afterwards, so use them only for one-shot output.
  • Reach for pandas on bulk tabular data. When you are appending thousands of rows of structured data rather than a few event lines, writing a DataFrame to Excel is faster to express and to run. Concatenate in pandas, then write once.
  • Appending from many source files? If the rows come from a folder of workbooks, combine them into one file in a single pass instead of opening and appending to the target repeatedly.

Where append puts the row

ws.append compared with writing to a coordinate append adds a row after the sheet's current last row, which includes rows Excel considers used but that look empty. Writing to a computed coordinate puts the data exactly where you intend. ws.append(row) after ws.max_row simple and fast can skip a gap ws.cell(row=n, ...) exact position derived from a key column predictable

Conclusion

ws.append() is the right tool when you can trust that ws.max_row reflects the real last-used row: load the workbook, append lists or dicts, use native types, and save. For templates with trailing blank rows or deleted-row artifacts, skip it and write to explicit coordinates with ws.cell(row=target, column=n) instead. Either way, wrap the save in an atomic write-then-replace to protect against a corrupt file if the process is interrupted mid-save.

Frequently asked questions

Where does ws.append() put the new row? It writes to the first row after ws.max_row, the largest used row, and maps each list item to columns A, B, C, and so on. You never compute the position yourself in the common case.

Why did append() land in the wrong place?append() trusts ws.max_row. Trailing blank rows, a frozen summary block, or rows deleted without reopening the file can leave max_row stale, so the row lands too low. Write to explicit ws.cell(row=target, column=n) coordinates instead.

Can I append into only some columns? Yes. Pass a dict keyed by column letter ({"A": ..., "C": ...}) or 1-based index ({1: ..., 3: ...}). Unlisted columns stay empty for that row.

Why won't my appended dates sort or sum like dates? You probably appended them as strings. Append native datetime/date and int/float values, then set number_format on the cell for display — text values won't aggregate or format as real dates.

How do I avoid corrupting the file if the save is interrupted? Save to a temporary path, then os.replace(tmp, target) to swap it in atomically on the same filesystem. This limits the corruption window but cannot bypass an active file lock, which still raises PermissionError.