Append a DataFrame to an Existing Excel File with pandas

to_excel creates a file. Run it twice against the same path and the second run silently discards the first — which is a surprise the first time it costs you a month of accumulated data. Appending needs ExcelWriter with mode="a", and then a second decision about what happens to a sheet that already exists. This guide covers adding a new sheet, adding rows below existing data, and the engine constraint that makes appending fundamentally different from writing. It extends Writing DataFrames to Excel with pandas.

Prerequisites

pip install pandas openpyxl

A workbook to append to:

import pandas as pd

with pd.ExcelWriter("ledger.xlsx", engine="openpyxl") as writer:
    pd.DataFrame({
        "date": pd.to_datetime(["2026-06-01", "2026-07-01"]),
        "region": ["North", "North"],
        "revenue": [5150.00, 4820.50],
    }).to_excel(writer, sheet_name="Detail", index=False)

Step 1 — Add a new sheet without losing the old ones

mode="a" opens the existing workbook rather than creating a new one:

import pandas as pd

summary = pd.DataFrame({"region": ["North"], "total": [9970.50]})

with pd.ExcelWriter("ledger.xlsx", mode="a", engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="Summary", index=False)

print(pd.ExcelFile("ledger.xlsx").sheet_names)   # ['Detail', 'Summary']

Two things are worth stating plainly. mode="a" requires the file to exist — it raises FileNotFoundError otherwise, which makes a first run of a monthly job fail unless you handle it:

from pathlib import Path
import pandas as pd

def write_or_append(df, path, sheet_name):
    """Append to the workbook if it exists, create it if not."""
    path = Path(path)
    if path.exists():
        with pd.ExcelWriter(path, mode="a", engine="openpyxl",
                            if_sheet_exists="replace") as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)
    else:
        with pd.ExcelWriter(path, engine="openpyxl") as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)
    return path

And the engine is not a free choice. xlsxwriter cannot append — it only creates files, with no ability to read an existing one. pandas picks openpyxl automatically for mode="a", but if you name xlsxwriter explicitly you get a ValueError. The wider trade-off between the two writers is covered in openpyxl vs xlsxwriter vs pandas.ExcelWriter.

Step 2 — Choose what happens to an existing sheet

Writing to a sheet name that already exists needs if_sheet_exists, and the default raises rather than guessing:

import pandas as pd

# Refresh a sheet completely — the usual choice for a regenerated summary.
with pd.ExcelWriter("ledger.xlsx", mode="a", engine="openpyxl",
                    if_sheet_exists="replace") as writer:
    summary.to_excel(writer, sheet_name="Summary", index=False)

"replace" is right for a derived sheet you regenerate each run. "overlay" is the one that actually appends rows, and it needs a startrow to say where.

Step 3 — Append rows below the existing data

Find the last used row, then write below it with no header:

import pandas as pd
from openpyxl import load_workbook

def append_rows(path, sheet_name, df):
    """Add df's rows below whatever is already in the sheet."""
    book = load_workbook(path)
    if sheet_name not in book.sheetnames:
        raise KeyError(f"{sheet_name!r} is not in {path}")
    start = book[sheet_name].max_row          # 1-based; next free row index
    book.close()

    with pd.ExcelWriter(path, mode="a", engine="openpyxl",
                        if_sheet_exists="overlay") as writer:
        df.to_excel(
            writer,
            sheet_name=sheet_name,
            index=False,
            header=False,        # the header is already there
            startrow=start,      # 0-based here, so max_row lands one row below
        )
    return start

new_rows = pd.DataFrame({
    "date": pd.to_datetime(["2026-08-01"]),
    "region": ["North"],
    "revenue": [5402.75],
})
append_rows("ledger.xlsx", "Detail", new_rows)

The index arithmetic is the part to get right and easy to get wrong. ws.max_row is 1-based, and startrow is 0-based, so passing max_row directly places the first new row immediately after the last existing one. Passing max_row + 1 leaves a blank row; passing max_row - 1 overwrites the last row of existing data.

header=False matters just as much. Without it, the column names are written again in the middle of the data, and every downstream read treats that row as a record.

Guard against duplicates. An append with no key check will happily add August twice when a job retries:

import pandas as pd

