After years of using Excel I have discovered today that Excel considers a numeric zero to be the same as an empty cell (at least in conditional formats).

The background to this, in case it helps, is that I have a series of expected results from a test run (in column E) and these are compared to a set of actual results loaded in from a SQL Server database (in column H).

My conditional format compares two cells using a formula like this ...
=IF(AND(H50<>E50,$B$2="Y",E50<>"*"),1,0)
H50 and E50 are the cells being compared.
$B$2 is a flag to determine whether or not SHOW the difference (using a pattern of red)
The "*" test is there to allow certain values to be ignored as they are known to always be different, timestamps for example.

What I am seeing is a numeric zero in E50 being compared to a NULL (empty cell) in H50 and being considered (by Excel) as identical.

I COULD alter the underlying data to make all cells text but this would be difficult to achieve across the project (hundreds of workbooks).

Can anyone help with a method of getting this compare to work "properly"?

Thanks in advance.