Understanding Excel Errors When Data Comes From Code

Excel is forgiving when a person uses it and strict when a program does. A person who types a bad formula sees an error code and fixes the cell. A program that writes ten thousand cells with the same mistake produces a file Excel refuses to open, or opens with a repair dialog, or fills with error codes that nobody reads until a total looks wrong. And when the program reads a workbook, the failures are exceptions from libraries that say a zip was bad or an engine was unsupported, with no hint of the real cause.

This guide is a map of those errors. It groups them by where they appear, gives the first checks for each group, and links to the detailed posts. It also covers the habits that stop most of them: choosing engines by content, fixing generators rather than outputs, and testing generated files by opening them. The worked examples assume Python, since that is where most workbook generation and reading happens, but the causes apply to any language.

Four places an error can appear

When something goes wrong, the first useful question is where. There are four places, and each has its own set of causes.

A fifth place hides among the others: the numbers are wrong with no error at all. A column of text numbers sums to zero, a date shifts by a month, a total misses rows. Those are silent failures, and the section on silent errors below treats them separately because they cause the most harm.

Group one: the file will not open

Start with what the file is. Do not trust the extension; read the first bytes.

def sniff(path):
    with open(path, "rb") as f:
        head = f.read(8)
    if head.startswith(b"PK\x03\x04"):
        return "zip: xlsx or similar"
    if head.startswith(b"\xd0\xcf\x11\xe0"):
        return "compound file: xls or encrypted workbook"
    if head.lstrip().lower().startswith((b"<html", b"<!doc", b"<?xml")):
        return "markup"
    return f"other: {head!r}"

A PK start is a zip, which is what an xlsx should be. D0 CF 11 E0 is an old binary xls or an encrypted workbook. Anything that looks like markup or plain text is a mislabeled export. The most common cause of "file format or file extension is not valid" is a script that wrote HTML or CSV and named it .xlsx (why Excel says the file format or extension is not valid).

If the first bytes say zip, test the archive with unzip -t file.xlsx. A truncated or damaged zip fails there, and the remedy is restoring from a backup or extracting what can be read (Excel cannot open the file because the file is corrupt). If the zip is intact, the file has the right container and the problem lies in the XML parts, which is group two.

Never recover or inspect a file you do not trust on a machine with sensitive data. Documents from unknown sources can be malicious, and a corrupt file is as good a disguise as any.

Group two: the file opens after a repair

The prompt "We found a problem with some content" means the zip is fine and Excel found a part of the XML that breaks its rules. After recovery Excel shows a log naming what it removed. The part path in the log, such as /xl/worksheets/sheet3.xml, points at the sheet.

The causes from generated workbooks fall in a short list: control characters in strings, text over 32,767 characters, sheet names that are too long or contain forbidden characters, duplicate or empty table headers, NaN and infinity values, dates outside the supported range, timezone-aware datetimes, overlapping merged ranges, hyperlinks that are too long, and too many unique styles. Each can be tested for before writing, and the generator should test for all of them once (fixing Excel found unreadable content after a script export).

Long strings deserve a mention of their own, because they cause damage in several tools in different ways: some truncate, some write an invalid cell, and a CSV import shifts rows (Excel cell contains more than 32767 characters). If a free-text column is in the data, clamp or move it.

When the log is unclear, bisect: write half of the rows and open the file, then the other half, and continue on the one that fails. A script can do the halving; the only reliable validator of what Excel will accept is Excel itself, or Microsoft's Open XML SDK validator in a build step.

Group three: a cell shows an error code

Cell errors are produced by formulas. If your program wrote values and no formulas, you will rarely see them, except in data copied from another workbook. If it wrote formulas, the error is evidence about the formula or its inputs.

The evidence is the stored formula, not the displayed error. Read it back with openpyxl in formula mode and compare it with what the generator meant to write (Excel REF NAME and VALUE errors explained).

Two cases bite generated formulas in particular. A newer function written without its _xlfn. prefix shows #NAME? until the cell is re-entered, and a formula copied from a localized Excel contains semicolons and translated function names that the file format does not accept. Write the English names and commas, whatever the user's language.

A formula that displays as its own source is a different matter: the cell is formatted as Text, Show Formulas is on, or the program wrote a string where it meant a formula (Excel formula shows as text instead of calculating). And a warning about a circular reference means a formula depends on itself, often a total that includes its own cell, which generators produce through off-by-one errors (fixing circular reference warnings in imported sheets).

Libraries do not calculate formulas. A workbook written by openpyxl or XlsxWriter holds formula text and no cached results until Excel opens and saves it. A reader that wants values sees empty cells. If a downstream tool needs values, write values, or let a real spreadsheet application recalculate the file once before handing it on (handling Excel formulas and cached values).

Group four: a library raises an exception

When the program reads a workbook, the exception is the clue, and most of them mean the engine and the content disagree.

The remedy for the first five is to select the engine from the content, not from the extension, and to say so in the code. A reader that sniffs the first bytes and picks openpyxl or xlrd accordingly avoids a whole family of environment-dependent failures. It helps to print the interpreter path and package versions when a job fails on one machine and passes on another: the difference is usually in the environment, not the code.

Missing dependencies are the quietest cause of "works on my machine". pandas treats Excel engines as optional, so a clean environment lacks openpyxl until someone adds it. Put the engines in the project's requirements file and not in someone's memory (fixing missing optional dependency openpyxl).

Silent errors: no message, wrong numbers

The worst failures give no error. The program completes, the workbook opens, and the numbers are wrong. The usual suspects:

• Text numbers. A column of figures stored as text is skipped by SUM and counted by pivots (fixing numbers stored as text in Excel).

• Dates. A serial number read as a plain number, a day and month swapped by locale, or a date system shifted by four years on files from a Mac.

• Leading zeros. An identifier converted to a number loses its zeros (keeping leading zeros in Excel).

• Precision. A long identifier stored as a number is rounded beyond 15 digits.

• Hidden rows and sheets. A reader that sees them while Excel's totals skip them gives different answers.

• Merged cells. Values only in the top left cell, so a join or group sees blanks.

• Truncation. A sheet that ends at the row limit, or a text cell cut at 32,767 characters.

• Cached versus live values. A reader returning the last cached result of a formula that is out of date.

Silent errors are caught by checks, not by messages. The cheapest check is a control total: the sum of a column, the number of rows, the number of distinct keys, compared with a figure from the source system. Add it to every load and every generated report, and fail when the comparison differs.

A debugging order that works

When an Excel problem lands on your desk, resist the urge to try fixes at random. This order finds most causes in a few minutes.

1. Name the group. File, package, cell or library? The message tells you.

2. Copy the file. Work on a copy so nothing is made worse.

3. Check the basics. Size, first bytes, modified time. Does it match what the source says it sent?

4. Test the container. unzip -t for xlsx. If the zip is bad, stop and get a better copy.

5. Read the stored data, not the display. Formula text, cell types, sheet names, the shared strings. Libraries in read mode, or a look inside the unzipped XML.

6. Reproduce with a smaller file. Halve the data until the failure isolates to a row or a column.

7. Find the producer. Which program wrote this, which version, and has it changed?

8. Fix the generator. Add the fix and a test for the case.

9. Rerun and verify. Open the new output in Excel, and compare counts and totals.

The ordering matters: reading the stored values before theorizing saves hours. Nearly every case is explained by one of three things you can see directly: the first bytes, the formula text or the repair log.

Prevention: make the generator defensive

A report generator should defend itself against the data it is given. The checks fit in one function that runs on every value before it is written.

import math
import re

CONTROL = re.compile(r"[\x00-\x08\x0b\x0c\x0e-\x1f]")
MAX_CELL = 32767

def safe_cell(v):
    if v is None:
        return None
    if isinstance(v, float) and (math.isnan(v) or math.isinf(v)):
        return None                      # or an explicit marker
    if isinstance(v, str):
        v = CONTROL.sub("", v)
        if len(v) > MAX_CELL:
            v = v[: MAX_CELL - 12] + " [truncated]"
        if v[:1] in ("=", "+", "-", "@"):
            v = "'" + v                  # keep user text from becoming a formula
    return v

def safe_sheet_name(name, used):
    s = re.sub(r"[\\/?*\[\]:]", "_", name).strip("'")[:31] or "Sheet"
    base, i = s, 2
    while s.lower() in used:
        s = f"{base[:28]}_{i}"
        i += 1
    used.add(s.lower())
    return s

