Every quarter, a sales manager I work with asks for the same thing: the numbers from my pandas script, but as a workbook she can open, filter and forward. That’s the job of a pandas write DataFrame to Excel step.
Renaming a CSV to .xlsx doesn’t work; Excel warns that the format and extension don’t match.
Quick answer: call DataFrame.to_excel() with a file name. Add index=False so the row numbers don’t become an extra column:
df.to_excel("q1_sales.xlsx", sheet_name="Q1 2026", index=False)In this tutorial, I’ll export one sheet, write several sheets with ExcelWriter, append to an existing file, format dates and USD values, add a chart and fix the errors you’ll actually hit. Everything below ran on Python 3.12.5, pandas 3.0.6, openpyxl 3.1.5 and XlsxWriter 3.3.1.
How do you write a pandas DataFrame to an Excel file?
You need pandas plus one Excel engine: openpyxl or XlsxWriter. Install both with pip install pandas openpyxl xlsxwriter. If the import still fails, my notes on a ModuleNotFoundError after pip install usually explain why.
The running sample is q1_sales.py: six orders from January to March 2026, with US regions, reps and revenue in USD. Every example imports it:
import pandas as pd
sales = pd.DataFrame({
"Order Date": pd.to_datetime(["2026-01-06", "2026-01-19", "2026-02-03",
"2026-02-24", "2026-03-10", "2026-03-27"]),
"Region": ["Northeast", "Midwest", "South", "West", "Northeast", "West"],
"Rep": ["Emily Carter", "James Wilson", "Olivia Brown",
"Liam Davis", "Emily Carter", "Ava Miller"],
"Units": [42, 18, 35, 27, 50, 31],
"Revenue": [12590.50, 5418.00, 9875.25, 8120.40, 14995.00, 9310.75],
})Now write it to a sheet named Q1 2026 and read it back to prove the file is complete:
import pandas as pd
from q1_sales import sales
sales.to_excel("q1_sales.xlsx", sheet_name="Q1 2026", index=False)
check = pd.read_excel("q1_sales.xlsx", sheet_name="Q1 2026")
print(check.shape)
print(check.to_string(index=False))
print("Total revenue:", f"${check['Revenue'].sum():,.2f}")Output:
(6, 5)
Order Date Region Rep Units Revenue
2026-01-06 Northeast Emily Carter 42 12590.50
2026-01-19 Midwest James Wilson 18 5418.00
2026-02-03 South Olivia Brown 35 9875.25
2026-02-24 West Liam Davis 27 8120.40
2026-03-10 Northeast Emily Carter 50 14995.00
2026-03-27 West Ava Miller 31 9310.75
Total revenue: $60,309.90
In Excel, the data is right but plain: no number formats, and the date column is too narrow for date plus time.
By default the file lands in the folder you ran the script from. Pass a full path, or a check that the file exists first if you don’t want to overwrite last quarter’s report.
Why does my Excel file have an extra index column?
to_excel() writes the DataFrame index as column A unless you say otherwise. Read that file back and pandas names it Unnamed: 0:
import pandas as pd
from q1_sales import sales
sales.to_excel("with_index.xlsx") # index=True is the default
print(pd.read_excel("with_index.xlsx").columns.tolist())
print(pd.read_excel("with_index.xlsx", index_col=0).columns.tolist())Output:
['Unnamed: 0', 'Order Date', 'Region', 'Rep', 'Units', 'Revenue']
['Order Date', 'Region', 'Rep', 'Units', 'Revenue']So use index=False when the index is only a row counter. Keep it when it carries meaning, like a date index, and read it back with index_col=0.
Already stuck with the column? Here’s how to drop the Unnamed column in pandas.
Write multiple DataFrames to separate Excel sheets with ExcelWriter
Calling to_excel() twice on the same file name replaces the whole file. To keep several sheets, open one pd.ExcelWriter and pass it to each call:
import pandas as pd
from openpyxl import load_workbook
from q1_sales import sales
summary = sales.groupby("Region", as_index=False)["Revenue"].sum()
with pd.ExcelWriter("q1_by_region.xlsx", datetime_format="yyyy-mm-dd") as writer:
summary.to_excel(writer, sheet_name="Summary", index=False)
for region, rows in sales.groupby("Region"):
rows.to_excel(writer, sheet_name=region, index=False)
print("Engine:", writer.engine)
print(load_workbook("q1_by_region.xlsx").sheetnames)
print(summary.to_string(index=False))Output:
Engine: xlsxwriter
['Summary', 'Midwest', 'Northeast', 'South', 'West']
Region Revenue
Midwest 5418.00
Northeast 27585.50
South 9875.25
West 17431.15
The with block saves and closes the file for you. Older tutorials call writer.save(), which pandas removed in 2.0, so stick to the context manager.
Sheet names can be 31 characters at most, which matters when you build them from data. The groupby aggregation functions guide shows other ways to build the summary.
Append a new sheet to an existing Excel file
Next quarter, the manager wants Q2 targets added to the same workbook. Use mode="a" with the openpyxl engine; XlsxWriter only creates new files:
import pandas as pd
from openpyxl import load_workbook
targets = pd.DataFrame({"Region": ["Midwest", "Northeast", "South", "West"],
"Q2 Target": [7500.00, 30000.00, 12000.00, 20000.00]})
with pd.ExcelWriter("q1_by_region.xlsx", mode="a", engine="openpyxl",
if_sheet_exists="replace") as writer:
targets.to_excel(writer, sheet_name="Targets", index=False)
print(load_workbook("q1_by_region.xlsx").sheetnames)
print(pd.read_excel("q1_by_region.xlsx", sheet_name="Targets").to_string(index=False))Output:
['Summary', 'Midwest', 'Northeast', 'South', 'West', 'Targets']
Region Q2 Target
Midwest 7500
Northeast 30000
South 12000
West 20000
if_sheet_exists decides what happens when the sheet name is taken: "error" (the default), "new", "replace" or "overlay". I use replace for scheduled reports so reruns don’t fail.
New orders belong in the DataFrame, not the sheet: add rows to the DataFrame, then write it again.
Set the start row, headers and missing values in to_excel()
Most layout requests are a keyword away. Here I total revenue per rep, add a new hire with no orders, start the table on row 3 and rename the headers:
import pandas as pd
from q1_sales import sales
reps = sales.groupby("Rep", as_index=False)[["Units", "Revenue"]].sum()
reps.loc[len(reps)] = ["Noah Garcia", None, None] # new hire, no orders yet
reps.to_excel("q1_reps.xlsx", sheet_name="Reps", index=False,
startrow=2, na_rep="n/a", float_format="%.2f",
header=["Sales Rep", "Units Sold", "Revenue (USD)"],
freeze_panes=(3, 0))
print(pd.read_excel("q1_reps.xlsx", skiprows=2).to_string(index=False))Output:
Sales Rep Units Sold Revenue (USD)
Ava Miller 31.0 9310.75
Emily Carter 92.0 27585.50
James Wilson 18.0 5418.00
Liam Davis 27.0 8120.40
Olivia Brown 35.0 9875.25
Noah Garcia NaN NaNna_rep writes the text n/a into empty cells; read_excel() treats n/a as missing, so it reads back as NaN. float_format="%.2f" rounds floats to two decimals.
Other useful ones are columns=[...] to export a subset, startcol and header=False. If you need to sort the DataFrame by a column, do it before the export.
Write dates to Excel without 00:00:00
pandas stores dates as datetimes, so Excel receives a date plus midnight. The cell format below comes straight from the saved files:
import pandas as pd
from openpyxl import load_workbook
from q1_sales import sales
sales.to_excel("default_dates.xlsx", index=False)
with pd.ExcelWriter("us_dates.xlsx", datetime_format="mm/dd/yyyy") as writer:
sales.to_excel(writer, index=False)
for name in ("default_dates.xlsx", "us_dates.xlsx"):
cell = load_workbook(name).active["A2"]
print(f"{name:<19} {cell.value} format: {cell.number_format}")Output:
default_dates.xlsx 2026-01-06 00:00:00 format: YYYY-MM-DD HH:MM:SS
us_dates.xlsx 2026-01-06 00:00:00 format: mm/dd/yyyySet datetime_format (or date_format for plain date objects) on ExcelWriter. It’s still a real date, so sorting works.
If the column arrived as text, convert the column to date