Updating a spreadsheet is challenging enough to correct what you can see, but what you can’t see can also throw you off.
What I’m specifically thinking about is clearing the contents of a cell. First off, I’ll tell you that the correct way to do this is to select the cell and press the delete key. This will clear the contents of the cell and won’t leave you any surprises in the future. Now let me tell you the way that I tend to clear the contents of a cell. My fingers like to press the spacebar. That’s right, I select the cell and hit space. This replaces the contents of the cell with a space. Then, when you click away from that cell, it looks just like a blank cell.
So what’s the big deal, you ask? Well, it turns out that a blank cell (empty from delete) and a cell with a space are treated differently by functions in Excel. I know that there are those you might expect, =CountBlank() for example, but there are also sneakier ones. Just a little bit sneakier is the =if(test, True, False) function. If, as the test expression, you reference a cell with a space in it say D4, =if(D4, True, False) , you get #VALUE, but, if you have nothing in D4, you get False.
There are more examples of this where instead of an error message, Excel just keeps on going. Next week, we’ll take a look at an example of =HLOOKUP() playing tricks with cells with spaces versus cells which are blank. It should be fun.
See you then, and remember. Delete clears the contents of the cell, and cells with spaces will lead to errors later.
Posted by: Dan Vega, Software Engineer, RedRover Software Inc