The function strips control characters, clamps long text, replaces NaN and infinity, and neutralizes text that begins with a formula character, which also protects readers from formula injection when the data came from outside. The sheet-name helper enforces the length, the forbidden characters and uniqueness. Write dates as naive datetimes in a known zone, and unique, non-empty table headers. Reuse style objects instead of creating one per cell.

Keep the number of formulas small. Every formula a program writes is a chance for a typo to repeat ten thousand times. Where a value is enough, write the value, and use the database for the calculation.

Test every generated workbook

Treat the output like code. After generating, reopen the workbook with a reader and check structure and numbers: the expected sheets exist with the expected names, the header row is right, the row count equals the source, a control total matches, no cell is an error string, and no formula cell is empty. Run this in the same job that generates the file and fail the job if any check does not pass.

For report types that matter, add a manual step the first time: open the file in the real Excel on the oldest version your users have. A reader library is more forgiving than Excel, so a clean load proves less than it seems to. Keep a small fixture of nasty inputs that previously caused failures, and generate a workbook from it in the test suite (testing that a generated Excel report is correct).

Keeping a pipeline running

A job that stops on the first bad file stops every other file too. A job that ignores bad files hides them. The compromise is to quarantine: move the failing file to a rejected folder with a sidecar text file recording the check that failed, and continue with the rest. Send one summary at the end listing rejected files and reasons. Someone has to look at that list, so make it short and put it where people already look.

Distinguish retryable from final failures. A truncated download may succeed on the second attempt; a file that is HTML will not. Retry the first kind a few times, then quarantine.

Reading other people's workbooks

Files from outside your control need more care than your own. Treat them as untrusted input. Check the size before reading, test the zip, look at the uncompressed sizes of its parts, and read in a process with limited memory. Do not enable macros or external link updates. A workbook can reference other files, run code in a macro-enabled format or expand from a few kilobytes to gigabytes. Dependable pipelines open them with a library that never executes macros, and discard the original after extraction.

When the structure varies between senders, write a small adapter per sender instead of one reader with a thousand conditions. Each adapter documents what that sender's file looks like, and a new sender is a new adapter.

Three short case studies

Real problems rarely arrive labeled. Three stories show how the groups above work in practice.

The export that opened in the wrong place. A nightly job wrote a customer file, and Monday's version was fine while Tuesday's had a block of rows shifted to the right. No error appeared anywhere. The first check was the row count: the CSV source had 41,208 records, and the worksheet had 41,236 data rows. Twenty-eight extra rows is the signature of long text being cut and spilling over. A scan for field lengths found twenty-eight notes fields over the cell limit, entered by one agent pasting email threads. The fix was a length clamp in the export with a link to the full text, plus a count of clamped cells in the job's log. Without the row-count habit, the problem would have shown up as a wrong total weeks later.

The report that needed repair every Monday. A weekly workbook produced by a script opened with the repair prompt. The log said that a table was repaired and cell information was removed from the third sheet. Opening the zip showed the third sheet had a table whose header row held two columns called Total. A change in the query had added a second total column. Excel repaired by renaming one and, on the way, dropped the formatting of the sheet. The generator now builds table headers through a function that makes them unique and fails the build if a duplicate would exist. The change was five lines, and a test with a duplicate header keeps it from coming back.

The environment that lost a package. A scheduled job ran for a year, then began failing with an error that said the Excel file format could not be determined. The files were unchanged. The cause was a rebuilt container image in which the Excel engine had been installed by hand in the old image and never recorded. The fix was a line in the requirements file and an explicit engine="openpyxl" argument so that a missing engine would be a clear import error instead of a guess. The lesson generalizes: when nothing in the data changed, suspect the environment.

In each story, the symptom pointed away from the cause. Counting rows, reading the zip and checking the environment were the steps that found it, and none needed Excel to be open.

Versions, locales and the machine that opens the file

A workbook that is valid can still behave differently on another machine, and the differences produce a class of reports that look like bugs in the file and are not.

Excel versions differ in the functions they know. A file that uses XLOOKUP, LET or dynamic arrays opens in Microsoft 365 and shows #NAME? in Excel 2016. The generator cannot see who will open the file, so it needs to know. If the audience includes older installations, restrict the formulas to functions available in the oldest version, or write values. When you must use a newer function, state the minimum version in the sheet's notes.

