NULL <> ISBlank()

  • Article

While Excel is definitely the most widely used BI end user tool,  SQL skills are often needed to get data from a source and into Excel be that Excel stand alone or with an add-on like PowerPivot. There are many big differences between Excel expressions and SQL but it’s the little ones that floor us sometimes in this case the difference between the way BLANK works in Excel and NULL in SQL:

In SQL:

NULL + 3 = NULL

NULL + ‘Deep Fat’ = NULL

However in Excel if I have a cell with nothing in it say AF1 such that =ISBLANK() returns TRUE or 1 then:

=AF1 + 3 returns 3

=AF1 & “Deep Fat”  returns “Deep Fat”

This divergence goes back a long way and to change it now would be like trying to get us to drive and the same side as the road as the French. 

Anyway just FYI as it caught a few of us out the other day but I am not naming any other names!