Excel – If cell is not blank (Formula & VBA)

The general formula to check if a cell is not blank is the following:

=IF(A1<>"",result,"")

To take an action only when the cell is not blank (not empty), you can use a formula based on the IF function. In the example shown, column D holds task complete dates. The formula in cell E5 is:

=IF(D5<>"","Done","")

The <> symbol is a logical operator that means "not equal to", so the expression <>"" means "not nothing" or "not empty". When column D contains a value, the result is TRUE and IF returns "Done". When column D is empty, the result is FALSE and IF returns an empty string ("").

To display both "Done" and "Not done", you can adjust the formula like this:

=IF(D5<>"","Done","Not done")

With ISBLANK

We can use the ISBLANK function to test if a cell is empty or not.

For example, =ISBLANK(A1) will return TRUE if A1 is empty, and FALSE if A1 contains text a formula (even if the formula returns an empty string "").

It's probably best to think of ISBLANK to mean "is empty" since it can return FALSE when cells look blank but aren't. For example, if A1 contains a space character (" "), or a formula that returns an empty string (""), A1 will look blank, but ISBLANK(A1) will return FALSE in both cases.

The ISBLANK function returns TRUE when a cell is empty and FALSE if not. To use ISBLANK, you can rewrite the formula like this:

=IF(ISBLANK(F8),"","Done")

Notice the TRUE and FALSE results have been swapped. To keep the original sequence, maintain the original idea of <>"", you can add the NOT function:

=IF(NOT(ISBLANK(F8)),"Done","")

In the following example, to be sure that actually the selected cell is empty (even considering a blank space), we can use the IF, OR and TRIM function to be sure to consider a selected space as "empty", inserting, if TRUE, the Blank text or, if FALSE, display the content of the selected cell without any modifications:

=IF(OR(ISBLANK(A1),TRIM(A1)=""),"Blank",A1)

In this case, the TRIM function removes all leading and trailing spaces from a cell. If you have only spaces inside a cell, it will remove all of them.

With IsEmpty (VBA)

The ISEMPTY function can only be used in VBA code in Microsoft Excel. We can use the ISEMPTY function to test a worksheet cell or a variable. Let's look at both of these cases.

With a Worksheet Cell

If you wish to test whether a worksheet cell is empty in VBA, you can not use the worksheet function called ISBLANK. In VBA, you must use the ISEMPTY function.

Here is an example of how to test whether a worksheet cell is empty using the ISEMPTY function:

Sub TestCell()
   'Test if the value if cell is blank/empty
   If IsEmpty(Range("A1").Value) = True Then
      MsgBox "Cell is empty"
   End If
End Sub

In this example, we will test whether cell A1 is empty. If cell A1 is empty, the message "Cell A1 is empty" will be displayed.

With a Variable

The ISEMPTY function can also be used to test whether a variable has been initialized. If the variable has not been initialized, the ISEMPTY function will return true. Otherwise, the function it will return false.

Variable is Uninitialized

Let's first look at an example of when a variable has not been initialized:

Sub TestVariable()
   Dim LResult
   'Test if the variable has been initialized
   If IsEmpty(LResult) = True Then
      MsgBox "Variable has not been initialized."
   End If
End Sub

In this example, the variable called LResult has been declared, but has not been initialized with a value. As a result, the ISEMPTY function will return true and display the message "Variable has not been initialized".

Variable is Initialized

Now, we will modify the example above and initialize the LResult variable before calling the ISEMPTY function.

Sub TestVariable()
   Dim LResult
   'Initialize the variable called LResult
   LResult = "example.com is a great resource!"
   'Test if the variable has been initialized
   If IsEmpty(LResult) = True Then
      MsgBox "Variable has not been initialized."
   End If
End Sub

Since the LResult variable has now been initialized to the value "example.com is a great resource!", the ISEMPTY function will return false and the message box will not be displayed.

Highlight Blank Cells

In addition, you can use conditional formatting in Excel to highlight cells that are blank.

For example, select the range A1:H8.

On the Home tab, in the Styles group, click Conditional Formatting.

Click Highlight Cells Rules, More Rules.

Select Blanks from the drop-down list, select a formatting style and click OK.

And here it is the result:

Search by Keyword