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. It is the export half of Getting Started with Python Excel Automation: once your data is loaded — see Reading Excel Files with Pandas — and cleaned, this is how you get it back out to a workbook someone can open.

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].

Control what the sheet looks like as you write

to_excel takes more than a path, and four of its arguments decide most of how the result reads:

import pandas as pd

df = pd.DataFrame({
    "Region": ["North", "South", "West"],
    "Revenue": [26700.5, 20900.0, 7600.25],
    "Share": [0.484, 0.378, 0.138],
})

with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    df.to_excel(
        writer,
        sheet_name="Summary",
        index=False,             # no anonymous first column
        startrow=2,              # leave room for a title block
        startcol=1,              # and a left margin
        freeze_panes=(3, 0),     # freeze everything above the data rows
        na_rep="",               # blanks stay blank, not "nan"
    )
    sheet = writer.sheets["Summary"]
    sheet["B1"] = "Monthly summary"

print("written")

freeze_panes is the one people miss — it takes a row and column pair rather than a cell reference, and applying it here saves a second pass with openpyxl. startrow and startcol are what let a generated block sit under a title without the two fighting over row one.

index=False deserves its reputation. Leaving the index in produces a first column with no header, which breaks Excel tables, confuses usecols on the way back in, and looks like a mistake to a reader — because it is one.

Several frames, one workbook

An ExcelWriter context is the natural unit for a multi-sheet report, and everything inside it lands in a single file:

import pandas as pd

summary = pd.DataFrame({"Region": ["North", "South"], "Revenue": [26700, 20900]})
detail = pd.DataFrame({"Order_ID": [2001, 2002, 2003], "Revenue": [79.96, 99.0, 85.75]})

with pd.ExcelWriter("multi.xlsx", engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="Summary", index=False)
    detail.to_excel(writer, sheet_name="Detail", index=False)

    # Two blocks stacked on one sheet: compute the offset from the first frame
    summary.to_excel(writer, sheet_name="Combined", index=False, startrow=0)
    detail.to_excel(writer, sheet_name="Combined", index=False, startrow=len(summary) + 2)

The stacked layout is worth understanding because it comes up constantly in reports. startrow is zero-based and counts the header, so leaving len(first) + 2 gives one blank spacer row between the blocks — enough that a reader sees two tables rather than one confusing one, and enough that a filter over the second block does not swallow the first.

import pandas as pd

with pd.ExcelWriter("multi.xlsx", engine="openpyxl", mode="a",
                    if_sheet_exists="replace") as writer:
    detail.to_excel(writer, sheet_name="Detail", index=False)      # refresh one sheet

if_sheet_exists has three settings and the default — "error" — is the safe one. "replace" is what a monthly refresh wants; "new" creates Detail1, Detail2 and so on, which is almost never what anyone intends and is the usual explanation for a workbook full of numbered duplicates.

Styling belongs after the write

Any formatting applied before to_excel is discarded, because the sheet is replaced. Reaching the worksheet through the writer keeps it in one block of code:

import pandas as pd
from openpyxl.styles import Font, PatternFill

with pd.ExcelWriter("styled.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Summary", index=False)

    ws = writer.sheets["Summary"]
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill("solid", start_color="4338CA")
    for cell in ws[1]:
        cell.font = header_font
        cell.fill = header_fill

    for row in range(2, ws.max_row + 1):
        ws.cell(row=row, column=2).number_format = "#,##0.00"
        ws.cell(row=row, column=3).number_format = "0.0%"

    ws.freeze_panes = "A2"
    ws.column_dimensions["A"].width = 16

Reusing the two style objects rather than constructing them per cell keeps the workbook's style table small, which is what keeps a large report quick to open. The same principle scales: apply number formats per column, conditional formatting per range, and let Excel deduplicate the rest.

Types on the way out

What Excel shows is decided by two things: the value pandas wrote, and the number format on the cell. Getting the first right removes most formatting problems before they start.

import pandas as pd

df = pd.DataFrame({
    "Code": ["00412", "00733"],                       # keep as text or the zeros vanish
    "Order_Date": pd.to_datetime(["2026-01-14", "2026-02-02"]),
    "Quantity": [4, 2],
    "Revenue": [79.96, 99.0],
    "Share": [0.484, 0.516],
})

with pd.ExcelWriter("typed.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Orders", index=False)
    ws = writer.sheets["Orders"]
    for row in range(2, ws.max_row + 1):
        ws.cell(row=row, column=2).number_format = "yyyy-mm-dd"
        ws.cell(row=row, column=4).number_format = "#,##0.00"
        ws.cell(row=row, column=5).number_format = "0.0%"

Real datetime values plus a date format give the reader Excel's date filters and sorting; a date written as text gives them an alphabetical list of strings. A percentage column must hold the fraction — 0.484, not 48.4 — because the 0.0% format multiplies by a hundred, and writing the already-scaled number is how reports end up showing 48400%.

Timezone-aware datetimes are the one type Excel cannot represent. to_excel raises rather than guessing, so convert before writing:

aware = pd.to_datetime(["2026-01-14T09:30:00+01:00"])
naive = aware.tz_convert("UTC").tz_localize(None)          # decide the timezone, then drop it
print(naive)

