How openpyxl Stores Python Values: Types, Dates and None

ws["B2"] = value looks like it simply puts a value in a cell, but underneath openpyxl decides three things: what kind of cell it is (number, text, boolean, date, formula), which number format it gets, and how it will be serialised into the file. Most of the time the decisions are right. A few are surprising, and a couple only surface later — when the file is reopened, or when save() fails far from the line that caused it. This page, part of Using openpyxl for Excel File Manipulation, maps every common Python value to what openpyxl does with it, as tested with openpyxl 3.1.

Prerequisites

pip install openpyxl numpy pandas

import datetime as dt
from decimal import Decimal

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

def roundtrip(value):
    wb = Workbook()
    ws = wb.active
    ws["A1"] = value
    cell = ws["A1"]
    written = (cell.data_type, cell.number_format)
    wb.save("roundtrip.xlsx")
    back = load_workbook("roundtrip.xlsx").active["A1"].value
    return written, type(back).__name__, back

roundtrip assigns a value, records the cell type and number format openpyxl chose, saves, reopens and returns what came back. Every claim below comes from running it.

Numbers, text and booleans

The plain types behave as you would expect:

for value in (42, 3.14, True, "hello", "00123"):
    print(repr(value), roundtrip(value))

data_type is 'n' for numbers, 's' for strings and 'b' for booleans, and all of them keep the General format. A string that looks like a number, such as "00123", stays text — openpyxl never converts types for you, which is what keeps leading zeros intact. Decimal values are accepted and written as numbers, but come back as float: the file stores numbers, not decimal types.

Dates and times carry their type in the format

Excel has no date type. A date is a serial number — days since 30 December 1899 — with a number format that says "show me as a date". openpyxl writes the number and picks the format from the Python type:

for value in (dt.datetime(2026, 3, 1, 14, 30), dt.date(2026, 3, 1), dt.time(9, 15),
              dt.timedelta(hours=26, minutes=30), pd.Timestamp("2026-03-01 08:00")):
    print(repr(value), roundtrip(value))

A datetime gets yyyy-mm-dd h:mm:ss, a date gets yyyy-mm-dd, a time gets h:mm:ss, and a timedelta gets [hh]:mm:ss, whose square brackets let hours run past 24. pandas Timestamp is a datetime subclass and is handled the same way.

Reading back is where the asymmetry appears: a date comes back as a datetime at midnight. openpyxl sees a date-formatted number and cannot know it started life as a date. Code that compares a value read from a file with a date object must convert first — value.date() — or the comparison is always false. Format Dates in Excel Cells with Python covers changing the display format.

Values that surprise: NumPy, NaN and strings with "="

Values from NumPy and pandas mostly work, because openpyxl registers NumPy's numeric types. One of them is handled differently from its Python equivalent:

for value in (np.int64(7), np.float64(2.5), np.bool_(True), float("nan")):
    print(repr(value), roundtrip(value))

np.int64 and np.float64 come back as plain int and float. np.bool_ does not become a boolean: openpyxl treats it as a number, so the cell holds 1 and reads back as the integer 1 — a flag column taken from a NumPy array turns into 0s and 1s. Convert with bool(value) before assigning. NaN produces an empty cell and reads back as None, the same as assigning None.

Strings that begin with = are stored as formulas, whatever follows:

print(roundtrip("= not a formula"))

wb = Workbook()
ws = wb.active
ws["A1"] = "= not a formula"
ws["A1"].data_type = "s"          # force text
wb.save("text.xlsx")
print(load_workbook("text.xlsx").active["A1"].data_type)
# s

The first version writes a formula that Excel cannot parse. Setting data_type = "s" after assigning keeps the value as text, which matters for any column holding user-entered notes; Write Clickable Links from a DataFrame Column to Excel discusses the security side of the same behaviour.

Values openpyxl rejects — now or later

Some values fail immediately with ValueError: Cannot convert ... to Excel. Others are accepted and fail only when the workbook is saved:

ws = Workbook().active
for value in ([1, 2], {"a": 1}, pd.NA, np.datetime64("2026-03-01")):
    try:
        ws["A1"] = value
        print("accepted", repr(value))
    except ValueError as exc:
        print("rejected", exc)

wb = Workbook()
wb.active["A1"] = dt.datetime(2026, 3, 1, 12, 0, tzinfo=dt.timezone.utc)
print("assignment accepted")
try:
    wb.save("tz.xlsx")
except TypeError as exc:
    print("save failed:", str(exc)[:48])

