Wednesday, November 12, 2014

Truly Blank?

Today’s topic may initially seem a bit obscure, but let me assure you, if you are ever faced with some odd results from what appears to be routine data, it is something to consider.  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 immediately know if blank cells are truly blank is due to the fact there are several ways of Hiding Data through:

o   The use of identically-colored fonts

o   Empty-string results of a formula

o   Masking the data with the use of Custom Formatting (three semicolons: ;;; )

It can cause Mayhem 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) 

o   If the cell is Blank, it will return True; if it is Not Blank, it will return False

o   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")

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

By determining if your cells are Truly Blank, you can help Avoid Quirky Results on your worksheet.  And, as Martha Stewart might say, “That’s a good thing…

No comments: