Excel couldn't locate blanks?

Look at the picture below: it shows that D163, D166, E166, D170 and E170 are blanks. But the notice in the center tells that it could not locate blanks, which means there is actually no blank in this sheet.

Why does it happen?

At the beginning, we should understand the difference between "real blanks" and "fake blanks."

If there's completely nothing in the cell, then it's "real blanks".

If there's something like a blank space, or it's sort of a blank text, this cell will look like blank. This is called "fake blanks".

If Excel couldn't locate blanks, it may be because the blank cell you see is a fake one.

How to check whether the blank cell is real or not?

Take advantage of formula ISBLANK (value).

ISBLANK (value): Checks whether the referenced cell is empty. ISBLANK returns TRUE if it is 100% blank and FALSE otherwise.

For instance, I checked Column D, E, F to see if there is a real blank. I utilized the formula, and searched to check if there is TRUE which means there is a real blank.

The searching result shows that there is no real blank because there is no "TRUE."

Apparently, Excel could not find a 100% blank in those three columns. But we do see blank cells in the first picture, right?

How to find out these fake blanks and make them true?

Step 1: Select all cells and replace with "AAA."

Step 2: Replace all "AAA" with none.

Now you can see the check cell G163, G170 and H170 is TRUE, which means now they are totally blanks. And you can locate them and make any change to them.