Python Excel AutomationPractical guides for developers
Fix
Getting Started With Python Excel AutomationDeep dive

Fix "Sheet Already Exists" Errors in pandas ExcelWriter

Fix ValueError: Sheet 'Summary' already exists and if_sheet_exists is set to 'error' when writing to an existing workbook with pandas: what each if_sheet_exists option really does — error, new, replace and overlay — why overlay leaves old rows behind, the related "Append mode is not supported with xlsxwriter" error, and a safe way to refresh one sheet of a workbook.
Errors & fixesWriting Excel files

A common reporting job updates one sheet of an existing workbook — refreshes the Summary sheet every morning while the rest of the file, with its notes and charts, stays as it is. The first attempt is usually pd.ExcelWriter(path, mode="a"), and the second run fails with:

ValueError: Sheet 'Summary' already exists and if_sheet_exists is set to 'error'.

The message points at the fix — the if_sheet_exists argument — but its four values behave quite differently, and one of them, overlay, quietly leaves old data in the sheet. There is also a closely related error, Append mode is not supported with xlsxwriter!, which appears when the engine is not specified. This page reproduces both errors, shows exactly what each option does to a sheet, and settles on a safe way to refresh one sheet in a workbook. It belongs to Troubleshooting Common Python Excel Errors.

Prerequisites

Bash
pip install pandas openpyxl xlsxwriter

The examples were run with pandas 3. The behaviour described has been the same since pandas 1.4, when overlay was added.

Step 1 — Reproduce the errors

Python
import pandas as pd
from openpyxl import load_workbook

def make_report():
    with pd.ExcelWriter("report.xlsx", engine="openpyxl") as xw:
        pd.DataFrame({"Region": ["North", "South", "East", "West"], "Sales": [120, 95, 80, 60]}).to_excel(xw, sheet_name="Summary", index=False)
        pd.DataFrame({"Note": ["Figures exclude returns."]}).to_excel(xw, sheet_name="Notes", index=False)
    wb = load_workbook("report.xlsx")
    wb["Summary"]["D1"] = "Checked by: AH"
    wb.save("report.xlsx")

new_summary = pd.DataFrame({"Region": ["North", "South"], "Sales": [131, 88]})

make_report()
for kwargs in [{}, {"engine": "openpyxl"}]:
    try:
        with pd.ExcelWriter("report.xlsx", mode="a", **kwargs) as xw:
            new_summary.to_excel(xw, sheet_name="Summary", index=False)
    except ValueError as e:
        print(f"{str(kwargs):<22} ValueError: {e}")

The first attempt fails before even looking at the sheet: without an engine argument pandas picks XlsxWriter for .xlsx files whenever it is installed, and XlsxWriter can only create new files, so append mode is refused. With engine="openpyxl" the file is opened, and the error is the one in the title: the default for if_sheet_exists is "error", which refuses to touch a sheet that is already there. That default is deliberate — pandas will not guess whether you want to keep, replace or merge with the existing sheet.

Notice when the error appears. A job written with mode="a" often works the first time it runs against a fresh template that has no Summary sheet yet, and fails on the second run, when the sheet it created the day before is in the way. That is why the error tends to surface in production rather than in testing: test the job by running it twice against the same file.

Step 2 — What each option does

Run the same append once for each value and look at the workbook afterwards:

Python
for option in ["new", "replace", "overlay"]:
    make_report()
    with pd.ExcelWriter("report.xlsx", mode="a", engine="openpyxl", if_sheet_exists=option) as xw:
        new_summary.to_excel(xw, sheet_name="Summary", index=False)
    wb = load_workbook("report.xlsx")
    ws = wb["Summary"]
    regions = [c.value for c in ws["A"]]
    print(f"{option:<8} sheets={wb.sheetnames}  Summary column A={regions}  D1={ws['D1'].value!r}")

new leaves Summary untouched and writes the data to a new sheet called Summary1 — rarely what a refresh wants, and each run adds another sheet. replace deletes the old Summary sheet and writes a fresh one in the same position: the data is exactly the new DataFrame, but everything else on the sheet is gone too, including the Checked by note in D1, any formatting, column widths and charts placed on that sheet. overlay writes the new data into the existing sheet cell by cell, leaving everything it does not overwrite — which keeps the note in D1, but also keeps East and West with their old figures in rows 4 and 5, because the new data has only two rows. The sheet now mixes this morning's data with yesterday's, with nothing to show which is which.

The four if_sheet_exists options error refuses to write. new writes to a new sheet called Summary1 and leaves the old one. replace deletes the sheet and writes a fresh one in the same position, losing other content on it. overlay writes over the existing cells, keeping other content but also any old rows the new data does not reach. result other content on the sheet risk error ValueError untouched none — nothing written new new sheet Summary1 untouched sheets pile up replace fresh sheet lost (notes, formats) lost layout overlay written over old cells kept stale rows remain

Step 3 — Refresh a sheet safely

Which option is right depends on what else is on the sheet. If the sheet holds only data, replace is correct and simple. If it holds things you want to keep — notes, formatting, a chart over the data — use overlay, but clear the old data area first so no stale rows survive:

Python
def refresh_sheet(path, sheet, df, keep_cols=None):
    wb = load_workbook(path)
    ws = wb[sheet]
    last_col = keep_cols or len(df.columns)
    for row in ws.iter_rows(min_row=1, max_row=ws.max_row, max_col=last_col):
        for cell in row:
            cell.value = None
    wb.save(path)
    with pd.ExcelWriter(path, mode="a", engine="openpyxl", if_sheet_exists="overlay") as xw:
        df.to_excel(xw, sheet_name=sheet, index=False)

make_report()
refresh_sheet("report.xlsx", "Summary", new_summary)
ws = load_workbook("report.xlsx")["Summary"]
print([c.value for c in ws["A"]], [c.value for c in ws["B"]], "| D1:", ws["D1"].value)

Clearing the values in the data columns — and only those, here A and B — removes yesterday's rows while leaving the note in D1, cell formatting and anything in other columns in place; overlay then writes the new data on top. The result has exactly the two new rows and keeps the note. If the data has a fixed layout that people format by hand — a header style, number formats — clearing values rather than deleting cells is what preserves it. When the whole report is generated by code, the simplest robust alternative is to regenerate the entire workbook each time rather than patching one sheet; Append a DataFrame to an Existing Excel File with pandas covers the related case of adding rows below existing data.

Which option for a refresh If the sheet holds only data that the script writes, use replace. If it also holds notes, formatting or charts that must survive, clear the old data values and then write with overlay. If the old sheet must be kept as history, write to a new, dated sheet name instead of using new. What else is on the sheet? only the data if_sheet_exists='replace' simple and clean notes, formats, charts clear values + 'overlay' no stale rows keep history dated sheet name e.g. Summary 2026-10-02 Refreshing one sheet without losing its layout Open the workbook with openpyxl, set the values in the data columns to empty while leaving formats, notes and other columns alone, save, then append the new DataFrame with mode a, the openpyxl engine and if_sheet_exists overlay. The sheet then holds exactly the new rows and keeps its layout. Open with openpyxl existing workbook Clear data values columns A:B only Save layout untouched overlay write mode='a', engine='openpyxl'

What append mode keeps — and what it cannot

Append mode with openpyxl loads the whole workbook, changes the sheet you write to, and saves everything again. Most content survives the round trip: other sheets, their values and formatting, defined names, data validation and conditional formatting. Some does not, because openpyxl does not model it fully: charts and images are read back but can lose formatting details, shapes and text boxes are dropped, pivot tables are kept only partially, slicers and form controls are lost, and a macro-enabled .xlsm loses its VBA unless loaded with keep_vba=True, which pandas does not do. If the workbook contains such things, append mode is the wrong tool; either keep the generated data in a separate workbook that the main one links to, or drive Excel itself with xlwings. A quick test is to append to a copy of the real workbook and open both side by side before putting the job into production.

Checking before you write

Two further habits make append-mode jobs predictable. First, look before writing: load_workbook(path, read_only=True).sheetnames lists the sheets cheaply, so the job can decide between creating a sheet and refreshing it, or refuse with a clear message if the workbook is not the one it expects. Second, remember that mode="a" requires the file to exist; a job that must work on its first ever run should create the workbook with mode="w" when the file is missing and switch to append mode afterwards, rather than catching the exception. And because openpyxl rewrites the whole file when it saves, an append is not cheaper than regenerating the file for small workbooks — it is the right tool when the workbook contains things your code did not create and should not lose.

Common pitfalls

  • Leaving out engine="openpyxl". pandas picks XlsxWriter, which cannot append.
  • overlay with fewer rows than before. Old rows remain; clear the data area first.
  • replace on a formatted sheet. It deletes notes, formats and charts on that sheet.
  • new in a scheduled job. Each run adds Summary1, Summary2, …
  • A file that does not exist yet. Append mode needs an existing file; create it with mode="w" first.

Conclusion

The "Sheet already exists" error comes from if_sheet_exists defaulting to "error" in append mode. Open the writer with mode="a" and engine="openpyxl", then choose deliberately: replace when the sheet holds only data, or clear the old data values and use overlay when the sheet holds content worth keeping. Avoid new in recurring jobs, and remember that overlay alone leaves old rows behind whenever the new data is shorter.

Frequently asked questions

How do I fix "Sheet already exists and if_sheet_exists is set to 'error'"? Pass if_sheet_exists="replace" or "overlay" to pd.ExcelWriter together with mode="a" and engine="openpyxl", choosing based on what else is on the sheet.

What is the difference between replace and overlay in pandas ExcelWriter? replace deletes the sheet and writes a new one; overlay writes over the existing cells and leaves anything it does not overwrite, including old rows.

Why do I get "Append mode is not supported with xlsxwriter"? pandas chose the XlsxWriter engine, which only creates new files; pass engine="openpyxl" when using mode="a".

Why does my refreshed sheet still contain old rows? overlay only writes the cells covered by the new data; clear the old data area first or use replace.

More on errors & fixes

More on errors & fixes →