def append_new_periods(path, sheet_name, df, key="date"):
    """Append only the rows whose key is not already present."""
    existing = pd.read_excel(path, sheet_name=sheet_name)
    already = set(existing[key].astype(str))

    fresh = df[~df[key].astype(str).isin(already)]
    if fresh.empty:
        print("nothing new to append")
        return 0

    append_rows(path, sheet_name, fresh)
    return len(fresh)

That idempotency is what makes a scheduled append safe to re-run, in the same spirit as the retry patterns in retrying a failed Excel report job.

Step 4 — Know what appending costs you

mode="a" opens the workbook through openpyxl, which parses the whole file and writes the whole file back. Nothing is written incrementally.

The rule that follows: append when a workbook is a living document you add to occasionally, and accumulate when a script owns the whole file.

import pandas as pd

# Owned by the script: collect everything, write once.
monthly = [load_month(m) for m in months]
pd.concat(monthly, ignore_index=True).to_excel(
    "ledger.xlsx", sheet_name="Detail", index=False, engine="xlsxwriter"
)

Common pitfalls and fixes

Performance and scale notes

Every mode="a" write is a full read-modify-write of the workbook. On a 20 MB ledger that is seconds per append, and twelve appends over a year cost more than a hundred times a single write of the same data.

Three alternatives when volume matters.

Accumulate in memory and write once, as above. This is the right answer whenever a script owns the file.

Append to CSV, convert at the end. CSV is genuinely append-only, so adding rows is O(rows added) regardless of file size:

import pandas as pd
from pathlib import Path

def append_csv(df, path):
    """Truly incremental — no re-parse of what is already there."""
    path = Path(path)
    df.to_csv(path, mode="a", header=not path.exists(), index=False)

# At the end of the period, convert once.
pd.read_csv("ledger.csv").to_excel("ledger.xlsx", index=False,
                                   engine="xlsxwriter")

Write one sheet per period instead of appending to one growing sheet. Each write touches only the new sheet, and reading them back is a single call with sheet_name=None, as described in reading all sheets into DataFrames:

import pandas as pd

with pd.ExcelWriter("ledger.xlsx", mode="a", engine="openpyxl",
                    if_sheet_exists="replace") as writer:
    august.to_excel(writer, sheet_name="2026-08", index=False)

One memory note: mode="a" holds the entire existing workbook in memory while it writes, so peak usage is roughly the whole file plus the new data. openpyxl's streaming write_only mode is not available here — it builds a new workbook and therefore cannot append — so for genuinely large accumulating datasets, the CSV route or one-sheet-per-period is the only approach that stays flat.

Conclusion

Appending to an existing workbook is pd.ExcelWriter(path, mode="a", engine="openpyxl") plus a decision about if_sheet_exists. Use "replace" for a derived sheet you regenerate, and "overlay" with startrow=ws.max_row and header=False when you genuinely want to add rows below existing data. Guard the first run, because mode="a" needs the file to exist, and guard against duplicates so a retry does not double a period. Then remember what it costs: every append rewrites the whole workbook, so when a script owns the file, accumulate the rows and write once instead.

Frequently asked questions

Why does my existing sheet disappear when I write to the file? The default ExcelWriter mode is "w", which creates a new file and discards whatever was there. Pass mode="a" to open the existing workbook instead of replacing it.

Can I append with the xlsxwriter engine? No. xlsxwriter can only create new files — it has no way to read an existing one. Appending requires engine="openpyxl", which pandas selects automatically when mode="a".

What does if_sheet_exists="overlay" do? It writes into the existing sheet without removing it, so combined with startrow you can add rows below what is already there. The alternatives are "replace", which discards the sheet's contents, and "new", which creates a numbered copy.

How do I find the row to append at? Open the workbook with openpyxl and read ws.max_row. Be aware it reports the used range, so on a sheet with stray formatting it can overreport — scan up for the last non-empty row if that is a risk.

Is appending in a loop a good idea? No. Each append re-opens, parses and rewrites the whole workbook, so a loop is quadratic. Accumulate the rows in memory and write once, or append to CSV and convert at the end.

Related

• Up to the parent: Writing DataFrames to Excel with pandas — the writing options this builds on.
• openpyxl vs xlsxwriter vs pandas.ExcelWriter — why only one engine can append.
• openpyxl: Append Data to an Existing Excel Sheet — the cell-level equivalent.
• Write Multiple DataFrames to One Excel File — writing everything in a single pass instead.
• Convert Excel to CSV with Python — the truly append-friendly format.