Fixing “DataFormat.Error: Invalid cell value” in Excel imports.

A DataFormat.Error: Invalid cell value occurs in Power Query when the Excel data connector encounters a cell whose contents cannot be parsed into a valid Power Query data structure. This typically happens when importing data from .xlsx or .xls workbooks containing corrupted cell metadata, dates outside supported ranges, formula evaluation breaks, or unprintable control characters. Power Query flags the entire record as an error because the binary structure of that specific cell violates standard workbook formatting rules.

Quick Answer

To resolve this error immediately, click on the whitespace next to the word Error in Power Query to identify the broken source row. Open the source Excel file, navigate to that cell, and clear any formula breaks (like #NUM! or #VALUE!), fix pre-1900 dates, or clear special character formatting. Save the file and refresh Power Query.

---

Understanding the Root Causes

When Power Query reads an Excel file using the Excel.Workbook M function, it parses the underlying OpenXML sheets. Unlike the Excel interface, which displays formula errors or irregular values gracefully in the grid, Power Query enforces strict type checking. If a cell contains data that does not conform to standard OpenXML schemas, the import engine throws DataFormat.Error: Invalid cell value.

The four most frequent culprits are:

1. Pre-1900 Dates and Negative Date Serials: Excel supports dates starting from January 1, 1900 (or 1904 if the 1904 date system is enabled). Negative numbers formatted as dates or manual inputs such as 12/31/1899 cause immediate parsing failures when Power Query reads the sheet.
2. Underlying Formula Breaks in Source Sheets: If the source workbook contains severe formula errors, such as dynamic array calculation failures or unresolved external references, the cell payload may fail to serialize correctly.
3. Embedded Non-Printable Characters: Text fields containing null bytes (ASCII 0) or low-order control characters copied from legacy ERP exports will break Power Query’s XML parser.
4. Corrupted Cell Styles and Number Formats: Malformed custom number formats in the source workbook can prevent the engine from extracting the raw underlying value.

---

Step-by-Step Diagnostic and Resolution Path

1. Isolate the Failing Cell

Do not click the blue “Error” hyperlink directly, as this adds a navigation step that isolates only the error. Instead:

1. Open the Power Query Editor.
2. Scroll to the column displaying the error.
3. Click in the blank white space inside the cell next to the word Error.
4. Read the detail pane at the bottom of the editor. It will confirm:
   DataFormat.Error: Invalid cell value.Details: [Value]
5. Note the surrounding row data (such as an ID or timestamp) so you can locate the exact cell in the source workbook.

2. Inspect and Clean the Source Workbook

Once the row is identified:

• Check if the source cell is a date column. If the cell displays 00/00/0000, #VALUE!, or a year prior to 1900, clear the formatting or correct the date entry.
• If the cell contains text copied from a database, it may contain hidden characters. In the source file, apply a clean-up formula or review #N/A because of Hidden Non-Printing Characters (CLEAN function fix).
• Check if the source file contains broken links or calculations. If formula errors are scattered throughout the source workbook, see “We found a problem with some content in ‘filename.xlsx'”: Using the Open and Repair tool.

3. Handle the Error Directly Inside Power Query

If you do not have permission to edit the source Excel file, you must handle the error within your M pipeline.

Option A: Replace the Error with a Default Value

1. Select the column containing the error.
2. Go to the Transform tab on the ribbon.
3. Select Replace Errors.
4. Enter a neutral value (such as 0 for numbers, null, or "Unknown" for text).

If you need to decide between removing rows versus substituting defaults, review How to use “Remove Errors” vs. “Replace Errors” in the PQ Interface.

Option B: Surgical Replacement Using M Code (try ... otherwise)
If automated replacement steps fail or mask errors you need to audit, create a custom column in the Advanced Editor using native M logic:

= Table.AddColumn(#"Previous Step", "CleanedColumn", each try [ProblemColumn] otherwise null)

For advanced error catching inside custom M logic, refer to Using try…otherwise in M-code to create custom error handling steps.

---

What to Do If the Error Persists

If every row in an entire column or table throws DataFormat.Error: Invalid cell value as soon as the file is loaded:

• Examine the File Format: Third-party systems often export HTML tables or Comma-Separated Values with an .xlsx extension. Open the file in a text editor like Notepad. If you see plain text or HTML tags (<html>, <table>), the file is not a genuine OpenXML spreadsheet. Re-save it properly as an .xlsx file or import it using Csv.Document or Web.Page.
• Bypass the Excel Connector: If the source file remains corrupt, export the sheet to CSV and import via Csv.Document, which ignores internal cell XML metadata.