Locale changes how people see and type things, though not what the file stores. A formula is stored in English with commas, and Excel shows it with local function names and separators. Numbers and dates in the cell XML are stored in neutral form. Problems arise when text is involved: a text value that looks like a number or a date is parsed according to the opening machine's settings, so 03/04/2025 can be March or April, and 1.234 can be one thousand or one. Write real numbers and real dates, not text, and the locale cannot misread them.

The date system can differ too. Files created on old Mac versions use a 1904 base, which shifts every date by about four years if read as the 1900 system. A library that reports the workbook's date system, or a sanity check that the minimum and maximum dates fall in the expected period, catches it.

Protected View, trusted locations and security policy decide whether a downloaded file opens editable, read-only or not at all. A report emailed to a customer may open in Protected View with formulas uncalculated until the reader clicks Enable Editing. If your file depends on calculation, write cached values, or tell readers what to expect.

Last, the machine matters when the file is large. A workbook with several hundred thousand formulas, or a 32-bit Excel with a memory limit, may fail to open on one computer and open on another. When a report works for you and not for a colleague, compare the Excel version and bitness before you change the file.

Writing error messages that help the next person

A pipeline that reads or writes workbooks will fail eventually, and the message it leaves is the first thing the next person reads. Make it useful. Say what the program expected and what it found: "expected an xlsx workbook, found HTML starting with <html>" rather than "bad file". Name the file, the sheet and, where relevant, the cell or row. Include the check that failed and the number involved: "row count 41,236, source 41,208". Say what to do next, if there is an obvious next step, such as "re-download the file" or "ask the sender to export as CSV".

Avoid swallowing exceptions. A broad except that logs "failed" and moves on turns a clear library error into a mystery. Catch narrowly, add context, and re-raise or quarantine. Keep the original exception text in the log, since it often holds the detail the wrapper lacks.

Finally, make failures visible to the people who depend on the output. A report that is silently stale is worse than one that is late with an explanation. A short notice, sent when a job fails or when files are rejected, costs one function and saves several confused emails.

A pre-flight checklist for any workbook pipeline

Before a workbook-producing or workbook-reading job goes live, confirm each of these. The engine is chosen by content and is listed in the requirements file. Inputs are checked for size, first bytes and zip validity. Strings are cleaned of control characters and clamped to the cell limit. Sheet names are valid and unique. Table headers are unique and non-empty. NaN, infinity and out-of-range dates are replaced. Formulas, if any, use English names, commas and the _xlfn. prefix for newer functions, and a test reads them back. Control totals and row counts are compared with the source on every run. Rejected files go to a quarantine folder with a reason. A fixture of known-bad inputs is part of the test suite. And someone has opened a generated sample in the oldest Excel version the users run.

When the error is not an Excel problem

The data was wrong before it reached Excel. A control character in a string, a NaN in a column, an impossible date: these come from the database or the API. Fix them where they start, and keep the Excel layer as a final check.

The format is the problem. If you are generating a million rows for another program to read, Excel is the wrong format. CSV, JSON Lines or Parquet have no cell limit, no repair prompts and no formulas to break (Excel files that are too big or too slow).

The environment is the problem. A missing package, the wrong interpreter, a file path that does not exist in the container, a disk that is full. Check these before debugging the file.

The user's machine is the problem. Protected View, an old Excel version without a function, a different locale. Reproduce on their configuration before changing the generator.

It is a security question. Unexpected content in a workbook, such as formulas that call external resources or macros nobody wrote, is a security matter first and a data matter second.

Where to go next

Start with the group that matches your symptom. For a file that will not open, begin with why Excel says the file format or extension is not valid. For a repair prompt, fixing Excel found unreadable content after a script export. For cell codes, Excel REF NAME and VALUE errors explained. For library exceptions, fixing BadZipFile when reading an XLSX and the Python xlrd error. To go beyond one workbook, automating Excel work without clicking covers building the whole job.

The reference material is Microsoft's page on Excel specifications and limits, the openpyxl documentation, the XlsxWriter documentation and the open standard ECMA-376. To convert a small extract to JSON, use the CSV to JSON converter.