In this article, we’ll look at one interesting topic: “how to check if a cell in Excel is empty“. When working with big amounts of data, these empty cells can cause a lot of issues.
We are going to look at seven different ways we can check if an excel cell is empty or not.
Checking if an excel cell is empty with
- Conditional formatting
- IF function
- ISBLANK function
- Excel VBA Macros
- Find command
- Combining IF with the ISBLANK function
- Multiple functions such as
Let’s get right to It. We begin with.
Method 1: Checking if an excel cell is empty with Conditional formatting
Follow the steps below carefully
- Select the cells or the range of cells that you wish to work on to search the empty cells.
- Click on the home tab and select Conditional Formatting
- Click on Highlight cells Rules
- Click on more rules from the popup menu
- Select Format only cells that contain
- Click on Blanks
- Choose the fill color of your choice to make the cells visible from the format option and click on OK
- The end results are displayed below as all the blank cells in the selected range are highlighted in brown
Method 2: Checking if an excel cell is empty with the IF function
The IF function allows us to compare a value to what we expect in a logical manner.
The formula for this function is
[code] IF(logical_test, value_if_true, [value_if_false]) [/code]
What this means is
logical_test – The condition we want to test.
value_if_true – The value that we want to return if the result of logical_test is TRUE.
Value_if_false – The value that you want to return if the result of logical_test is FALSE.
The steps are as follows:
Click on cell B2 to activate it
Input the formula: =IF(B5=””,”Blank”,”Not Blank”) and press ENTER
- Drag the fill handle down to apply the function to the rest of the cells
If you want to learn more about Microsoft Excel, then check out the list of posts that have been published under the Microsoft Excel Category
Method 3: Checking if an excel cell is empty with ISBLANK Function
Based on two states, the ISBLANK function returns TRUE or FALSE. Show TRUE if the argument is empty, else FALSE.
The formula used is: ISBLANK(value)
- Value – This value will be tested. This may be empty or filled with text or logical value, etc.
- Click on the B2 cell
- Input the ISBLANK function and press enter
- Select A2 as the argument. This is how it will be
Now drag the fill handle down to the last cell to apply the formula to the rest of the cells.
Now we can see that there is just one cell that is empty, and the result for that cell is TRUE. However, because the rest of the cells aren’t empty, they indicate FALSE.
Method 4: Checking if an excel cell is empty with Excel VBA Macros
- First, go to the Home tab.
- Select the Developer option from the main tab.
- From the commands select Marcos.
We will get a Dialog box
Name the Macro as Checking_empty_cells and the click on create
Now, type the below code in the VBA command module and press F5 to run the code. Or function key (fn) + f5[code]Sub checking_empty_cells() Dim i As Long Dim c As Long Dim MRange As Range Dim MCell As Range Set MRange = Range(“A2:A11”) For Each MCell In MRange c = c + 1 If IsEmpty(MCell) Then MCell.Interior.Color = RGB(255, 87, 87) i = i + 1 End If Next MCell MsgBox “No. of Empty Cells are ” & i & “.” End Sub [/code]
The result after running the code is displayed below
We can see the empty cells are 3.
Method 5: Checking if an excel cell is empty with FIND COMAND
The Find command can also be used to see if a cell in a worksheet is empty. Before we begin, we’ll make a few changes to the previous dataset.
The steps below will guide you to complete this
select the range of cells from which you want to check for empty cells
Leave the Find what box empty
Click on Find All
As listed above, we can see the empty cells are A3, A6, and A9
Method 6: Checking if an excel cell is empty by Combining IF with ISBLANK function
Click on cell B2Type the formula: =IF(ISBLANK(A1), “Blank”, “Not Blank”) into the formula bar
And click on enter
Drag the fill handle below to apply the function to the rest of the cells
Method 7a: Checking if an excel cell is empty with COUNTBLANK
COUNTBLANK is a statistical tool that is used to count the number of cells that are empty in a range of cells.
The formula it uses is: COUNTBLANK(range)
Click on B1 to input the COUNTBLANK formula: =COUNTBLANK(A1:A10)
Click on enter
We can see from the above shot that the number of empty cells in the range A1:A10 is 3.
Method 7b: Checking if an excel cell is empty with SUMPRODUCT
SUMPRODUCT is a function that adds all of the products together. Originally, the SUMPRODUCT function was used to execute a sum operation. The sum of the products of supplied ranges or arrays is produced. Subtraction and division with multiplication are also included.
The formula used is: =SUMPRODUCT(array1, [array2], [array3], …)
The steps below will guide you on how to use this function
Click on cell B1 and input the formula: =SUMPRODUCT(–(A1:A10=””))>0
Click on OK
Method 7c: Checking if an excel cell is empty with COUNTIF
It calculates the number of cells that fulfill a condition.
COUNTIF is a function that counts how many times something happens.
Compare to blank in the A1:A10 range.
If there are any blanks, display TRUE; otherwise, display FALSE. The formula is as follows:
And click on enter
We only identified three vacant cells after using this formula, and that number is displayed.