Fix "ValueError: Cannot convert to Excel" in openpyxl

Writing values cell by cell or row by row with openpyxl sometimes stops with:

ValueError: Cannot convert [1, 2] to Excel

— or Cannot convert {'a': 1} to Excel, or Cannot convert <NA> to Excel. openpyxl writes only values that Excel can store in a cell, and the value named in the message is not one of them. The fix is to decide how that value should appear in the workbook and convert it before writing. This guide, part of Troubleshooting Common Python Excel Errors, lists the types that fail, shows how to find them in a large dataset, and gives a conversion helper that handles them all.

Prerequisites

pip install openpyxl pandas

Step 1 — Reproduce and read the message

The message prints the offending value itself, which is usually enough to recognise where it came from:

import uuid

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
for value in [[1, 2], {"a": 1}, (1, 2), {1}, uuid.UUID(int=7)]:
    try:
        ws["A1"] = value
    except ValueError as exc:
        print(exc)

Lists and dicts usually come from JSON — an API response where one field holds an array or a nested object. UUIDs come from databases and ORMs. Sets come from deduplicating in Python. None of them has an Excel equivalent, so openpyxl cannot guess how you want them shown.

Step 2 — The pd.NA case: nullable columns

The most common trigger in pandas pipelines is quieter. Nullable dtypes — Int64, boolean, string — represent missing values with pd.NA, and openpyxl.utils.dataframe.dataframe_to_rows passes those through unchanged:

import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows

df = pd.DataFrame({
    "qty": pd.array([1, None], dtype="Int64"),
    "name": pd.array(["widget", None], dtype="string"),
})
wb = Workbook()
ws = wb.active
try:
    for row in dataframe_to_rows(df, index=False, header=True):
        ws.append(row)
except ValueError as exc:
    print(exc)                       # Cannot convert <NA> to Excel

Convert missing values to None before writing — the one "missing" value openpyxl understands — by casting to object first so pandas does not put NA back:

clean = df.astype(object).where(df.notna(), None)
wb = Workbook()
ws = wb.active
for row in dataframe_to_rows(clean, index=False, header=True):
    ws.append(row)
wb.save("nullable.xlsx")
print([c.value for c in ws[3]])      # [None, None]

pd.read_excel reads those empty cells back as missing values, so the round trip is clean. Missing data in reports generally is covered in Handling Missing Data in Excel Reports.

Step 3 — Find the offending values in a large dataset

In a frame with hundreds of thousands of rows, the error names a value but not a row or column. Scan the frame for anything openpyxl cannot write and report where it is before writing anything:

import datetime as dt
import decimal
import math

WRITABLE = (int, float, decimal.Decimal, str, bool, dt.date, dt.datetime, dt.time, dt.timedelta, type(None))

def unwritable(frame: pd.DataFrame) -> pd.DataFrame:
    hits = []
    for col in frame.columns:
        for idx, value in frame[col].items():
            if value is pd.NA or not isinstance(value, WRITABLE) and not hasattr(value, "item"):
                hits.append({"row": idx, "column": col, "type": type(value).__name__, "value": repr(value)[:40]})
    return pd.DataFrame(hits)

records = pd.DataFrame({
    "id": [uuid.UUID(int=1), uuid.UUID(int=2)],
    "tags": [["new", "vip"], []],
    "meta": [{"source": "web"}, None],
    "amount": [10.5, 7.0],
})
print(unwritable(records))

The hasattr(value, "item") test lets NumPy scalars through — openpyxl writes numpy.int64 and numpy.float64 fine — while flagging everything that would fail. The report points at columns, which is what you need: the fix is almost always per column, not per cell.

Step 4 — Convert each kind deliberately

Once you know which columns hold which types, choose a representation that serves the reader:

import json

def for_excel(frame: pd.DataFrame) -> pd.DataFrame:
    out = frame.copy()
    for col in out.columns:
        out[col] = out[col].map(lambda v: (
            None if v is pd.NA or (isinstance(v, float) and math.isnan(v)) else
            str(v) if isinstance(v, uuid.UUID) else
            ", ".join(map(str, v)) if isinstance(v, (list, tuple, set)) else
            json.dumps(v, ensure_ascii=False, sort_keys=True) if isinstance(v, dict) else
            v
        ))
    return out

ready = for_excel(records)
wb = Workbook()
ws = wb.active
for row in dataframe_to_rows(ready, index=False, header=True):
    ws.append(row)
wb.save("records.xlsx")
print(pd.read_excel("records.xlsx").iloc[0].tolist())

UUIDs become their standard text form, lists become a comma-separated string — readable and searchable with Excel's filter — and dicts become JSON text, which keeps the structure for anyone who needs to parse it later. Sets are joined too, but sort them first if the order should be stable between runs.

Step 5 — Split structures that people need to analyse

Joining a list into one cell is right for labels. When readers need to count or filter by the parts — orders with the "vip" tag, totals per tag — give each part its own row instead, with explode, or its own column with json_normalize for dicts:

