Python Excel AutomationPractical guides for developers
How-to
Getting Started With Python Excel AutomationDeep dive

How pandas Data Types Are Written to Excel Cells

What to_excel actually stores for each pandas dtype — integers, nullable types, floats, booleans, text, datetime64, date objects, timedeltas, categoricals, Decimal, infinity, Period, lists and timezone-aware timestamps — checked cell by cell with openpyxl, plus the conversions to apply before writing so the workbook holds what you meant.
Writing Excel filespandas

DataFrame.to_excel looks like a straight copy from DataFrame to worksheet, but Excel only has four kinds of cell value — number, text, boolean and error — plus number formats that make some numbers look like dates. Every pandas type has to be squeezed into one of those, and some of the choices pandas makes are surprising: durations become day counts that display as whole numbers, infinity becomes the text inf, a 17-digit integer is silently rounded, and a timezone-aware column refuses to write at all. Knowing the mapping lets you convert columns before writing, so the workbook holds what you meant rather than what was convenient. Everything here was checked by writing each type with both engines and reading the cells back with openpyxl. It belongs to Writing DataFrames to Excel with pandas.

Prerequisites

Bash
pip install pandas openpyxl xlsxwriter

The results were the same for engine="xlsxwriter" (the default when it is installed) and engine="openpyxl" on pandas 3.0, openpyxl 3.1 and XlsxWriter 3.2.

Step 1 — Write one column of every type

Python
import datetime as dt
import decimal
import numpy as np
import pandas as pd

samples = {
    "int64": pd.Series([1, 2], dtype="int64"),
    "Int64 with NA": pd.Series([1, None], dtype="Int64"),
    "float with NaN": [1.5, np.nan],
    "bool": [True, False],
    "str": ["a", None],
    "datetime64": pd.to_datetime(["2026-01-05 13:30", "2026-02-01 00:00"]),
    "date objects": [dt.date(2026, 1, 5), dt.date(2026, 2, 1)],
    "timedelta": pd.to_timedelta(["1 days 06:00:00", "0 days 00:30:00"]),
    "category": pd.Categorical(["x", "y"]),
    "Decimal": [decimal.Decimal("1.10"), decimal.Decimal("2.005")],
    "inf": [np.inf, -np.inf],
    "Period": pd.period_range("2026-01", periods=2, freq="M"),
    "list": [[1, 2], [3]],
    "17-digit int": pd.Series([12345678901234567, 2], dtype="int64"),
}
frame = pd.DataFrame(samples)
frame.to_excel("types.xlsx", index=False)

Step 2 — Read back what each cell holds

Python
from openpyxl import load_workbook

ws = load_workbook("types.xlsx").active
print(f"{'column':15} {'stored value':28} {'type':4} number format")
for col in ws.iter_cols(min_row=1, max_row=3):
    header, first, second = col
    print(f"{header.value:15} {first.value!r:28} {first.data_type:4} {first.number_format}   next: {second.value!r}")

openpyxl's data_type is n for numbers (dates included — a date is a number with a date format), s for text, b for booleans and d when it recognises a date format on a number. Here is what came back:

What to_excel stores for each pandas type A table with one row per pandas type and three columns showing the stored cell kind, the number format, and the surprise to watch for. Integers, floats and booleans map cleanly; datetime64 gets a date-time format; timedelta becomes a day count with format 0; inf, Period and lists become text; a 17-digit integer is rounded. stored as number format watch for int64 / Int64 number General NA becomes an empty cell float64 number General NaN becomes an empty cell bool / boolean TRUE / FALSE General none str / category text General category writes its labels datetime64 number YYYY-MM-DD HH:MM:SS time shown even at midnight date objects number YYYY-MM-DD none timedelta days as number 0 1 d 6 h displays as 1 Decimal number General converted to float inf / -inf text General the text inf, not a number Period, list text General str() of the object 17-digit int number General rounded to 15 digits

Integers, floats, booleans and plain text behave exactly as you would hope, and missing values (NaN, <NA>, None) become empty cells by default. The rest of this page goes through the types that need attention.

Step 3 — Dates and datetimes

A datetime64 column is written as an Excel date serial with the format YYYY-MM-DD HH:MM:SS, so even values at midnight display 00:00:00. A column of Python date objects (object dtype) gets YYYY-MM-DD. Choose the display yourself through the writer:

