Problem with conditional formatting, format only cells with Blanks

Question

Hi there,

I'm having trouble with conditional formatting. 

My issue is:

I have a range with 10 cells. In this 10 cells, i have 2 blank cells (means that they contain nothing); and 1 cell contains some spaces

When i used Conditional Formatting, rule type is: format only cells that contain

Rule description is: Format only cells with: Blanks

and Problem is: 3 cells is hightlight (not 2, but 3). What's going on? 2 cells is really empty (blank), but 1 cell is not, it contains some spaces.

I checked with COUNTBLANK in this 10 cells range, and it returns only 2.

I'm using my personal laptop, with Office professional plus 2010. And i checked this issue on my friend's laptop, with office 2016, and the same problem.

Any suggestion for my problem? 

Thanks!

***Post moved by the moderator to the appropriate forum category.***

Answer 1

Yes! It sees a space as a blank. I consider this is a bug but try the following work around.

Select "Use a formula to determine which cells to format"

Enter the following formula where A1 is the first cell in the "Applies to" range (ie. the range for the conditional formatting). Ensure that you include the leading equals sign.

=ISBLANK(A1)