Control How NaN, None and Infinity Appear in Excel Output

A DataFrame almost always has holes: a customer with no region, an order not yet shipped, a growth rate divided by a zero baseline. When it goes to Excel, each of those holes has to become something in a cell, and the choice affects every formula a reader writes on top of the sheet. pandas makes sensible defaults for missing values and a surprising one for infinity. This page, part of Writing DataFrames to Excel with Pandas, shows what gets written, what Excel does with it, and how to control it per column.

Prerequisites

pip install pandas openpyxl

import numpy as np
import pandas as pd
from openpyxl import load_workbook

frame = pd.DataFrame({
    "region": ["North", None, "East", "West"],
    "units": pd.array([12, None, 7, 9], dtype="Int64"),
    "growth": [0.10, np.nan, np.inf, -np.inf],
    "shipped": pd.to_datetime(["2026-03-01", None, "2026-03-03", "2026-03-04"]),
})

def cells(path):
    ws = load_workbook(path).active
    return [[c.value for c in row] for row in ws.iter_rows(min_row=2)]

Step 1 — See the defaults

frame.to_excel("defaults.xlsx", index=False)
for row in cells("defaults.xlsx"):
    print(row)

Every missing value — None in the text column, <NA> in the nullable integer column, NaN in the float column, NaT in the dates — is written as an empty cell. That is the right default for a spreadsheet: Excel's SUM, AVERAGE and COUNT skip empty cells, just as pandas' aggregations skip missing values, so a reader's formulas agree with your numbers.

The infinities are the exception. Excel has no infinity, so pandas writes the text "inf" and "-inf". That turns one cell in a numeric column into a string, and Excel treats it very differently from a blank.

Step 2 — Understand what Excel does with blanks and "inf"

The consequences show up in formulas. Recalculating a sheet with a blank and an "inf" in a column of growth rates (0.1, "inf", blank, 0.3) gives:

Two lessons. First, "inf" quietly disappears from totals and averages, so a report can look complete while a row's value is excluded — and any formula that uses the cell directly breaks. Second, a blank in direct arithmetic counts as zero. That is usually harmless, but a reader who computes =B4*C4 for a row with a missing price gets 0, not an error. Neither is a reason to avoid blanks; both are reasons to know exactly which cells are blank.

Step 3 — Replace infinities before writing

Infinity in a report almost always comes from dividing by zero — growth against a zero baseline, a rate per zero hours. Decide what it means and write that instead. Usually the honest answer is "not defined", which is a blank:

sales = pd.DataFrame({"region": ["North", "South", "East"], "last_year": [100, 0, 80], "this_year": [120, 45, 80]})
sales["growth"] = sales["this_year"] / sales["last_year"] - 1
print(sales["growth"].tolist())
# [0.19999999999999996, inf, 0.0]

sales["growth"] = sales["growth"].replace([np.inf, -np.inf], np.nan)
sales.to_excel("growth.xlsx", index=False)
print(cells("growth.xlsx"))

South now has an empty growth cell rather than "inf". If readers need to know why it is empty, add a note column or a comment rather than putting text in a number column. Add Comments and Notes to Excel Cells with Python shows how.

inf_rep changes the text written for infinity (inf_rep="∞"), but it is still text, with every problem from Step 2. Treat it as a display choice for sheets nobody calculates with.

Step 4 — Use placeholders per column, not with na_rep

na_rep replaces every missing value in every column with one string:

frame.to_excel("na_rep.xlsx", index=False, na_rep="n/a")
print(cells("na_rep.xlsx")[1])
# ['n/a', 'n/a', 'n/a', 'n/a']

In the text column that is exactly what a reader wants. In units, growth and shipped it puts text into numeric and date columns, so sums skip the row, arithmetic returns #VALUE!, and the date column no longer sorts cleanly. Fill placeholders in the columns where they belong and leave the rest blank:

out = frame.copy()
out["region"] = out["region"].fillna("Unassigned")
out["growth"] = out["growth"].replace([np.inf, -np.inf], np.nan)
out.to_excel("per_column.xlsx", index=False)
for row in cells("per_column.xlsx"):
    print(row)

For numeric columns where a reader must see that a value is missing, use formatting rather than text: a light fill on blank cells, applied with conditional formatting, keeps the column numeric. Highlight Blank Cells in Excel with openpyxl builds that rule.

Blank cells are easy to overlook, so a report with gaps should say how many there are. A small summary sheet next to the data does that without touching the data itself:

clean = frame.copy()
clean["growth"] = clean["growth"].replace([np.inf, -np.inf], np.nan)
gaps = clean.isna().sum().rename("missing").to_frame()
gaps["share"] = (gaps["missing"] / len(clean)).round(3)