Python
with pd.ExcelWriter("dates.xlsx", engine="xlsxwriter",
                    datetime_format="dd mmm yyyy hh:mm", date_format="dd mmm yyyy") as xw:
    frame[["datetime64", "date objects"]].to_excel(xw, index=False)
ws = load_workbook("dates.xlsx").active
print(ws["A2"].number_format, "|", ws["B2"].number_format)

datetime_format applies to datetime columns and date_format only to date objects. Both are honoured by the xlsxwriter engine; the openpyxl engine ignores datetime_format, so either use xlsxwriter or set cell.number_format afterwards. To drop the time part entirely, convert with .dt.date before writing, as described in Write Dates Without Time from pandas to Excel. Timezone-aware columns are rejected with ValueError: Excel does not support datetimes with timezones; convert to the zone the reader expects and remove the zone with .dt.tz_convert("Europe/Berlin").dt.tz_localize(None).

Step 4 — Durations

A timedelta is stored as a fraction of days — 1 day 6 hours becomes 1.25, 30 minutes becomes 0.0208 — with the number format 0, which rounds the display to a whole number. Excel therefore shows 1 and 0. The value is right; the format hides it. Give the column a duration format:

Python
with pd.ExcelWriter("durations.xlsx", engine="xlsxwriter") as xw:
    frame[["timedelta"]].to_excel(xw, index=False)
    hours = xw.book.add_format({"num_format": "[h]:mm"})
    for row in range(1, 3):
        xw.sheets["Sheet1"].write_number(row, 0, frame["timedelta"][row - 1] / pd.Timedelta(days=1), hours)
print(load_workbook("durations.xlsx").active["A2"].number_format)

[h]:mm shows 30:00 for 1.25 days; the square brackets stop hours wrapping at 24. Rewriting the cells is needed because pandas' per-cell format overrides a column format set with set_column. If the reader wants plain numbers instead, write hours as floats: frame["timedelta"].dt.total_seconds() / 3600. More formats are in Format Durations Over 24 Hours in Excel.

A timedelta is written as days with a format that hides the fraction The pandas column holds 1 day 6 hours and 30 minutes. The workbook stores 1.25 and 0.0208 with number format 0, so Excel displays 1 and 0. With the format [h]:mm the same values display 30:00 and 0:30. timedelta 1 days 06:00:00 0 days 00:30:00 A B C 1 stored format 0 [h]:mm 2 1.25 1 30:00 3 0.0208 0 0:30 pandas timedelta cell value / shown with "0" / shown with [h]:mm to_excel

Step 5 — Numbers that do not survive

Excel stores every number as a 64-bit float, which holds about 15–16 significant digits. A 17-digit integer such as an order or card-like ID is written rounded — 12345678901234567 came back as 1.234567890123457e+16 — and nothing on the Excel side can restore it. Decimal values are converted to float on the way in, so 2.005 is stored as the nearest binary float, which is fine for display but not for exact accounting comparisons. Infinity is written as the text inf (controlled by inf_rep), so a numeric column suddenly contains strings that break SUM. Fix all three before writing:

Python
fixed = frame.assign(**{
    "17-digit int": frame["17-digit int"].astype(str),
    "Decimal": frame["Decimal"].map(lambda d: float(round(d, 2))),
    "inf": frame["inf"].replace([np.inf, -np.inf], np.nan),
})
fixed[["17-digit int", "Decimal", "inf"]].to_excel("numbers.xlsx", index=False)
for row in load_workbook("numbers.xlsx").active.iter_rows(min_row=2, values_only=True):
    print(row)

Long identifiers are written as text so every digit survives; decimals are rounded to the precision the report needs; infinities become empty cells (or pass inf_rep="#N/A" if you prefer a visible marker). See Fix Long ID Numbers Losing Digits in Excel and Control How NaN and Infinity Appear in Excel Output.

Step 6 — Objects that turn into text

Anything pandas cannot map to a number, boolean or date is written with str(): a Period becomes "2026-01", a list becomes "[1, 2]", a dict its repr. Categoricals write their labels, which is usually what you want. Convert periods to real dates so Excel can sort and chart them, and join lists into readable text:

