How to Fix Excel Cells That Look Blank but Are Not
Question details
The user needs to clean spreadsheet cells that appear visually empty but are not recognized as blank due to hidden formatting, zero-length strings, or imported characters.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing data from external sources or portals where seemingly empty cells fail validation rules or the ISBLANK() function.
- Observed behavior
- Cells look completely empty but evaluate to FALSE when checked with ISBLANK. Clearing formatting does not resolve the issue, and cells require individual manual editing.
Before applying a fix, check what kind of invisible data you are dealing with. Type `=LEN(A1)` in an adjacent cell—if the result is greater than 0, the cell contains hidden characters (like spaces) rather than a true blank.
Clean Blank Values Using Power Query
Power Query is the most reliable tool for standardizing imported data and converting fake blank cells or hidden characters into true null values.
This method is highly recommended when dealing with large datasets imported from external portals or web pages, as it automates the cleanup process without manual cell editing.
Select your data range, go to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.
Select the affected columns. Right-click the column header and choose 'Replace Values'. In the 'Value To Find' box, enter the specific hidden character (or leave it empty if it's a zero-length string). In 'Replace With', type 'null' (in lowercase) and click OK.
If you need to populate these newly created null cells with data from the rows directly above them, right-click the column header, select 'Fill', and then click 'Down'.
Click 'Close & Load' in the top-left corner to return the cleaned dataset to a new worksheet. The cells will now register as truly blank.
Identify and Remove Hidden Characters with Formulas
Use diagnostic formulas to find the exact hidden character code causing the issue, then remove it using the Find and Replace tool.
Use WPS Spreadsheet to Quickly Clean Imported Data
If you frequently work with messy imported data, WPS Spreadsheet provides an intuitive, robust set of data cleaning tools. You can easily remove hidden spaces, fix formatting issues, and replace non-standard blanks without complex formulas.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open the workbook containing the problematic blank cells.
- 2. Highlight the affected data: Select the column or range that is failing the ISBLANK checks.
- 3. Use Find and Replace: Press 'Ctrl + H'. Enter the hidden space character in 'Find what', leave 'Replace with' completely empty, and select 'Replace All'.
- 4. Verify true blanks: Use `=ISBLANK()` on the cleaned cells to confirm they now evaluate to TRUE.
Frequently Asked Questions
Why does ISBLANK return FALSE for a cell that looks completely empty?
This usually occurs when the cell contains a zero-length string (like ""), standard spaces, or invisible non-printing characters (such as line breaks or non-breaking spaces). These are visually invisible but mathematically present, preventing the cell from being truly blank.
Will clearing cell formats fix fake blank cells?
No. The 'Clear Formats' option only removes visual styling such as bold text, borders, or background colors. It does not delete the underlying hidden characters or data strings causing the cell to register as non-blank.
How do I quickly fix zero-length strings left behind by old formulas?
Select the column containing the zero-length strings, go to the 'Data' tab, click 'Text to Columns', and immediately click 'Finish' without changing any settings. This forces the spreadsheet to re-evaluate the column, converting those strings into true empty cells.