tags = records[["id", "tags"]].explode("tags").dropna(subset=["tags"])
tags["id"] = tags["id"].astype(str)
meta = pd.json_normalize(records["meta"].apply(lambda m: m or {}).tolist()).add_prefix("meta_")
with pd.ExcelWriter("records_split.xlsx") as writer:
    tags.to_excel(writer, sheet_name="Tags", index=False)
    pd.concat([records[["amount"]], meta], axis=1).to_excel(writer, sheet_name="Details", index=False)
print(pd.read_excel("records_split.xlsx", sheet_name="Tags"))

The tags sheet has one row per tag, which a pivot table can count directly — something no amount of formatting can do for a joined string.

Step 6 — Make the error point at the cell

Code that writes rows from many sources — API pages, database cursors, several frames — benefits from a small wrapper that converts what it can and, when something still fails, says exactly where. The bare openpyxl message never includes the row or column; this one does:

def append_row(ws, values, row_label=None):
    converted = []
    for position, value in enumerate(values, start=1):
        if value is pd.NA:
            value = None
        elif isinstance(value, uuid.UUID):
            value = str(value)
        converted.append(value)
    try:
        ws.append(converted)
    except ValueError as exc:
        bad = next(i for i, v in enumerate(converted, start=1)
                   if not isinstance(v, WRITABLE) and not hasattr(v, "item"))
        raise ValueError(f"row {row_label or ws.max_row + 1}, column {bad}: {exc}") from exc

wb = Workbook()
ws = wb.active
append_row(ws, ["ok", uuid.UUID(int=5), pd.NA])
try:
    append_row(ws, ["order-17", 3, ["a", "b"]], row_label="order-17")
except ValueError as exc:
    print(exc)                       # row order-17, column 3: Cannot convert ['a', 'b'] to Excel

The wrapper deliberately converts only the unambiguous cases — missing values and identifiers — and lets lists and dicts fail loudly. Those need a decision about representation (Step 4 or Step 5), and silently joining them would hide a data change from whoever reads the report. Passing a business key such as an order number as row_label makes the message useful to someone who does not know the code.

Or let pandas do the writing

If you do not need openpyxl's cell-level control, DataFrame.to_excel avoids the error entirely: pandas converts unsupported objects to strings before passing them to the engine, and handles pd.NA itself. The trade-off is that you get Python's repr-style text — ['new', 'vip'] with brackets and quotes, dicts in Python syntax rather than JSON — so an explicit conversion like Step 4 still produces a cleaner sheet. For example, a list [1, 'x'] lands in the cell as the text [1, 'x'], and a dict as {'k': 1} — valid in Python, awkward for anyone reading the report. The writing options are compared in openpyxl vs xlsxwriter vs pandas.ExcelWriter.

Common pitfalls

• Converting cell by cell after the error. Wrap one conversion step around the whole frame, as in Step 4, rather than catching the exception per cell.
• Timezone-aware datetimes. They fail with a TypeError rather than this ValueError; the fix is different — see Fix "Excel does not support datetimes with timezones" in pandas.
• Very long strings. A JSON-serialised dict can exceed Excel's limit of 32,767 characters per cell; Excel truncates or complains. Store large payloads elsewhere and write a reference.
• Control characters in the text. Strings from APIs can contain characters Excel rejects, raising IllegalCharacterError — the next thing to fail after this one; see Fix IllegalCharacterError When Writing Excel with openpyxl.

Conclusion

"Cannot convert … to Excel" means a value has no cell representation: a list, tuple, set, dict, UUID or pd.NA. Scan the frame to find which columns hold such values, then convert each column deliberately — None for missing values, text for identifiers, joined strings for labels, exploded rows or normalised columns for structures people analyse, JSON for everything else — before writing with openpyxl.

Frequently asked questions

What types can openpyxl write to a cell? Numbers (int, float, Decimal), strings, booleans, dates, datetimes, times, timedeltas and None — plus formulas as strings starting with =. Anything else raises ValueError.

Why does pd.NA cause the error when NaN does not? openpyxl knows how to write None and float NaN, but pd.NA is a separate pandas object it does not recognise. Nullable columns such as Int64 and string use pd.NA for missing values, so convert them to None before writing.

Why does pandas to_excel work when openpyxl directly fails? pandas converts unsupported objects to their string form before handing them to the engine. Writing cells yourself with openpyxl skips that step, so you must convert the values.

Should I write a list as text or split it across cells? If people need to filter or sum the parts, split them into columns or rows. If the list is just a label — tags, codes — join it into one string with a clear separator.

Related

• Up one level: Troubleshooting Common Python Excel Errors — every common error explained.
• Fetch API Data into Excel with Python requests — where nested JSON usually comes from.
• openpyxl: Append Data to an Existing Excel Sheet — row writing with openpyxl.
• Check Excel Data Types with pandas — catching type surprises early.