Data Validation

Everything we do with Excel starts with data. So today I wanted to examine some cell contents a little closer.

There are times when we look at a cell and what we see is not what really is in the cell.

Take a look at the below example.

Value	Test	Formula
A	FALSE	=ISBLANK(A2)
B	FALSE	=ISBLANK(A3)
1	FALSE	=ISBLANK(A4)
2	FALSE	=ISBLANK(A5)
	FALSE	=ISBLANK(A6)
	TRUE	=ISBLANK(A7)

I want to test the values in my left column (column A) to see if there is a value in the cell or if the cell is empty. I can use the formula =ISBLANK to accomplish this.

As you can see the first 4 rows all have values in them and the formula returns False indicating that. However in the last two cells I get different results. Both cells appear to be empty but in reality only one of them is as indicated by the TRUE result.

Now there are a couple of reasons why a seemingly empty cell would return FALSE for the =isblank function.

First is that there is a value in the cell. Perhaps the font color of the text in the cell matches the background color of the cell giving the appearance of an empty cell.

The second and more common reason is that there is a space value in the cell (someone has hit the space bar and then the enter key. This also can occur when importing values from other data sources such as an Access database.

Take another look at the data. This time I want to do a VLookup for the letter B in my column A and return the value in column B. However my VLookup formula does not work. The reason is again due to a blank space after the B in cell A3. The lookup is searching for “B” and the value in the cell is “B “.

Value	Test	Vlookup	Formula
A	FALSE	#N/A	=VLOOKUP("B",A1:B7,2,FALSE)
B	FALSE
1	FALSE
2	FALSE
	FALSE
	TRUE

This vlookup error is quite common. Almost weekly I am asked to trouble shoot a coworkers worksheet because their vlookup does not work. 9 out of 10 times the problem lies with blank spaces to the right of the value(s) they are using in the look up.

Now you can avoid this common error by using the =Trim function to strip out blanks from both sides of your data.

A third but common occurrence is when a number is represented as a text. Again if we look at the data in column A, I show two numbers (1 and 2). However in this instance, 1 is not a number but a text. The formula ISText fails for the number 2 since 2 is a number in my data.

Value	Blank Test	Vlookup	Formula	Text Value	Formula
A	FALSE	#N/A	=VLOOKUP("B",A1:B7,2,FALSE)	TRUE
B	FALSE			TRUE
1	FALSE			TRUE	=ISTEXT(A4)
2	FALSE			FALSE	=ISTEXT(A5)
	FALSE			TRUE
	TRUE			FALSE

So what does all this mean? Well just because it looks like a duck and quacks like a duck, it does not mean it is a duck. So when you have a formula that you are sure should work. Start by taking a closer look at your data to ensure that what you see is what you really have.