Fix

Getting Started With Python Excel AutomationDeep dive

Fix IllegalCharacterError When Writing Excel with openpyxl

Q: Can I keep the characters by encoding them?

xlsxwriter escapes them as _xHHHH_ sequences, which Excel understands. Most other readers show the escape literally, so cleaning is usually the better choice.

openpyxl refuses to write text containing control characters such as \x00 or a vertical tab. Find which cells hold them, decide what each character should become, and clean the data so the write succeeds.

Errors & fixes Writing Excel files openpyxl

A report job that ran for months fails one morning with openpyxl.utils.exceptions.IllegalCharacterError, followed by a customer name and the words cannot be used in worksheets. The name looks perfectly ordinary in the log. The problem is a character you cannot see: a vertical tab copied out of a Word document, a null byte from a legacy database field, an escape code from a terminal. The .xlsx format is XML, and XML forbids most control characters, so openpyxl refuses to write them rather than produce a file Excel would reject. This guide, part of Troubleshooting Common Python Excel Errors, finds and fixes them.

Prerequisites

Bash

pip install pandas openpyxl xlsxwriter

A frame with the two most common offenders — a vertical tab, which Word and some PDF extractors use as a line break, and a null byte from a fixed-width database export:

Python

import pandas as pd

customers = pd.DataFrame({
    "Customer": ["Acme\x0bLtd", "Globex", "Initech"],
    "Note": ["priority", "line one\x00", "call\x1bback"],
    "Amount": [1250.0, 980.0, 430.5],
})

Step 1 — Reproduce the error

to_excel with the openpyxl engine raises on the first bad cell it meets. The message includes the offending value, but because the character is invisible, it prints as if nothing were wrong.

Python

from openpyxl.utils.exceptions import IllegalCharacterError

try:
    customers.to_excel("customers.xlsx", index=False, engine="openpyxl")
except IllegalCharacterError as error:
    print("IllegalCharacterError:", repr(str(error)))
# IllegalCharacterError: 'Acme\x0bLtd cannot be used in worksheets.'

Using repr is the trick worth remembering: it shows \x0b where a normal print shows nothing. The error only reports the first failing cell, so fixing that one value and retrying tends to turn into a loop — find them all at once instead.

Step 2 — Find every offending cell

openpyxl exposes the exact pattern it checks as ILLEGAL_CHARACTERS_RE. Apply it to the text columns of the frame and list each row, column and the characters found, so the report is complete in one pass.

Python

from openpyxl.cell.cell import ILLEGAL_CHARACTERS_RE

def find_illegal(frame: pd.DataFrame) -> pd.DataFrame:
    found = []
    for column in frame.select_dtypes(include=["object", "string"]).columns:
        for index, value in frame[column].items():
            if isinstance(value, str):
                characters = sorted({f"\\x{ord(ch):02x}" for ch in ILLEGAL_CHARACTERS_RE.findall(value)})
                if characters:
                    found.append({"row": index, "column": column, "characters": " ".join(characters), "value": repr(value)})
    return pd.DataFrame(found)

print(find_illegal(customers))

The characters column is what drives the decision in the next step. A \x0b means the source intended a line break; a \x00 is padding or corruption; \x1b is usually the remains of terminal colour codes. Each deserves a different replacement.

Step 3 — Replace with meaning, then remove the rest

Deleting every illegal character works, but it glues words together: Acme\x0bLtd becomes AcmeLtd. Translate the characters that carry meaning first — vertical tab and form feed to a line break, which Excel displays in a wrapped cell — and only then strip whatever remains.

Python

MEANINGFUL = {"\x0b": "\n", "\x0c": "\n"}

def clean_text(value):
    if not isinstance(value, str):
        return value
    for bad, good in MEANINGFUL.items():
        value = value.replace(bad, good)
    return ILLEGAL_CHARACTERS_RE.sub("", value)

cleaned = customers.copy()
for column in cleaned.select_dtypes(include=["object", "string"]).columns:
    cleaned[column] = cleaned[column].map(clean_text)

print(cleaned.to_dict("list"))
cleaned.to_excel("customers.xlsx", index=False, engine="openpyxl")
print("written")

Terminal escape sequences leave debris after the escape character is removed — \x1b[31m becomes [31m. If your data comes from captured console output, strip whole ANSI sequences with re.sub(r"\x1b\[[0-9;]*m", "", value) before the general cleanup.

Step 4 — Clean a whole DataFrame in one call

For large frames, a regex replacement across all columns is shorter and faster than mapping a Python function over every cell. DataFrame.replace with regex=True touches only string values, leaving numbers and dates alone.

Python

fast = (
    customers.replace({"\x0b": "\n", "\x0c": "\n"}, regex=True)
             .replace(ILLEGAL_CHARACTERS_RE, "", regex=True)
)
print(fast.equals(cleaned))                 # True
fast.to_excel("customers_fast.xlsx", index=False)

Both calls need regex=True. With regex=False, DataFrame.replace only swaps values that are entirely equal to the key, so a cell containing Acme\x0bLtd would be left untouched — the kind of no-op that makes a fix look applied when it is not.

Put this line immediately before every to_excel in jobs that handle free text from outside sources — CRM notes, scraped pages, form submissions. It costs little and makes the write immune to a whole category of failures. The broader text clean-up, including non-breaking spaces and inconsistent whitespace, is covered in Strip Whitespace and Normalise Text Columns with pandas.

Step 5 — Know what xlsxwriter does instead

The xlsxwriter engine does not raise. It escapes forbidden characters using the _xHHHH_ notation defined by the file format, so the write succeeds — but the character is still in the data.

Python

from openpyxl import load_workbook

customers.to_excel("customers_xlsxwriter.xlsx", index=False, engine="xlsxwriter")
print(repr(load_workbook("customers_xlsxwriter.xlsx").active["A2"].value))
# 'Acme_x000B_Ltd'

Excel interprets the escape when it opens the file, but other tools that read the workbook, openpyxl included, return the literal _x000B_ text. A job that switches engines to make the error go away has moved the problem downstream, where a lookup on Acme Ltd silently fails. Clean the data regardless of which engine writes it.

Step 6 — Wrap the write in a safe helper

In a reporting codebase the cleanup should not depend on every author remembering it. A small helper that cleans, counts what it changed, writes, and wraps line-broken cells in the output keeps the behaviour consistent across jobs and leaves a trace in the log when a source starts sending control characters.

Python

import logging
from openpyxl.styles import Alignment

logging.basicConfig(level=logging.INFO, format="%(levelname)s %(message)s")
log = logging.getLogger("excel-writer")

def safe_to_excel(frame: pd.DataFrame, path: str, sheet_name: str = "Sheet1") -> None:
    text_columns = frame.select_dtypes(include=["object", "string"]).columns
    cleaned_frame = frame.copy()
    changed = 0
    for column in text_columns:
        before = cleaned_frame[column]
        after = before.map(clean_text)
        changed += int((before.fillna("") != after.fillna("")).sum())
        cleaned_frame[column] = after
    if changed:
        log.warning("removed or translated control characters in %d cell(s) before writing %s", changed, path)

    with pd.ExcelWriter(path, engine="openpyxl") as writer:
        cleaned_frame.to_excel(writer, sheet_name=sheet_name, index=False)
        sheet = writer.sheets[sheet_name]
        for row in sheet.iter_rows(min_row=2):
            for cell in row:
                if isinstance(cell.value, str) and "\n" in cell.value:
                    cell.alignment = Alignment(wrap_text=True, vertical="top")

safe_to_excel(customers, "customers_safe.xlsx", sheet_name="Customers")
print(repr(load_workbook("customers_safe.xlsx")["Customers"]["A2"].value))

The warning is deliberately a log line rather than an error. The report still goes out, correctly, and whoever watches the logs learns that a source system has started including control characters — often the first sign of a change upstream, such as a new export format or a different text field being copied in.

Step 7 — Keep a control character in your test data

This error tends to appear first in production because test fixtures are typed by hand and never contain invisible characters. Add one deliberately, so a refactor that drops the cleanup fails a test instead of a scheduled job.

Python

def test_writer_handles_control_characters(tmp_path="."):
    frame = pd.DataFrame({"Name": ["Acme\x0bLtd", "Null\x00Byte"], "Value": [1, 2]})
    target = f"{tmp_path}/control_chars.xlsx"
    safe_to_excel(frame, target)
    values = [cell.value for cell in load_workbook(target).active["A"][1:]]
    assert values == ["Acme\nLtd", "NullByte"]

test_writer_handles_control_characters()
print("control character test passed")

The pattern generalises to other invisible-input bugs — non-breaking spaces, zero-width joiners, byte-order marks — and belongs alongside the checks in Test Excel Output with pytest.

Common pitfalls

Symptom	Cause	Fix
`IllegalCharacterError` naming a normal-looking value	Invisible control character	Inspect with `repr()`; clean with `ILLEGAL_CHARACTERS_RE`
Words run together after cleaning	Vertical tab deleted instead of translated	Replace `\x0b` and `\x0c` with `\n` first
Line breaks not visible in Excel	Cell not set to wrap text	Apply `Alignment(wrap_text=True)` to those cells
`_x000B_` appears in text read back	File written by xlsxwriter with dirty data	Clean before writing with any engine
`[31m` fragments remain	ANSI escape sequences partly removed	Strip whole `\x1b[...m` sequences first
Error appears only in production	Test data never contained control characters	Add a control character to a test fixture

Performance and scale notes

DataFrame.replace with a compiled pattern is vectorised per column and handles hundreds of thousands of text cells in well under a second. The per-cell map in Step 3 is easier to extend with special cases and fine for report-sized data. Scanning for offenders (Step 2) is a diagnostic; in a production job, clean unconditionally and log a count of changed cells, which avoids scanning twice.

Conclusion

IllegalCharacterError means a string contains a control character that XML, and therefore the .xlsx format, does not allow. Use repr() to see it, ILLEGAL_CHARACTERS_RE to find every affected cell at once, translate vertical tabs and form feeds into line breaks, and strip what remains before writing. Do this whatever the engine — xlsxwriter's escaping lets the write succeed but carries the problem into the file — and include a control character in your test data so the protection stays in place.

Frequently asked questions

Which characters does openpyxl reject? ASCII control characters 0–8, 11–12 and 14–31. Tab, line feed and carriage return are allowed, as is all normal text including accented letters and emoji.

Is IllegalCharacterError a bug in openpyxl? No. The xlsx format is XML, and XML 1.0 does not permit those control characters in text. openpyxl raises instead of writing a file Excel would report as corrupt.

Can I keep the characters by encoding them? xlsxwriter escapes them as xHHHH sequences, which Excel understands. Most other readers show the escape literally, so cleaning is usually the better choice.

Where do these characters usually come from? Copying from Word or PDFs (vertical tabs), fixed-width or binary database fields (null bytes), and captured console output (escape codes).

Up one level: Troubleshooting Common Python Excel Errors — the most common failures and their fixes.
Fix "Excel Found Unreadable Content" After Writing with Python — when a file is written but Excel repairs it.
Fix "Number Stored as Text" Warnings in Excel Output — another data-type problem visible only after writing.
Validate an Excel Report Before Sending It — catching defects before a report goes out.
Styling Excel Cells with openpyxl — wrap text so translated line breaks display.

More on errors & fixes