If a cell is blank

This tutorial shows how to test if a cell is blank (empty) and return a value if the test is True or False through the use of Excel formulas, with the IF and ISBLANK functions, or VBA

EXCEL FORMULA 1. If a cell is blank using the IF function

EXCEL

Hard coded formula
If a cell is blank
Cell reference formula
If a cell is blank
=IF(C5="","No","Yes")
=IF(C9="",$C$5,$C$6)
GENERIC FORMULA

=IF(cell_ref="", value_if_true, value_if_false)

ARGUMENTS
cell_ref: A cell that you want to check if it's blank.
value_if_true: Value to be returned if the cell that is being tested is blank.
value_if_false: Value to be returned if the cell that is being tested is not blank.

GENERIC FORMULA

=IF(cell_ref="", value_if_true, value_if_false)

ARGUMENTS
cell_ref: A cell that you want to check if it's blank.
value_if_true: Value to be returned if the cell that is being tested is blank.
value_if_false: Value to be returned if the cell that is being tested is not blank.

EXPLANATION

This formula uses the IF function with a test criteria of two double quotation marks (""), without any value inserted between them, to assess if a cell is blank and return a specific value. The expression ="" means "empty". If the cell is blank the formula will return a value that has been assigned as the true value, alternatively if the cell is not blank the formula will return a value assigned as the false value.
Click on either the Hard Coded or Cell Reference button to view the formula that has the return values directly entered into the formula or referenced to specific cells.
In this example column C contains the quantity of a specific product. Column D captures the formula that checks for blank cells in column C. If the cell is blank the formula will return a value of "No" (hard coded example) or value in cell C5 (cell reference example). If the cell is not empty, that is if the cell contains a value, the formula will return a value of "Yes" (hard coded example) or value in cell C6 (cell reference example).

If you are using a formula with values entered directly into it and want to return a numerical value, instead of a text value, you do not need to apply the double quotation marks around the values that are to be returned. For example, if you want to return number 1 if a cell is blank and 0 if a cell contains a value you can apply the following formula =IF(C5="",1,0).

EXCEL FORMULA 2. If a cell is blank using the IF and ISBLANK functions

EXCEL

Hard coded formula
If a cell is blank - Formula 2 - hard coded
Cell reference formula
If a cell is blank - Formula 2 - cell reference
=IF(ISBLANK(C5),"No","Yes")
=IF(ISBLANK(C9),$C$5,$C$6)
GENERIC FORMULA

=IF(ISBLANK(cell_ref), value_if_true, value_if_false)

ARGUMENTS
cell_ref: A cell that you want to check if it's blank.
value_if_true: Value to be returned if the cell that is being tested is blank.
value_if_false: Value to be returned if the cell that is being tested is not blank.

GENERIC FORMULA

=IF(ISBLANK(cell_ref), value_if_true, value_if_false)

ARGUMENTS
cell_ref: A cell that you want to check if it's blank.
value_if_true: Value to be returned if the cell that is being tested is blank.
value_if_false: Value to be returned if the cell that is being tested is not blank.

EXPLANATION

This formula uses a combination of the IF and ISBLANK functions to assess if a cell is blank and return a specific value. Unlike the first formula, which uses the double quotation marks ("") to test if the selected cell is blank, this formula uses the ISBLANK function. If the cell is blank the ISBLANK function will return TRUE, alternatively it will return FALSE.
Click on either the Hard Coded or Cell Reference button to view the formula that has the return values directly entered into the formula or referenced to specific cells.
In this example column C contains the quantity of a specific product. Column D captures the formula that checks for blank cells in column C. If the cell is blank the formula will return a value of "No" (hard coded example) or value in cell C5 (cell reference example). If the cell is not empty, that is if the cell contains a value, the formula will return a value of "Yes" (hard coded example) or value in cell C6 (cell reference example).

If you are using a formula with values entered directly into it and want to return a numerical value, instead of a text value, you do not need to apply the double quotation marks around the values that are to be returned. For example, if you want to return number 1 if a cell is blank and 0 if a cell contains a value you can apply the following formula =IF(ISBLANK(C5),1,0).