with pd.ExcelWriter("with_gaps.xlsx") as writer:
    clean.to_excel(writer, sheet_name="Data", index=False)
    gaps.to_excel(writer, sheet_name="Missing values", index_label="column")
print(gaps)

Readers see at a glance that three of the four growth values are missing — one original gap and the two infinities replaced with NaN in the second line — and the count is computed from the same DataFrame that was written, so it cannot drift from the data. For a fuller treatment, Find and Report Missing Values in an Excel File builds a report per column and per row.

Step 5 — Writing with openpyxl or xlsxwriter directly

pandas converts missing values before handing cells to the engine. Code that writes cells itself gets the libraries' own behaviour, which differs:

from openpyxl import Workbook
import xlsxwriter

wb = Workbook()
ws = wb.active
ws["A1"] = None          # no cell content — the normal empty cell
ws["A2"] = float("nan")  # written as a number cell with an empty value
wb.save("direct_openpyxl.xlsx")

book = xlsxwriter.Workbook("direct_xlsxwriter.xlsx")
sheet = book.add_worksheet()
try:
    sheet.write_number(0, 0, float("nan"))
except TypeError as exc:
    print(exc)
book.close()

openpyxl accepts NaN and writes a number cell with no value in it, which is not the same as an ordinary empty cell. xlsxwriter refuses with a TypeError unless the workbook is created with {"nan_inf_to_errors": True}, in which case it writes Excel error values — #NUM! for NaN and #DIV/0! for infinity. Error values are honest but they propagate through every formula that touches them. In both libraries, the clean approach is the same as in pandas: convert NaN to None before writing, and decide explicitly what infinity should become.

Step 6 — Read the file back with the right types

Blank cells come back as NaN, which changes some types on the way in:

back = pd.read_excel("per_column.xlsx")
print(back.dtypes.to_dict())

back = pd.read_excel("per_column.xlsx", dtype={"units": "Int64"})
print(back.dtypes.to_dict())

The units column was Int64 when written and comes back as float64, because a NumPy integer column cannot hold a missing value. Asking for "Int64" in dtype restores the nullable integer. "inf" text, if any is left in a file, is converted back to a float infinity by pandas' type inference — so a round trip hides the problem that Excel users see.

Common pitfalls

• na_rep on a whole report. It turns every numeric column with a gap into mixed text. Fill per column.
• Leaving inf in. It becomes text that totals ignore silently and arithmetic rejects.
• Writing 0 for missing. It changes averages and lies about the data. Blank is the neutral choice.
• Forgetting dates. NaT becomes a blank, which is right; na_rep would turn it into text in a date column.

Performance notes

replace([np.inf, -np.inf], np.nan) and fillna are vectorised and cheap even on millions of rows. The costly mistake is doing replacements cell by cell with openpyxl after writing; clean the DataFrame first and let pandas write the final values in one pass.

Conclusion

pandas writes NaN, None, pd.NA and NaT as empty cells, which Excel's aggregates skip just as pandas does, and infinity as the text "inf", which totals ignore and arithmetic rejects. Replace infinities with NaN (or a meaningful value) before writing, fill text placeholders per column rather than with na_rep, show missing numbers with formatting instead of text, and read files back with nullable dtypes so the round trip keeps its types.

Frequently asked questions

What does to_excel write for NaN? An empty cell, by default. The same happens for None, pd.NA and NaT, in every column type. Empty cells are ignored by Excel's SUM, AVERAGE and COUNT, which matches how pandas skips missing values.

Why do I see "inf" in my spreadsheet? Infinity has no Excel equivalent, so pandas writes it as text using inf_rep, which defaults to "inf". It usually comes from dividing by zero. Replace infinities with NaN, or with a value that means something to readers, before writing.

Is na_rep a good way to show "n/a" for missing values? Only for text columns. na_rep applies to every missing value in every column, so numeric and date columns end up mixing numbers with text, which breaks arithmetic in Excel. Fill placeholders per column instead.

Why did my integer column come back as floats? A blank cell reads back as NaN, and a NumPy integer column cannot hold NaN, so pandas uses float64. Pass dtype={"col": "Int64"} to read_excel to get the nullable integer type back.

Related

• Up one level: Writing DataFrames to Excel with Pandas — every to_excel option.
• Handling Missing Data in Excel Reports with Pandas — finding and filling gaps before they reach the report.
• Fill Missing Values in Excel with Pandas fillna — choosing fill values.
• Write Dates Without the Time Part from pandas to Excel — the other common surprise in date columns.