Drawing a Blank

If you have ever faced some odd results from what appears to be routine data (and who hasn’t…), it is wise to consider if you have any hidden blank cells.  Let’s say that you have what Appears to be a number of Blank Cells in the range on which you are performing a calculation.  If you are getting strange results, you might ask yourself, “Are the blank cells actually blank?”  The truth is that it is Not Always Easy to know whether a cell or cells in Excel are Truly Blank!

The reason it is difficult to know with certainty, is because of the fact there are several ways of Hiding Data by:

• The use of identically-colored fonts
• Empty-string results of a formula
• Masking the data with the use of Custom Formatting (three semicolons: ;;; )

As any good database manager or analyst knows, this can cause Havoc with your calculations. To detect this Invisible Data, there are at least a couple of techniques.  Assuming your cell in question is A1, you can:

1.         Simply insert this Function in an adjacent cell:  =ISBLANK(A1) 

• If the cell is Blank, it will return True; if it is Not Blank, it will return False
• Copy the simple formula to include the rest of the range you are investigating

 2.         A second technique it the use an IF Statement as follows:  =IF(A1<>"","Not Blank", "Blank")

• This IF Statement obviously returns Blank or Not Blank
• You can then take the appropriate action with the Not Blank cells

By determining if your cells are Truly Blank, you can help prevent Strange and Unwanted Results on your worksheet.  So instead of Drawing a Blank, ask yourself, do these cells actually contain data?  Hmmmm?...