🔧 Errors & fixes

📘 Start with the full guides

The long-form treatment of errors & fixes, with the reasoning the shorter pages assume.

• GuideTroubleshooting Common Python Excel ErrorsDecode the errors Python throws at Excel files: BadZipFile, InvalidFileException, engine errors, missing worksheets, corrupt output and locked files — with fixes.
• GuideError Handling and Logging in Excel AutomationMake scheduled Excel jobs survivable — structured logging, retries with backoff, atomic writes, permission and lock errors, output validation, exit codes and alerts that mean something.

🛠️ Step-by-step how-tos

One task each, with code you can copy, run and adapt.

• How-toWhat openpyxl Loses When You Save an Existing Workbookopenpyxl rewrites the whole file when it saves, and features it does not model disappear — sparklines, shapes and text boxes, data-bar and validation extensions, macros, slicers. Tested list, a pre-save risk scan, and safer alternatives.
• How-toHandle Daylight Saving Time in Excel TimestampsExcel stores local clock times with no time zone, so the hour that repeats when clocks go back and the hour that never happens when they go forward cause wrong durations, double-counted readings and errors in pandas. Localise naive timestamps safely with ambiguous and nonexistent options, infer the repeated hour from the order of readings, compute true elapsed durations, aggregate by day without 23- and 25-hour surprises, and write results back in a form Excel users understand.
• How-toHandle Microsoft Graph Throttling in Excel AutomationKeep Excel Online automation running when Microsoft Graph pushes back — honour Retry-After on 429 and 503 responses, back off with jitter on 504s, never blindly retry non-idempotent writes, run one worker per workbook, and handle throttled requests inside JSON batches. Includes a retry client you can test without a network.
• How-toClose Excel Cleanly and Avoid Orphan COM ProcessesExcel exits only when every reference is released. Learn the try/finally shape, CoInitialize on worker threads, and how to detect and kill just the instance you started.
• How-toRun Excel COM Automation from Task Scheduler Without a Logged-In UserWhy a pywin32 script that drives Excel works when you run it by hand and fails or hangs from Task Scheduler — session 0, the missing systemprofile Desktop folder, DCOM identity, dialogs nobody can click — and the setups that work, plus how to avoid needing Excel at all.
• How-toInspect the XML Inside an .xlsx File with PythonAn .xlsx file is a ZIP of XML parts, and looking inside answers questions no library will: list the parts with zipfile, follow workbook.xml and its relationships to the right sheet file, decode cells and their t= types, resolve shared strings and number formats, pretty-print a part, and diff two workbooks part by part to see exactly what a tool changed.
• How-toRead Excel 2003 XML Spreadsheet FilesMany banking, ERP and web systems export "Excel" files that are really Excel 2003 XML Spreadsheet documents, often with an .xls extension, and pandas, xlrd and calamine all refuse them. Recognise the format from its first bytes, read every worksheet into pandas with a short ElementTree parser that respects skipped cells, skipped rows, merged cells and the Number, DateTime and Boolean types, and convert the result to a real .xlsx.
• How-toRead Strict Open XML Excel Files in PythonRead workbooks saved in Excel's "Strict Open XML Spreadsheet" format, which pandas, openpyxl and calamine reject with "Worksheet index 0 is invalid, 0 worksheets found" or "Cannot detect file format": recognise a Strict file, convert its namespaces to the usual transitional ones in memory so pandas can read it, convert with LibreOffice when that is not enough, and stop the files arriving.
• How-toRead an Excel File That Is Open in ExcelRead a workbook that someone has open in Excel without errors or surprises: why pandas sees the last saved version and not what is on screen, how to take a consistent snapshot copy and retry when the file is locked or caught mid-save, how to tell when a file is open, and how to read the live, unsaved values from the running Excel with xlwings.
• How-toFind External Links and Broken References in a Workbook with PythonAudit a workbook for links to other files and for #REF! errors — in cell formulas, defined names, data validation and conditional formatting — with openpyxl and the zip file itself, then report them or replace them with values.
• How-toWrite Very Wide DataFrames to ExcelExcel stops at 16,384 columns. pandas raises "This sheet is too large!" above that — but only checks the DataFrame itself, so a frame that fits exactly loses its last column silently when the index is written. Detect the real width, then choose a fix: transpose short-and-wide data, reshape to long format with melt, or split the columns across several sheets with the key repeated on each, and check what was written.

🔧 When it breaks