VBA CODE 1. If a cell is blank using the If Statement

VBA

Hard coded against single cell
Sub If_a_cell_is_blank()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank
If ws.Range("C5") = "" Then

ws.Range("D5") = "No"

Else

ws.Range("D5") = "Yes"

End If

End Sub

Cell reference against single cell
Sub If_a_cell_is_blank()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank
If ws.Range("C9") = "" Then

ws.Range("D9") = ws.Range("C5")

Else

ws.Range("D9") = ws.Range("C6")

End If

End Sub

Hard coded against range of cells
Sub If_a_cell_is_blank()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank across a range of cells with a For Loop
For x = 5 To 11

If ws.Cells(x, 3) = "" Then

ws.Cells(x, 4) = "No"

Else

ws.Cells(x, 4) = "Yes"
End If

Next x

End Sub

Cell reference against range of cells
Sub If_a_cell_is_blank()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank across a range of cells with a For Loop
For x = 9 To 15

If ws.Cells(x, 3) = "" Then

ws.Cells(x, 4) = ws.Range("C5")

Else

ws.Cells(x, 4) = ws.Range("C6")
End If

Next x

End Sub

KEY PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Cell to Test: Select the cell that you want to check if it's blank by changing the cell reference ("C5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the cells that you want to test if they are blank and return a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
True and False Results: In this example if a cell is blank the VBA code will return a value of "No". If a cell is not blank the VBA code will return a value of "Yes". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
NOTES
Note 1: If the cell that is being tested is returning a value of ("") this VBA code will identify the cell as blank.
Note 2: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
KEY PARAMETERS
Output Range: Select the output range by changing the cell reference ("D9") in the VBA code.
Cell to Test: Select the cell that you want to check if it's blank by changing the cell reference ("C9") in the VBA code.
Worksheet Selection: Select the worksheet which captures the cells that you want to test if they are blank and return a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
True and False Results: In this example if a cell is blank the VBA code will return a value stored in cell C5. If a cell is not blank the VBA code will return a value stored in cell C6. Both of these values can be changed to whatever value you desire by either referencing to a different cell that captures the value that you want to return or change the values in those cells.
NOTES
Note 1: If the cell that is being tested is returning a value of ("") this VBA code will identify the cell as blank.
KEY PARAMETERS
Output and Test Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (5 to 11). This example assumes that both the output and the associated test cell will be in the same row.
Test Column: Select the column that captures the cells that are to be tested by changing number 3, in ws.Cells(x, 3).
Output Column: Select the output column by changing number 4, in ws.Cells(x, 4).
Worksheet Selection: Select the worksheet which captures the cells that you want to test if they are blank and return a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
True and False Results: In this example if a cell is blank the VBA code will return a value of "No". If a cell is not blank the VBA code will return a value of "Yes". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
NOTES
Note 1: If the cell that is being tested is returning a value of ("") this VBA code will identify the cell as blank.
Note 2: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
KEY PARAMETERS
Output and Test Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (9 to 15). This example assumes that both the output and the associated test cell will be in the same row.
Test Column: Select the column that captures the cells that are to be tested by changing number 3, in ws.Cells(x, 3).
Output Column: Select the output column by changing number 4, in ws.Cells(x, 4).
Worksheet Selection: Select the worksheet which captures the cells that you want to test if they are blank and return a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
True and False Results: In this example if a cell is blank the VBA code will return a value stored in cell C5. If a cell is not blank the VBA code will return a value stored in cell C6. Both of these values can be changed to whatever value you desire by either referencing to a different cell that captures the value that you want to return or change the values in those cells.
NOTES
Note 1: If the cell that is being tested is returning a value of ("") this VBA code will identify the cell as blank.

VBA CODE 2. If a cell is blank using IsEmpty

VBA