Lists, dictionaries, pd.NA and numpy.datetime64 are rejected on assignment, which is easy to debug. A timezone-aware datetime is the awkward one: the assignment succeeds and the TypeError appears at save(), possibly thousands of lines and many cells later, with nothing pointing at the cell responsible. Convert to naive local time before assigning — value.astimezone(zone).replace(tzinfo=None) — and see Handle Timezones in Excel Timestamps with Python for choosing the zone.

Normalise values before they reach openpyxl

The safest pattern for code that writes arbitrary data — rows from a database, values from a DataFrame — is one conversion function that every value passes through. It turns the surprising cases into the intended ones and makes the rejections explicit:

def to_cell(value, tz=None):
    if value is None or value is pd.NaT or (isinstance(value, float) and value != value):
        return None
    if value is pd.NA:
        return None
    if isinstance(value, np.bool_):
        return bool(value)
    if isinstance(value, np.datetime64):
        value = pd.Timestamp(value).to_pydatetime()
    if isinstance(value, dt.datetime) and value.tzinfo is not None:
        value = value.astimezone(tz).replace(tzinfo=None)
    if isinstance(value, (list, tuple, dict, set)):
        return str(value)
    return value

row = [np.bool_(True), pd.NA, np.datetime64("2026-03-01T08:00"),
       dt.datetime(2026, 3, 1, 12, 0, tzinfo=dt.timezone.utc), ["a", "b"], 42]
wb = Workbook()
wb.active.append([to_cell(v, tz=dt.timezone.utc) for v in row])
wb.save("normalised.xlsx")
print([c.value for c in load_workbook("normalised.xlsx").active[1]])

Every value now lands as something Excel understands: a real boolean, an empty cell, a naive datetime, text for the list. Converting the list to a string is a choice — raising an error instead would be equally reasonable for data that should never contain lists. Fix "ValueError: Cannot convert to Excel" in openpyxl covers the rejection cases in more depth.

Common pitfalls

• Comparing reread dates with date objects. They come back as datetimes; compare value.date().
• Writing NumPy booleans directly. They become 0 and 1.
• Assuming numeric-looking strings become numbers. They stay text; convert explicitly if they are quantities.
• Timezone-aware datetimes. Accepted now, fatal at save.
• Durations over a day. A timedelta of 26 hours displays as 26:30:00 in Excel because of the [hh] brackets in its default format; Excel documents that a format without them shows only the time of day, 02:30:00. LibreOffice happens to show 26:30:00 either way, so test duration formats in the application your readers use, and keep the brackets when you restyle duration columns.
• Strings starting with "=". Stored as formulas unless data_type is set to "s".

Performance notes

Type detection happens on every assignment and is fast; the conversion function above adds a few isinstance checks per value, which is negligible next to the cost of creating cells. When writing large DataFrames, let pandas' to_excel do the conversion — it already handles NaN, NaT and NumPy types — and use a function like to_cell only where your own code writes cells.

Conclusion

openpyxl stores numbers, text and booleans as you would expect, writes dates, times and durations as numbers with date formats — so dates come back as midnight datetimes — and turns any string beginning with = into a formula. numpy.bool_ becomes 0 or 1, pd.NA and numpy.datetime64 are rejected, and timezone-aware datetimes fail only at save(). Route values through one normalising function before they reach cells, and these become deliberate choices rather than surprises.

Frequently asked questions

Why does a date I wrote come back as a datetime? Excel has no separate date type: a date is a number with a date format. openpyxl writes a date with the yyyy-mm-dd format and reads every date-formatted number back as a datetime at midnight. Call .date() on it if you need a date.

Why did True from a NumPy array become 0? openpyxl recognises Python's bool but treats numpy.bool_ as a number, so the cell is stored as 0 or 1 rather than TRUE or FALSE. Convert with bool(value) before assigning.

Why does saving fail with "Excel does not support timezones"? A timezone-aware datetime was assigned to a cell. openpyxl accepts it at assignment but refuses at save time, because the file format has no timezone. Convert to naive local time before assigning.

How do I write text that starts with an equals sign? Assign it and then set cell.data_type = "s". Without that, openpyxl stores any string beginning with = as a formula.

Related

• Up one level: Using openpyxl for Excel File Manipulation — reading and writing cells.
• Read a Cell Value From Excel With openpyxl — the reading side in detail.
• Control How NaN, None and Infinity Appear in Excel Output — the same questions through pandas.
• Fix Long ID Numbers Losing Digits or Turning into 1.23E+18 in Excel — how large integers are written.