Choosing UTC explicitly is the honest version — dropping the timezone without converting silently shifts every timestamp by the offset, which is the kind of error that surfaces months later in a reconciliation.

Writing large frames without the wait

The default path builds every cell as an object before anything reaches disk, so a few hundred thousand rows becomes slow and memory-hungry. Two changes fix it, and both trade away the ability to revisit a cell after writing it:

import pandas as pd

with pd.ExcelWriter(
    "large.xlsx",
    engine="xlsxwriter",
    engine_kwargs={"options": {"constant_memory": True}},
) as writer:
    big_df.to_excel(writer, sheet_name="Detail", index=False)

In constant_memory mode every column width, format and conditional rule has to be declared before the data is written, because a flushed row cannot be touched again. When the output is only ever read by another program, skipping the workbook entirely and writing Parquet is faster still and keeps the dtypes that CSV throws away.

Verify the file you produced

A write that raised no exception is not a write that worked. Reading the result back is quick and catches the mistakes that are otherwise found by a recipient:

import pandas as pd

check = pd.read_excel("typed.xlsx", sheet_name="Orders", dtype=object)
assert len(check) == len(df), f"row count {len(df)} -> {len(check)}"
assert list(check.columns) == list(df.columns), "columns changed on the round trip"
assert str(check.loc[0, "Code"]) == "00412", "leading zeros lost"
print("round trip verified")

The leading-zero assertion is the useful one to keep permanently. It is the failure that survives every other check — the file opens, the row count matches, the totals are right — and it breaks the join in whatever system reads the report next.

Naming the file the reader will look for

Output filenames are part of the interface. A stable name plus a dated archive copy covers both of the things people want — "the current one" and "March's one" — without either getting in the other's way:

import shutil
from datetime import date
from pathlib import Path

def publish(frame, stem="monthly_report", outdir="reports"):
    outdir = Path(outdir)
    (outdir / "archive").mkdir(parents=True, exist_ok=True)

    current = outdir / f"{stem}.xlsx"
    frame.to_excel(current, index=False, sheet_name="Summary")
    archived = outdir / "archive" / f"{stem}_{date.today():%Y-%m}.xlsx"
    shutil.copy2(current, archived)
    return current, archived

print(publish(df))

Timestamped-only filenames — report_20260302_060411.xlsx — look tidy in a folder listing and are miserable to link to, because the name changes every run. The pair above gives colleagues a URL or path that never moves while keeping every historical version, and it makes a rerun idempotent: the same month overwrites its own archive copy rather than adding a near-duplicate.

Common failures when writing

One writer, one responsibility

As a reporting script grows, the temptation is to let the function that computes the numbers also write, style and deliver them. Splitting those responsibilities keeps each part testable: a build() that returns a DataFrame can be asserted on without touching the filesystem, a write() that takes a frame and a path can be checked by reading the file back, and a deliver() that takes a path is the only piece that needs a mail server. Most reporting code that becomes hard to change did so by merging those three.

Frequently asked questions

Should I use openpyxl or xlsxwriter as the engine? Use openpyxl when you need append mode, read-back, or cross-platform compatibility. Reach for xlsxwriter only for rich cell formatting or charts on a brand-new file — it cannot edit existing workbooks.

Does float_format change how Excel displays numbers? No. float_format is printf-style (e.g. "%.2f") and changes the value pandas actually writes. To change only how Excel displays a number — currency symbols, thousands separators — set the cell's number_format with openpyxl after writing.

How do I put several tables in one file? Open one pd.ExcelWriter and call to_excel once per sheet inside a with block, which saves and closes the file. To stack blocks on a single sheet, use startrow/startcol offsets.

Why does append mode fail with Append mode is not supported with xlsxwriter?mode="a" is an openpyxl feature. Pass engine="openpyxl" and an if_sheet_exists policy; xlsxwriter only ever writes new files.

Why are my numbers showing up as text in Excel? The DataFrame column is strings, not numbers. Convert it with pd.to_numeric(df["col"]) before writing — a column of strings exports as text and won't aggregate in Excel.

Key takeaways

• DataFrame.to_excel() is a one-liner for a single table; pd.ExcelWriter inside a with block is what you reach for the moment you need more than one sheet.
• Pass index=False unless you deliberately want the row labels written — the extra leading column is almost never wanted in a report.
• float_format (printf-style, e.g. "%.2f") changes the value pandas writes; a cell's number_format changes only how Excel displays it. They are not interchangeable.
• Choose the engine by task: openpyxl for append mode, read-back, and cross-platform work; xlsxwriter only for rich formatting or charts on a brand-new file it cannot later edit.
• Do the formatting pass after writing, by reopening the file with openpyxl, so data logic and presentation stay in separate, testable steps.

Related

• Getting Started with Python Excel Automation — the parent guide to reading, transforming, and writing Excel with Python.
• Write Pandas DataFrame to Excel Without Index — the index gotchas, in full.
• openpyxl vs xlsxwriter vs pandas.ExcelWriter — which engine to reach for, and why.
• Reading Excel Files with Pandas — the other half of the loop, before you write data back out.
• Using openpyxl for Excel File Manipulation — cell-level control after the write.
• Emailing Excel Reports with smtplib — deliver the workbook once it is built.