Hard coded against single cell
Sub If_a_cell_is_blank_using_IsEmpty()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank
If IsEmpty(ws.Range("C5")) Then

ws.Range("D5") = "No"

Else

ws.Range("D5") = "Yes"

End If

End Sub

Cell reference against single cell
Sub If_a_cell_is_blank_using_IsEmpty()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank
If IsEmpty(ws.Range("C9")) Then

ws.Range("D9") = ws.Range("C5")

Else

ws.Range("D9") = ws.Range("C6")

End If

End Sub

Hard coded against range of cells
Sub If_a_cell_is_blank_using_IsEmpty()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank across a range of cells with a For Loop
For x = 5 To 11

If IsEmpty(ws.Cells(x, 3)) Then

ws.Cells(x, 4) = "No"

Else

ws.Cells(x, 4) = "Yes"
End If

Next x

End Sub

Cell reference against range of cells
Sub If_a_cell_is_blank_using_IsEmpty()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank across a range of cells with a For Loop
For x = 9 To 15

If IsEmpty(ws.Cells(x, 3)) Then

ws.Cells(x, 4) = ws.Range("C5")

Else

ws.Cells(x, 4) = ws.Range("C6")
End If

Next x

End Sub

KEY PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Cell to Test: Select the cell that you want to check if it's blank by changing the cell reference ("C5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the cells that you want to test if they are blank and return a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
True and False Results: In this example if a cell is blank the VBA code will return a value of "No". If a cell is not blank the VBA code will return a value of "Yes". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
NOTES
Note 1: If the cell that is being tested is returning a value of ("") this VBA code will identify the cell as not blank.
Note 2: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
KEY PARAMETERS
Output Range: Select the output range by changing the cell reference ("D9") in the VBA code.
Cell to Test: Select the cell that you want to check if it's blank by changing the cell reference ("C9") in the VBA code.
Worksheet Selection: Select the worksheet which captures the cells that you want to test if they are blank and return a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
True and False Results: In this example if a cell is blank the VBA code will return a value stored in cell C5. If a cell is not blank the VBA code will return a value stored in cell C6. Both of these values can be changed to whatever value you desire by either referencing to a different cell that captures the value that you want to return or change the values in those cells.
NOTES
Note 1: If the cell that is being tested is returning a value of ("") this VBA code will identify the cell as not blank.
KEY PARAMETERS
Output and Test Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (5 to 11). This example assumes that both the output and the associated test cell will be in the same row.
Test Column: Select the column that captures the cells that are to be tested by changing number 3, in ws.Cells(x, 3).
Output Column: Select the output column by changing number 4, in ws.Cells(x, 4).
Worksheet Selection: Select the worksheet which captures the cells that you want to test if they are blank and return a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
True and False Results: In this example if a cell is blank the VBA code will return a value of "No". If a cell is not blank the VBA code will return a value of "Yes". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
NOTES
Note 1: If the cell that is being tested is returning a value of ("") this VBA code will identify the cell as not blank.
Note 2: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
KEY PARAMETERS
Output and Test Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (9 to 15). This example assumes that both the output and the associated test cell will be in the same row.
Test Column: Select the column that captures the cells that are to be tested by changing number 3, in ws.Cells(x, 3).
Output Column: Select the output column by changing number 4, in ws.Cells(x, 4).
Worksheet Selection: Select the worksheet which captures the cells that you want to test if they are blank and return a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
True and False Results: In this example if a cell is blank the VBA code will return a value stored in cell C5. If a cell is not blank the VBA code will return a value stored in cell C6. Both of these values can be changed to whatever value you desire by either referencing to a different cell that captures the value that you want to return or change the values in those cells.
NOTES
Note 1: If the cell that is being tested is returning a value of ("") this VBA code will identify the cell as not blank.

VBA CODE 3. If a cell is blank using vbNullString

VBA

Hard coded against single cell
Sub If_a_cell_is_blank_using_vbNullString()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank
If ws.Range("C5") = vbNullString Then

ws.Range("D5") = "No"

Else