Python
tidy = pd.DataFrame({
    "Month": frame["Period"].dt.to_timestamp(),
    "Items": frame["list"].map(lambda xs: ", ".join(map(str, xs))),
})
with pd.ExcelWriter("tidy.xlsx", engine="xlsxwriter", datetime_format="mmm yyyy") as xw:
    tidy.to_excel(xw, index=False)
ws = load_workbook("tidy.xlsx").active
print(ws["A2"].value, ws["A2"].number_format, "|", ws["B2"].value)
A pre-write pass that makes the workbook hold what you meant Four conversions applied before to_excel. Timezone-aware datetimes are converted and made naive, durations become hours or get a duration format, long identifiers become text, and Period and list objects become dates and joined strings. Datetimes tz_convert, then tz_localize(None) Durations hours as float, or [h]:mm Long IDs astype(str) Objects to_timestamp, join lists to_excel cells hold real values

Step 7 — Text that changes meaning on the way in

Plain strings are the safest type, with two exceptions worth testing for. A string that starts with = is written as a formula by both engines — "=1+1" arrives as a formula cell, not as text — which is how formula injection gets into exported files. And the xlsxwriter engine turns strings that look like URLs into clickable hyperlinks, while the openpyxl engine leaves them as text:

Python
risky = pd.DataFrame({"s": ["=1+1", "00123", "http://example.org", "1e5"]})
for engine in ("xlsxwriter", "openpyxl"):
    risky.to_excel("risky.xlsx", index=False, engine=engine)
    cells = load_workbook("risky.xlsx").active["A"][1:]
    print(engine, [(c.value, c.data_type) for c in cells], "link:", cells[2].hyperlink is not None)

Both engines store "=1+1" with data type f. Numeric-looking text such as "00123" and "1e5" stays text, so leading zeros survive. Neutralise user-supplied values that begin with =, +, - or @ before writing, as described in Prevent Formula Injection in Excel Exports.

Common pitfalls

  • Trusting the display. A timedelta cell showing 1 holds 1.25; check the number format before concluding the data is wrong.
  • Setting a column format after to_excel. pandas writes a format on each datetime and timedelta cell, which wins over set_column; pass formats to ExcelWriter or rewrite the cells.
  • IDs as numbers. Anything longer than 15 digits, or with leading zeros, must be text before writing.
  • Infinity in calculated columns. Ratios with a zero denominator produce inf, which is written as text and breaks totals.
  • Assuming the engines differ. For values they behave the same; the difference is which writer options they honour (datetime_format only works with xlsxwriter).

Performance notes

The conversions above are vectorised and cost far less than writing the file. Avoid per-row .map on large frames where a vectorised alternative exists — dt.total_seconds() instead of a lambda, astype(str) instead of formatting each value. Object columns of lists or dicts slow to_excel because each value is converted with str() individually; convert them once beforehand.

Conclusion

Excel cells hold numbers, text, booleans and errors; pandas maps integers, floats, booleans and strings cleanly, writes datetimes as date serials with a full date-time format, durations as day fractions formatted to hide the fraction, infinity as text, Decimal as float, and unknown objects with str(). Long integers lose digits and timezone-aware datetimes fail. A short pre-write pass — formats on the writer, hours or [h]:mm for durations, text for long IDs, NaN for infinity, real dates for periods — makes the workbook hold exactly what the DataFrame meant.

Frequently asked questions

Why does my timedelta column show whole numbers in Excel? pandas writes timedeltas as fractions of a day with the number format 0, which rounds the display. The cell holds 1.25 for 1 day 6 hours; apply a format such as h:mm, or write hours as floats instead.

Why is infinity written as "inf" text in Excel? Excel has no infinity value, so to_excel writes the string given by inf_rep, which defaults to "inf". Replace infinities with NaN before writing, or pass inf_rep to choose another marker.

Does to_excel keep Decimal precision? No. Decimal values are converted to Python floats, so they are stored as the nearest binary floating-point number. Round them to the precision you need, or write them as text if exact digits matter.

How do I write timezone-aware datetimes to Excel? Convert them to the time zone the reader expects with dt.tz_convert, then remove the zone with dt.tz_localize(None). Excel has no time zone support, so pandas raises ValueError otherwise.

More on writing excel files

More on writing excel files →