The errors this topic produces, with the cause explained rather than just the fix.

• FixHandle Permission Denied When Writing Excel in PythonWhy openpyxl raises PermissionError when a workbook is open in Excel, how to detect the lock file, retry sensibly, fall back to a timestamped name, and avoid the problem entirely.
• FixFix "At least one sheet must be visible" in openpyxl and pandasopenpyxl refuses to save a workbook in which no sheet is visible: "IndexError: At least one sheet must be visible", or for a single sheet "The only worksheet of a workbook cannot be hidden". Both usually come from a pandas ExcelWriter that wrote no DataFrames, or from hiding data sheets. Find which case you have, and guard against empty output.
• FixFix BadZipFile When Reading an Excel File in Pythonzipfile.BadZipFile: File is not a zip file — why openpyxl and pandas raise it, how to identify the real format, and how to recover the data or repair the workbook.
• FixFix "ValueError: Cannot convert to Excel" in openpyxlopenpyxl accepts only values Excel can store — numbers, text, booleans, dates, None. Lists, dicts, sets, UUIDs and pandas' NA raise "Cannot convert … to Excel". Find the offending values, convert them deliberately, and write DataFrames without surprises.
• FixFix "Could Not Convert String to Float" in Excel DataFix ValueError: could not convert string to float when converting an Excel column with pandas: find exactly which cells fail and why, then clean thousands separators, currency symbols, percentages, accounting negatives in parentheses, non-breaking spaces, Unicode minus signs, European decimal commas and placeholder text — and report what could not be converted instead of hiding it.
• FixFix the "Data Validation extension is not supported" Warning in openpyxlWhat openpyxl's "Data Validation extension is not supported and will be removed" warning means, when it is harmless, how to silence it narrowly, and how to rebuild the dropdowns it drops before you save the workbook.
• FixFix Duplicate Column Names When Reading Excel with pandasWhen a sheet has two columns with the same header, pandas renames the second to "Sales.1", and near-duplicates such as "Sales " and "sales" slip through as separate columns. Find every duplicate, decide what each column really is, give them unique names from a two-row header or an explicit mapping, and stop surprises such as to_dict silently dropping data.
• FixFix "Excel does not support datetimes with timezones" in pandasExcel cells cannot store a time zone, so pandas refuses to write timezone-aware timestamps. Decide which clock the reader needs, convert to it, drop the zone, and label the column — for columns, indexes and object columns alike.
• FixFix "Excel File Format Cannot Be Determined" in pandasWhy pandas cannot pick an engine for your spreadsheet, how to name the engine explicitly, and how to handle streams, URLs and files with no extension at all.
• FixFix "Excel Cannot Open the File Because the File Format or Extension Is Not Valid"Excel refuses a file your Python script produced when its bytes do not match its .xlsx name: CSV or HTML saved under an Excel extension, a writer that was never closed, bytes mangled in text mode, or a macro workbook saved as .xlsx. Diagnose which one with a short checker and fix the writing code.
• FixFix "Excel Found Unreadable Content" After Writing with PythonYour script saved a workbook and Excel offers to repair it. The rules Excel enforces — sheet names, table names, defined names, timezones — and how to satisfy them.
• FixFix "ExcelWriter object has no attribute save" in pandaspandas 2.0 removed ExcelWriter.save(), so old scripts fail with AttributeError. Replace it with a with block or close(), and fix the other removed writer APIs that usually break at the same time — book assignment, sheets assignment and handles.
• FixFix Floating-Point Noise Like 0.1 + 0.2 ≠ 0.3 in Excel OutputSums such as 0.1 + 0.2 print as 0.30000000000000004, prices turn into 1998 cents instead of 1999, and threshold checks fail by a hair. See where binary floating point leaks into Excel reports, what the xlsx writers already hide, and how to round, compare and convert money safely.
• FixFix Formula Cells Reading as NaN (or 0) in pandaspd.read_excel returns NaN — or a suspicious 0 — for every formula cell in a workbook generated by a script, while the same file shows the right numbers in Excel. The cause is a missing cached result, not a bad read. Detect it, then fix it by recalculating with LibreOffice, computing the values in pandas, or writing cached results when the file is created.
• FixFix Formulas Showing as Text in Excel Output from PythonA workbook written by Python shows "=SUM(B2:B9)" in the cell instead of the result. Check whether the cell was stored as a formula or as text, then fix the cause: a leading space or apostrophe, a writer told not to convert strings to formulas, text written with write_string, or a sheet saved with Show Formulas switched on.
• FixFix #### Showing Instead of Values in Excel OutputNumbers and dates written from Python show as ##### in Excel when the column is too narrow for the formatted value, and negative dates and times always show that way in Excel. Set widths from the formatted text, use xlsxwriter's autofit, shrink to fit where space is fixed, and check the result by rendering the sheet.
• FixFix IllegalCharacterError When Writing Excel with openpyxlopenpyxl 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.
• FixFix Invalid Worksheet Name Errors When Writing ExcelSheet names built from data — regions, customers, dates — break Excel's rules: 31 characters at most, none of [ ] : * ? / \, no leading or trailing apostrophe, unique regardless of case. See what openpyxl, xlsxwriter and pandas do with each rule and write one function that makes any name safe.
• FixFix KeyError from Spaces in Excel Column Namespandas raises KeyError for a column you can see in the sheet: the heading has a trailing space, a non-breaking space, a line break typed with Alt+Enter, a zero-width character or a different dash. Show the hidden characters with repr and unicodedata, suggest the nearest real column name, normalise the headings once after reading, and make the lookup fail with a useful message.
• FixFix "Filesize would require ZIP64 extensions" (LargeZipFile) in XlsxWriterVery large exports fail at close() with xlsxwriter.exceptions.FileSizeError: Filesize would require ZIP64 extensions, raised from zipfile.LargeZipFile. Learn what crosses the limit (one XML part over 2 GiB, not the .xlsx file size), estimate when your export will hit it, turn on use_zip64 in XlsxWriter or through pandas, and when to split the data or choose another format instead.
• FixFix Long ID Numbers Losing Digits or Turning into 1.23E+18 in ExcelAccount numbers, card numbers and 18-digit IDs lose their last digits when written to Excel as numbers — Python writers keep 16 significant digits and Excel keeps 15. Write identifiers as text, read them back as text, and stop the leading zeros and trailing digits from changing.
• FixFix "MergedCell object attribute value is read-only" in openpyxlopenpyxl raises this AttributeError when code writes to a cell that sits inside a merged range. Find the merge, write to its top-left cell, and watch for the less obvious cause — insert_rows and delete_rows move cells but leave merged ranges behind.
• FixFix "Missing optional dependency 'openpyxl'" in pandaspandas reads and writes Excel through separate engine packages, and the error means the engine is not importable from the Python that runs your code. Find which interpreter is running, install into that one, and handle the version-too-old variant.
• FixFix #NAME? in Formulas Written from Python (XLOOKUP, TEXTJOIN, IFS)Formulas that work when typed into Excel show #NAME? when openpyxl writes them. Newer functions must be stored with an _xlfn. prefix, and misspelled or localised names, missing defined names and unquoted text cause the same error. Lint every formula before saving.
• FixFix NoneType Errors When Reading Cells with openpyxlWhy openpyxl hands your code None — empty cells, formulas that were never calculated, merged ranges, trailing formatted rows — and how to fix each one: find which cell is None, skip or default empty values deliberately, get formula results with data_only after a recalculation, and read merged ranges from their top-left cell.
• FixFix "Number Stored as Text" Warnings in Excel OutputGreen triangles all over a workbook your script wrote mean numbers went in as strings. Convert them to real numbers before writing, keep identifiers as text on purpose, and silence the warning where text is correct.
• FixFix "openpyxl does not support the old .xls file format"Why openpyxl refuses .xls files, how to read them with xlrd or calamine, how to convert a whole folder to .xlsx, and how to spot a mislabelled file before it fails.
• FixFix openpyxl max_row Counting Empty Rowsws.max_row reports 2,000 on a sheet with six rows of data, and ws.append() writes far below the table. Learn the three things that inflate max_row — formatted cells, cells created by reading, and stale dimension tags — and how to find and fix the real last row.
• FixFix openpyxl "TypeError: expected <class ...>"openpyxl checks the type of every style, chart and dimension attribute, and raises "TypeError: expected <class 'float'>" or "expected <class 'openpyxl.styles.fills.Fill'>" when it gets the wrong kind of value. Read the class name, find the assignment, and pass the right object — including the case where the error only appears at save time, far from the line that caused it.
• FixFix openpyxl "Value must be one of" and "Max value is" Errorsopenpyxl validates style and settings values against the names Excel uses internally, so Side(style="bold"), Alignment(horizontal="middle"), PatternFill("Solid") and orientation="Landscape" all fail with ValueError: Value must be one of {...}. Translate the familiar names to the accepted ones, use openpyxl's constants, handle "Max value is" and colour errors, and watch for values that are only checked at save time — after half a file has been written.
• FixFix "OverflowError: date value out of range" When Reading ExcelDate cells that hold numbers too large or too small to be dates — a sentinel like 99999999, a yyyymmdd number formatted as a date, an amount column with the wrong format — raise OverflowError in from_excel and older openpyxl, become #VALUE! with a warning in openpyxl 3.1, NaT in pandas, or OutOfBoundsDatetime later. Find the raw values, decide what they mean, and convert them deliberately.
• FixFix pandas FutureWarnings in Excel PipelinesClear the FutureWarnings an Excel reporting script prints under pandas 2.x before they turn into errors in pandas 3: month-end and hourly frequency aliases, fillna(method=), applymap, positional Series indexing, chained assignment and inplace on a column — plus the silent pandas 3 change to text columns that breaks dtype == object checks — with each warning reproduced under both versions and fixed.
• FixFix read_excel Returning an Empty DataFrameFind out why pandas read_excel returns an empty DataFrame — or one full of NaN — for a file that clearly has data: the wrong sheet read by default, skiprows or header set past the data, usecols pointing at empty columns, skipfooter or nrows trimming everything, and formulas with no saved results; with a diagnostic that inspects the workbook and suggests the right arguments.
• FixFix ReadOnlyWorksheet Has No Attribute Errors in openpyxlSwitching to load_workbook(read_only=True) for speed breaks code that used iter_cols, merged_cells, column_dimensions, comments, hyperlinks or writes — and EmptyCell objects have no coordinate. See exactly what read-only mode supports, rewrite column access and gap handling for streaming, get the missing metadata from a second normal load, and know when read-only is the wrong mode.
• FixFix "Sheet Already Exists" Errors in pandas ExcelWriterFix ValueError: Sheet 'Summary' already exists and if_sheet_exists is set to 'error' when writing to an existing workbook with pandas: what each if_sheet_exists option really does — error, new, replace and overlay — why overlay leaves old rows behind, the related "Append mode is not supported with xlsxwriter" error, and a safe way to refresh one sheet of a workbook.
• FixFix Text Truncated at 32,767 Characters in Excel OutputAn Excel cell holds at most 32,767 characters, and Python writers enforce it differently: openpyxl cuts long text silently, xlsxwriter returns -2, pandas warns. Find the long values before writing, make truncation an error, and keep the full text by splitting it across cells, linking to a file, or choosing another format.
• FixFix "This sheet is too large" When Writing Excel with pandaspandas raises "This sheet is too large! Your sheet size is … Max sheet size is 1048576, 16384" when a DataFrame exceeds Excel's grid. Split rows across sheets, transpose or split wide frames, summarise, or choose another format — and avoid the off-by-one that silently drops a row.
• FixFix "Object of type Timestamp is not JSON serializable" When Exporting Excel Datajson.dumps fails on rows read from Excel because pandas hands you Timestamp, numpy int64 and NaN values that the json module does not understand — and NaN that it does write produces invalid JSON. Fix it with to_json and ISO dates, a small default= converter, or a clean conversion step, and check the output parses in strict JSON readers.
• FixFix UnicodeDecodeError When Reading CSV Files Saved by Excel"utf-8 codec can't decode byte 0xfc" or "byte 0xff in position 0" when reading a CSV someone saved from Excel means the file is not UTF-8: Excel's plain "CSV" format uses the Windows code page, "Unicode Text" is UTF-16, and "CSV UTF-8" adds a byte order mark. Identify which one you have, read it with the right encoding, and stop silent corruption from latin-1 and errors="replace".
• FixFix "Unnamed: 0" Columns When Reading Excel with pandasColumns called "Unnamed: 0", "Unnamed: 3" or "Unnamed: 0_level_1" appear when a header cell is empty. Find which of the four causes produced them and apply the matching fix — index_col, usecols, header rows or a cleanup step.
• FixFix "Usecols do not match columns" in pandas read_excelValueError: Usecols do not match columns, columns expected but not found — and its cousin "Defining usecols with out-of-bounds indices is not allowed" — mean the names or positions you asked for are not in the header pandas actually read. Print the real header, account for title rows, trailing spaces and renamed columns, and select columns with a forgiving callable so a cosmetic change in the source does not break the job.
• FixFix "ValueError: I/O operation on closed file" with ExcelWriterpandas raises "ValueError: I/O operation on closed file" when an ExcelWriter or the buffer it wrote to is used after it was closed: a file handle whose with block ended before the writer, a BytesIO returned from inside its own with block, or a buffer read after close. Reproduce each case, see why the file ends up empty, fix the order of the blocks, and catch the silent variant where writing after the with block loses a sheet without any error.
• FixFix the win32com gen_py AttributeError (CLSIDToClassMap)Excel automation with pywin32 suddenly fails with an AttributeError saying a win32com.gen_py module has no attribute CLSIDToClassMap. The generated type-library cache is broken. Find the gen_py folder, delete just the Excel entry, let EnsureDispatch rebuild it, and stop it happening again on shared and scheduled machines.
• FixFix "Workbook contains no default style" Warning in openpyxlopenpyxl warns "Workbook contains no default style, apply openpyxl's default" when a file's stylesheet lacks a Normal style. The data is fine — but the related "no stylesheet" warning can turn dates into numbers. Tell them apart, silence the harmless one precisely and repair the files.
• FixFix "Worksheet Does Not Exist" KeyError in openpyxlWhy openpyxl raises KeyError on a sheet you can see in Excel: hidden whitespace, case, renamed tabs and index confusion — plus a resilient sheet lookup helper.
• FixFix "Worksheet index 0 is invalid, 0 worksheets found"pandas raises "ValueError: Worksheet index 0 is invalid, 0 worksheets found" when openpyxl opens an .xlsx and finds no worksheets, even though Excel shows the data. The usual cause is a file from another tool whose internal paths differ in upper and lower case. Diagnose it by comparing the workbook's relationships with the zip contents, then read it with calamine or repair the paths.
• FixFix Wrong Sheet Order After Writing with pandasSheets come out as Apr, Aug, Dec, Feb… or the summary ends up last after appending to a workbook. Find which of the usual causes applies — alphabetical group keys, sets and unsorted dictionaries, sheets appended to an existing file — write the sheets in the order you want, or reorder an existing workbook with openpyxl, set the sheet that opens first, and check the final order in a test.
• FixFix "XLRDError: Excel xlsx file; not supported" in Pythonxlrd 2.0 reads only legacy .xls files, so any code path that sends an .xlsx workbook to xlrd now fails. Find where xlrd is being chosen, switch that path to openpyxl, and port old xlrd-style code without pinning an outdated version.
• FixFix xlsxwriter "Exception caught in workbook destructor"The xlsxwriter message "Exception caught in workbook destructor. Explicit close() may be required" comes from versions before 1.1, where an unclosed workbook was saved when garbage collected. Current versions print nothing and write nothing instead. Close every workbook explicitly, handle FileCreateError, and avoid writing half-built reports from a with block.
• FixFix XlsxWriter Overwriting an Existing WorkbookYou pointed XlsxWriter — or pandas with its default engine — at an existing workbook to add a sheet, and every other sheet vanished. XlsxWriter only creates new files and never reads old ones. See why, what "Append mode is not supported with xlsxwriter!" means, how to add or replace a sheet with openpyxl's append mode and if_sheet_exists, and how to guard against overwriting a file by accident.
• FixFix Excel Serial Numbers Showing Instead of DatesYour Excel file shows 45292 where a date should be. Fix it in Python with openpyxl number formats, pandas ExcelWriter date formats, and a repair pass for existing workbooks.
• FixHandle COM Errors and Excel Dialog Prompts in PythonDecode pywintypes.com_error through excepinfo, recognise the four HRESULTs that matter, suppress every dialog before opening a file, and add a watchdog for the hangs left over.
• FixFix Python in Excel ErrorsWhat #PYTHON!, #CALC!, #SPILL!, #BUSY!, #CONNECT!, #TIMEOUT!, #BLOCKED! and #UNKNOWN! mean in a =PY cell, how to read the Diagnostics pane, and the fix for each one.

🧭 Follow it through a track

Prefer an ordered route? These tracks cover errors & fixes along the way, in the order that builds on itself.

• 🌱Getting started61 of this topic's pages
• 🚀Reporting workflows3 of this topic's pages
• 🧹Data transformation2 of this topic's pages

🔗 Related topics

Subjects that keep turning up on the same pages as this one, most overlap first.