How to Fix Excel Power Query DataFormat.Error Invalid Cell Value #DIV/0!
Question details
Users experience a DataFormat.Error in Excel Power Query caused by #DIV/0! formula errors in the source data, which disrupts the query refresh process.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Refreshing a workbook containing Power Query data, particularly when sharing files with users on older versions like Excel 2016.
- Observed behavior
- Power Query fails to load or transform data, returning a DataFormat.Error indicating an invalid cell value (#DIV/0!).
Before applying fixes, review your source dataset to identify which columns contain the #DIV/0! errors, as this determines whether you should correct the formulas directly or handle them within Power Query.
Replace Errors with Null Values in Power Query
Add an early step in your query to replace existing errors with nulls, preventing transformation failures and improving backward compatibility.
Handling errors directly within the Power Query Editor is the safest approach, especially when the source workbook contains multiple sheets and is accessed by users on different Excel versions (like Excel 2016).
Select a cell in your queried data table, go to the 'Data' tab on the ribbon, and click 'Queries & Connections'. Double-click your specific query in the side pane to open the editor.
Identify and select the column or columns containing the #DIV/0! errors. To select multiple columns, hold down the 'Ctrl' key while clicking the column headers, or select all columns by clicking the first, holding 'Shift', and clicking the last.
Navigate to the 'Transform' tab in the Power Query Editor ribbon. Click the 'Replace Values' dropdown menu and select 'Replace Errors'.
In the dialog box that appears, type 'null' (without quotes) or another logical fallback value like '0', then click 'OK'. This automatically generates the Table.ReplaceErrorValues function in your applied steps.
Click 'Close & Load' on the Home tab to save your changes and refresh the query data without encountering the DataFormat.Error.
Correct Source Data Formulas Using IFERROR
Prevent the #DIV/0! error at its root by updating the original Excel formulas before they are imported into Power Query.
Process Data Seamlessly with WPS Office
Dealing with complex Excel errors like Power Query DataFormat.Error can be frustrating. Try WPS Office, a lightweight, highly compatible alternative for everyday spreadsheet tasks, data analysis, and formula management.
- 1. Download the Installer: Visit the official WPS Office website and click the download button to get the free setup file.
- 2. Install WPS Office: Run the downloaded installer and follow the simple on-screen prompts to set up the software.
- 3. Open Your Spreadsheet: Launch WPS Spreadsheets, open your existing .xlsx files, and enjoy seamless data manipulation.
Frequently Asked Questions
Why does Power Query show a DataFormat.Error when refreshing?
This error generally occurs when Power Query encounters a cell value that doesn't match the expected column data type. Excel formula errors like #DIV/0!, #N/A, or #VALUE! in your source data trigger this failure during the import process.
Does replacing errors with 'null' affect my final data analysis?
Replacing errors with 'null' simply leaves the cell blank in the queried dataset. Most pivot tables, charts, and aggregation functions (like SUM or AVERAGE) safely ignore null values, making it a standard and safe practice for data cleaning.
Why is this error more prominent when sharing files with Excel 2016 users?
Older versions of Excel, such as Excel 2016, use older builds of the Power Query engine. These older engines can be more rigid and may fail to gracefully handle unhandled formula errors during background transformations compared to newer Microsoft 365 updates.
Can I replace errors with a specific number instead of 'null'?
Yes. In the 'Replace Errors' dialog box, instead of typing 'null', you can input any fallback value. For financial or mathematical data, you might type '0' so the rows can still be calculated in downstream formulas.
