Python Pandas: How to Add a Sheet to an Existing Excel File with Python Pandas

Adding a new worksheet to an existing Excel file without losing existing sheets requires using ExcelWriter in append mode. This preserves all current data while adding new content.

Prerequisites

Install the openpyxl engine for Excel file handling:

pip install openpyxl

Append a New Sheet

Use mode='a' (append) with ExcelWriter:

import pandas as pd

# Create data for new sheet
df = pd.DataFrame({
    'Product': ['Widget', 'Gadget'],
    'Sales': [100, 150]
})

# Append to existing Excel file
with pd.ExcelWriter('report.xlsx', mode='a', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Q1_Sales', index=False)

The existing sheets remain intact, and Q1_Sales is added.

Adding Multiple Sheets

import pandas as pd

df1 = pd.DataFrame({'A': [1, 2, 3]})
df2 = pd.DataFrame({'B': [4, 5, 6]})

with pd.ExcelWriter('data.xlsx', mode='a', engine='openpyxl') as writer:
    df1.to_excel(writer, sheet_name='Sheet_A', index=False)
    df2.to_excel(writer, sheet_name='Sheet_B', index=False)

Handling Existing Sheet Names

When a sheet name already exists, control the behavior with if_sheet_exists:

import pandas as pd

df = pd.DataFrame({'Updated': ['data', 'here']})

with pd.ExcelWriter('report.xlsx', mode='a', engine='openpyxl',
                    if_sheet_exists='replace') as writer:
    df.to_excel(writer, sheet_name='Summary', index=False)

Options for if_sheet_exists

Option	Behavior
'error'	Raise ValueError (default)
'replace'	Overwrite the existing sheet
'new'	Create new sheet with numeric suffix (Sheet1, Sheet2...)
'overlay'	Write on top of existing data (Pandas 1.4+)

import pandas as pd

# Create unique sheet name automatically
with pd.ExcelWriter('data.xlsx', mode='a', engine='openpyxl',
                    if_sheet_exists='new') as writer:
    df.to_excel(writer, sheet_name='Report', index=False)
    # If 'Report' exists, creates 'Report1'

warning

The if_sheet_exists parameter requires Pandas 1.3 or later. Earlier versions will raise an error if the sheet name exists.

Creating File If It Doesn't Exist

Handle both new and existing files:

import pandas as pd
import os

filepath = 'report.xlsx'
df = pd.DataFrame({'Data': [1, 2, 3]})

# Check if file exists
if os.path.exists(filepath):
    mode = 'a'  # Append to existing
else:
    mode = 'w'  # Create new file

# if_sheet_exists only valid in append mode
writer_kwargs = {'mode': mode, 'engine': 'openpyxl'}
if mode == 'a':
    writer_kwargs['if_sheet_exists'] = 'replace'

with pd.ExcelWriter(filepath, **writer_kwargs) as writer:
    df.to_excel(writer, sheet_name='Results', index=False)

Reusable Function

import pandas as pd
import os

def save_to_excel(df, filepath, sheet_name, if_exists='replace'):
    """Save DataFrame to Excel, creating file or appending sheet."""
    mode = 'a' if os.path.exists(filepath) else 'w'
    
    with pd.ExcelWriter(
        filepath,
        mode=mode,
        engine='openpyxl',
        if_sheet_exists=if_exists if mode == 'a' else None
    ) as writer:
        df.to_excel(writer, sheet_name=sheet_name, index=False)

# Usage
df = pd.DataFrame({'Sales': [100, 200]})
save_to_excel(df, 'report.xlsx', 'January')
save_to_excel(df, 'report.xlsx', 'February')

Common Errors

FileNotFoundError with mode='a'

# ❌ Error: File must exist for append mode
with pd.ExcelWriter('nonexistent.xlsx', mode='a') as writer:
    df.to_excel(writer)

# ✅ Check first or use try/except

Missing Engine

# ❌ May fail without explicit engine
with pd.ExcelWriter('file.xlsx', mode='a') as writer:
    df.to_excel(writer)

# ✅ Always specify engine for append mode
with pd.ExcelWriter('file.xlsx', mode='a', engine='openpyxl') as writer:
    df.to_excel(writer)

Quick Reference

Goal	Code
Add new sheet	mode='a', engine='openpyxl'
Replace existing sheet	if_sheet_exists='replace'
Auto-rename if exists	if_sheet_exists='new'
Raise error if exists	if_sheet_exists='error' (default)

Summary

• Use pd.ExcelWriter(path, mode='a', engine='openpyxl') to add sheets to existing Excel files while preserving other sheets.
• Control behavior for existing sheet names with if_sheet_exists
• Use 'replace' to overwrite or 'new' to create uniquely named sheets.

Always verify the file exists before using append mode.