Reporting on empty fields

Workbooks includes filters and report criteria to allow you to check if a field ‘is blank’ or ‘is not blank’, but sometimes, you need to do this check within a calculated column. There are a couple of ways to do this.

First thing to note, is that empty field might be ‘blank’ or it might be ‘null’. A field is ‘null’ if its value has never been specified – it doesn’t even know if it is meant to be empty or not. It becomes ‘blank’ once it knows that it is meant to be empty. Therefore, when testing if a field is empty, you have to test if it is blank or null:

field = "" OR field IS NULL

To get a report to output 1 if the field is empty and 0 if not, you can use an IF statement:

IF(field = "" OR field IS NULL, 1, 0)

Workbooks provides a function to make this even easier, IS_BLANK:

IS_BLANK(field)

IS_BLANK will output 1 if the field is empty, and 0 if it is not empty. Therefore, to check if a field is not blank, you can use:

IF(field != "" AND field IS NOT NULL, 1, 0)
or
!IS_BLANK(field)