Writing DataFrames to Excel with Pandas

DataFrame.to_excel() writes a table to an .xlsx file in one line. Real reports ask for a bit more: several sheets in one workbook, consistent number formats, placement offsets, and appending to a file that already exists. This page covers each of those, building on one sample DataFrame so every block runs in order. Once your data is loaded — see Reading Excel Files with Pandas — this is how you get it back out.

Install pandas and an engine

pandas needs a backend to produce .xlsx. openpyxl handles writing (and editing existing files) and is all you need to follow along:

pip install pandas openpyxl

xlsxwriter is an alternative engine geared toward charts and rich formatting on new files; it cannot edit existing ones. Install it with pip install xlsxwriter if you need those features. The examples here use openpyxl.

The basic write

Build a DataFrame and write it. Pass index=False so pandas does not add its row numbers as a leading column — that extra column is almost never wanted in a report:

import pandas as pd

sales = pd.DataFrame({
    "Region": ["North", "South", "East", "West"],
    "Units": [120, 90, 75, 140],
    "Revenue": [2399.40, 1810.00, 1499.25, 2800.00],
})

sales.to_excel("report.xlsx", sheet_name="Sales", index=False)
print("Wrote report.xlsx")

Why index=False matters in practice, plus how it interacts with a MultiIndex and append mode, is covered in Write Pandas DataFrame to Excel Without Index.

Key parameters

to_excel() exposes the controls you reach for most often:

float_format is printf-style, not an Excel number format. It changes the value pandas writes, so use it for fixed decimal precision in the stored data:

sales.to_excel("report_2dp.xlsx", sheet_name="Sales",
               index=False, float_format="%.2f")
print("Wrote report_2dp.xlsx with 2-decimal revenue")

To control how Excel displays numbers (currency symbols, thousands separators) without changing the stored value, set a cell number format with openpyxl after writing — shown below.

Multi-sheet workbooks

To put several tables in one file, open a single ExcelWriter and call to_excel once per sheet. The with block saves and closes the file automatically:

by_region = sales.copy()
totals = pd.DataFrame({
    "Metric": ["Total Units", "Total Revenue"],
    "Value": [sales["Units"].sum(), sales["Revenue"].sum()],
})

with pd.ExcelWriter("workbook.xlsx", engine="openpyxl") as writer:
    totals.to_excel(writer, sheet_name="Summary", index=False)
    by_region.to_excel(writer, sheet_name="By_Region", index=False)

print("Wrote workbook.xlsx with 2 sheets")

You can also place more than one block on a single sheet using startrow/startcol — useful for a title row above a table, or two tables side by side:

with pd.ExcelWriter("stacked.xlsx", engine="openpyxl") as writer:
    totals.to_excel(writer, sheet_name="Dashboard", index=False, startrow=0)
    by_region.to_excel(writer, sheet_name="Dashboard", index=False,
                       startrow=len(totals) + 2)  # leave a blank gap row

print("Wrote stacked.xlsx")

Appending to an existing workbook

To add a sheet to a file that already exists, open the writer in append mode. if_sheet_exists decides what happens when the sheet name is already there — "replace", "overlay", or "error" (the default):

new_data = pd.DataFrame({"Region": ["Central"], "Units": [60], "Revenue": [999.0]})

with pd.ExcelWriter("workbook.xlsx", engine="openpyxl",
                    mode="a", if_sheet_exists="replace") as writer:
    new_data.to_excel(writer, sheet_name="Central", index=False)

print("Appended 'Central' sheet to workbook.xlsx")

Append mode is an openpyxl feature; it is not available with the xlsxwriter engine, which only writes new files. To add rows to an existing sheet rather than a new sheet, see openpyxl: Append Data to an Existing Sheet.

Formatting the output

pandas writes raw values; styling happens in the engine. The portable approach is to reopen the saved file with openpyxl and edit cells — this works regardless of which engine wrote the data:

from openpyxl import load_workbook
from openpyxl.styles import Font

wb = load_workbook("workbook.xlsx")
ws = wb["By_Region"]

# Bold the header row
for cell in ws[1]:
    cell.font = Font(bold=True)

# Display the Revenue column (column C) as currency, without changing the value
for row in ws.iter_rows(min_row=2, min_col=3, max_col=3):
    for cell in row:
        cell.number_format = "$#,##0.00"

# Widen columns to fit content
for column_cells in ws.columns:
    width = max(len(str(c.value)) for c in column_cells if c.value is not None)
    ws.column_dimensions[column_cells[0].column_letter].width = width + 2

wb.save("workbook.xlsx")
print("Formatted By_Region sheet")

For deeper styling — conditional formatting, freeze panes, charts — move to Using openpyxl for Excel File Manipulation and Applying Conditional Formatting with openpyxl.

Common errors and fixes

• ModuleNotFoundError: No module named 'openpyxl' — no engine installed. Run pip install openpyxl.
• PermissionError: [Errno 13] Permission denied — the target file is open in Excel, or the path is not writable. Close the file or write to a new name.
• ValueError: Append mode is not supported with xlsxwriter — append (mode="a") requires engine="openpyxl".
• A leading unnamed column — you omitted index=False.
• Numbers stored as text in Excel — make sure the DataFrame column is numeric (pd.to_numeric(df["col"])) before writing; a column of strings exports as text.
• InvalidWorksheetName / sheet name rejected — names cap at 31 chars and forbid \ / ? * [ ] :. Sanitize first: re.sub(r"[\\/?*\[\]:]", "", name)[:31].

Conclusion

to_excel() with ExcelWriter is the right export path for pandas data. Use openpyxl as the engine when you need append mode, read-back capability, or cross-platform compatibility; use xlsxwriter when you need rich cell formatting or charts on a new file. Do the formatting pass after writing, not during, so data logic and presentation concerns stay in separate, testable steps.

Where to go next

• Write Pandas DataFrame to Excel Without Index — the index gotchas, in full.
• Using openpyxl for Excel File Manipulation — cell-level control after the write.
• Emailing Excel Reports with smtplib — deliver the workbook once it is built.