How to Append Data to an Existing Excel File Using Pandas to_excel

Use DataFrame.to_excel with mode="a" and the if_sheet_exists parameter to append data to existing Excel workbooks without overwriting previous content.

The pandas-dev/pandas repository provides a robust Excel I/O system that supports appending DataFrames to existing files through the ExcelWriter class. When you need to add new data to an existing workbook rather than replacing it entirely, the to_excel method offers specific parameters designed for this workflow.

How ExcelWriter Handles Append Mode

The core logic for appending to Excel files resides in pandas/io/excel/_base.py, specifically within the ExcelWriter class. When you specify mode="a", the writer validates that the target file exists and loads it using the appropriate engine (typically openpyxl for .xlsx files).

In pandas/core/generic.py, the DataFrame.to_excel method constructs an ExcelWriter instance with your specified parameters. If mode="a" is provided and the target sheet already exists, Pandas requires the if_sheet_exists argument to determine whether to create a new sheet, overlay data onto the existing sheet, or replace the sheet entirely.

Appending Data with to_excel: Three Practical Approaches

Adding a New Sheet to an Existing Workbook

The simplest append operation creates a new worksheet within an existing file. This approach requires mode="a" and avoids conflicts with existing sheets by using a unique sheet_name.

import pandas as pd

new_data = pd.DataFrame({
    "Product": ["Widget A", "Widget B"],
    "Revenue": [1200, 850]
})

# Append as a new sheet named "Q2"

new_data.to_excel(
    "sales_report.xlsx",
    sheet_name="Q2",
    index=False,
    mode="a"
)

Appending Rows to an Existing Sheet Using Overlay Mode

To add data below existing rows in the same sheet, use if_sheet_exists="overlay" combined with the startrow parameter. First, determine the last row of existing data, then write the new DataFrame starting at the next available row.

# Read existing data to find the starting row

existing = pd.read_excel("sales_report.xlsx", sheet_name="Q1")
start_row = len(existing) + 2  # +2 accounts for header and 0-indexing

new_data.to_excel(
    "sales_report.xlsx",
    sheet_name="Q1",
    startrow=start_row,
    index=False,
    header=False,  # Don't duplicate column headers

    mode="a",
    if_sheet_exists="overlay"
)

Replacing an Existing Sheet Completely

When you need to refresh data in an existing sheet while preserving other sheets in the workbook, use if_sheet_exists="replace". This deletes the target sheet and recreates it with the new DataFrame content.

new_data.to_excel(
    "sales_report.xlsx",
    sheet_name="Q1",
    index=False,
    mode="a",
    if_sheet_exists="replace"
)

Critical Parameters for Excel Append Operations

Understanding these parameters ensures reliable append operations without data loss:

Parameter	Required	Description
mode	Yes for append	Use "a" for append mode. Default is "w" (write/overwrite).
if_sheet_exists	Yes when appending to existing sheets	Controls behavior when target sheet exists: "new" (error), "overlay" (write into existing), or "replace" (delete and recreate).
startrow / startcol	Optional	Zero-indexed row/column offsets for positioning data within the target sheet. Essential for overlay operations.
header	Optional	Set to False when appending rows to prevent duplicate column headers.
engine	Optional	Explicitly specify "openpyxl" for .xlsx append operations. Note that xlsxwriter does not support append mode.

Engine Compatibility Note: The append functionality relies on engine-specific implementations in files like pandas/io/excel/_openpyxl.py. The xlsxwriter engine (implemented in pandas/io/excel/_xlsxwriter.py) only supports write mode and cannot append to existing files. Always use openpyxl when appending to .xlsx files.

Summary

• Use mode="a" in DataFrame.to_excel to open existing Excel files for appending rather than overwriting.
• Specify if_sheet_exists when targeting sheets that may already exist, choosing between "overlay" (merge data), "replace" (refresh sheet), or "new" (error on conflict).
• Calculate startrow manually when using overlay mode to position new data below existing rows without overwriting.
• Set header=False when appending rows to existing sheets to avoid duplicating column headers.
• Use the openpyxl engine for append operations, as xlsxwriter does not support reading or modifying existing workbooks.

Frequently Asked Questions

Can I append to an Excel file using the xlsxwriter engine?

No. The xlsxwriter engine only supports creating new files and cannot read or append to existing workbooks. According to the implementation in pandas/io/excel/_xlsxwriter.py, this engine lacks the necessary hooks to load existing files. Use the openpyxl engine instead when appending to .xlsx files.

What is the difference between if_sheet_exists='overlay' and 'replace'?

The overlay option writes DataFrame data into the existing sheet starting at the specified startrow and startcol, preserving other content in the sheet. The replace option deletes the entire existing sheet and creates a fresh sheet with the same name containing only the new DataFrame data. Use overlay to append rows, and replace to refresh entire sheets.

How do I append data below existing rows without overwriting them?

First, read the existing sheet to determine the last row using pd.read_excel(). Calculate the starting row as len(existing_data) + offset (typically +2 to account for the header row and zero-indexing). Then call to_excel() with mode="a", if_sheet_exists="overlay", startrow=calculated_row, and header=False to prevent duplicate headers.

Why do I get an error when using mode='a' without if_sheet_exists?

Pandas requires explicit instruction when appending to a file that already contains a sheet with your target name. If you specify mode="a" and the sheet_name already exists, Pandas raises a ValueError unless you provide the if_sheet_exists parameter to define whether to error, overlay, or replace. This safety mechanism prevents accidental data loss when appending to existing workbooks.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}