ws.Range("D5") = "Yes"

End If

End Sub

Cell reference against single cell
Sub If_a_cell_is_blank_using_vbNullString()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank
If ws.Range("C9") = vbNullString Then

ws.Range("D9") = ws.Range("C5")

Else

ws.Range("D9") = ws.Range("C6")

End If

End Sub

Hard coded against range of cells
Sub If_a_cell_is_blank_using_vbNullString()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank across a range of cells with a For Loop
For x = 5 To 11

If ws.Cells(x, 3) = vbNullString Then

ws.Cells(x, 4) = "No"

Else

ws.Cells(x, 4) = "Yes"
End If

Next x

End Sub

Cell reference against range of cells
Sub If_a_cell_is_blank_using_vbNullString()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank across a range of cells with a For Loop
For x = 9 To 15

If ws.Cells(x, 3) = vbNullString Then

ws.Cells(x, 4) = ws.Range("C5")

Else

ws.Cells(x, 4) = ws.Range("C6")
End If

Next x

End Sub

KEY PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Cell to Test: Select the cell that you want to check if it's blank by changing the cell reference ("C5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the cells that you want to test if they are blank and return a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
True and False Results: In this example if a cell is blank the VBA code will return a value of "No". If a cell is not blank the VBA code will return a value of "Yes". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
NOTES
Note 1: If the cell that is being tested is returning a value of ("") this VBA code will identify the cell as blank.
Note 2: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
KEY PARAMETERS
Output Range: Select the output range by changing the cell reference ("D9") in the VBA code.
Cell to Test: Select the cell that you want to check if it's blank by changing the cell reference ("C9") in the VBA code.
Worksheet Selection: Select the worksheet which captures the cells that you want to test if they are blank and return a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
True and False Results: In this example if a cell is blank the VBA code will return a value stored in cell C5. If a cell is not blank the VBA code will return a value stored in cell C6. Both of these values can be changed to whatever value you desire by either referencing to a different cell that captures the value that you want to return or change the values in those cells.
NOTES
Note 1: If the cell that is being tested is returning a value of ("") this VBA code will identify the cell as blank.
KEY PARAMETERS
Output and Test Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (5 to 11). This example assumes that both the output and the associated test cell will be in the same row.
Test Column: Select the column that captures the cells that are to be tested by changing number 3, in ws.Cells(x, 3).
Output Column: Select the output column by changing number 4, in ws.Cells(x, 4).
Worksheet Selection: Select the worksheet which captures the cells that you want to test if they are blank and return a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
True and False Results: In this example if a cell is blank the VBA code will return a value of "No". If a cell is not blank the VBA code will return a value of "Yes". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
NOTES
Note 1: If the cell that is being tested is returning a value of ("") this VBA code will identify the cell as blank.
Note 2: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
KEY PARAMETERS
Output and Test Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (9 to 15). This example assumes that both the output and the associated test cell will be in the same row.
Test Column: Select the column that captures the cells that are to be tested by changing number 3, in ws.Cells(x, 3).
Output Column: Select the output column by changing number 4, in ws.Cells(x, 4).
Worksheet Selection: Select the worksheet which captures the cells that you want to test if they are blank and return a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
True and False Results: In this example if a cell is blank the VBA code will return a value stored in cell C5. If a cell is not blank the VBA code will return a value stored in cell C6. Both of these values can be changed to whatever value you desire by either referencing to a different cell that captures the value that you want to return or change the values in those cells.
NOTES
Note 1: If the cell that is being tested is returning a value of ("") this VBA code will identify the cell as blank.

RELATED TOPICS

Related Topic Description Related Topic and Description
How to test if a cell is blank in a range and return a value using Excel and VBA methods
How to test if a cell is not blank and return a value using Excel and VBA methods
How to test if a cell is not blank in a range and return a value using Excel and VBA methods
How to count cells that are blank using Excel and VBA methods
How to count cells that are not blank using Excel and VBA methods

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE
The Excel ISBLANK function is used to check if a cell is empty