Monday, May 4, 2009

That Blank Look

Experienced Excel users know that the IF function is a powerful tool when wanting to return certain words based on the parameters you set within the formula. But what if you want the target cell to remain blank if certain conditions are not met?

The answer is so simple, it will make you laugh. You use quotation marks as with text, but just put a space between them.

=IF(A1 > 365,”Data Error!”,” ”)

In our example, the IF function can help maintain data entry integrity. If the value in cell A1 is greater than 365, “Data Error!” is displayed in the cell. If A1 does not exceed the parameter chosen, there is no need for the message, and the cell remains blank.

Sometimes that “Blank Look” is just what you need. (Just don’t try it when your boss is talking to you…).

1 comment:

Unknown said...

DON'T use a space, use null:
=IF(A1>365,"Data Error!","")
it's "" not " "

The cell is also not blank as the post says, it's a zero-length string. They're different.

Bob Umlas
Excel MVP