This tutorial shows how to find blank cells in excel. Sometimes we receive excel files from someone or import that file from a database. To work properly we have to check if there is any blank cell in that particular excel file. If our dataset is small we can identify the blank cells just by looking at the dataset but we can not do this for a dataset that is huge. In this article, we will learn about how to find blank cells in excel in 8 easy ways.
Throughout this article, we will cover 8 different ways to find blank cells in excel. Go through all the methods and compare among them so that you can select a suitable way for your work.
1. Using Go To Special Dialogue Box to Find Blank Cells in Excel
First and foremost, we will find blank cells from a dataset with the “Go To Special” dialogue box. In the following screenshot, we have the attendance of 6 students for 3 days. We can see their attendance status as Present. The blank cell means that the student was absent on that day.
So, let’s see the steps to find blank cells using the “Go to Special” option.
STEPS:
- Firstly, select cell range (B4:E9).
- Secondly, go to the Home tab.
- Thirdly, select the option “Find & Select” from the Editing section of the excel ribbon.
- Next, from the drop-down select “GoTo Special”.
- Then, a new dialogue box named “Go To Special” will appear.
- After that, check the option Blanks and press OK.
- Also, we can use a keyboard shortcut to perform the above methods. First, press Ctrl + G to open the “Go-To” dialogue box. Next press Alt + S to open the “Go To Special” dialogue box. Then, press Alt + K to check the option Blanks.
- Lastly, we can see that the above command finds and selects all the blank cells in the cell range (B4:B9).
Read More: How to Ignore Blank Cells in Range in Excel
2. Using Excel COUNTBLANK Function to Find Blank Cells
In the second method, we will use the COUNTBLANK function to find blank cells in excel. The COUNTBLANK function is classified as a statistical function in Excel. COUNTBLANK is a function that finds and counts the number of blank cells in a range of cells. To illustrate this method we will continue with the same dataset that we used in the previous method but we will return the number of blank cells in cell D11.
Let’s see the steps to use the COUNTBLANK function to find blank cells in excel.
STEPS:
- First, select cell D11. Insert the following formula in that cell:
=COUNTBLANK(B4:E9)
- Next, press Enter.
- Finally, the above command returns value 7 in cell D11. This means there are 7 blank cells in the cell range (B4:E9).
Related Content: Find, Count and Apply Formula If a Cell is Not Blank
3. Finding Blank Cells with Excel COUNTIF Function
We can also find blank cells from a data range by using the COUNTIF function. The COUNTIF function is also a statistical function. The COUNTIF function generally counts cells that satisfy a specific criterion. To illustrate this method we will use the same dataset that we used before.
Let’s see the steps to perform this action.
STEPS:
- In the beginning, select cell D11. Write down the following formula in that cell:
=COUNTIF(B4:E9,"")
- After that, press Enter.
- So, we get the number of blank cells 7 in cell D11.
Read More: Find If Cell is Blank in Excel
4. Applying Excel Conditional Formatting to Highlight Blank Cells
We can apply “Conditional Formatting” to find and highlight blank cells from a dataset. “Conditional Formatting” is used to apply formatting to a cell or range of cells. In this example, we will highlight blank cells from the dataset that we use in the previous article.
So, let’s see step-by-step the use of “Conditional Formatting” to find blank cells.
STEPS:
- Firstly, select cell range (B4:E9).
- Secondly, go to the Home tab.
- Then, click on the option “Conditional Formatting” from the ribbon. From the drop-down menu select the option “New Rule”.
- Thirdly, we can see a new dialogue box named “New Formatting Rule”.
- Next, from the “Select a Rule Type” section select the option “Format only cells that contain”.
- Then, select the option Blanks in the section “Format only cells with”.
- After that, click on Format.
- So, one more dialogue box named “Format Cells” will appear.
- Then, go to the Fill Select any fill color from the available colors and press OK.
- After that, the above action gets us back to the “New Formatting Rule” dialogue box.
- If we notice the Preview box of the Format option we can see the newly added fill color.
- Then press OK.
- Lastly, we can see that all the blank cells in the cell range (B4:E9) are highlighted now.
Read More: Highlight Blank Cells in Excel
Similar Readings
- Null vs Blank in Excel
- How to Remove Blank Lines in Excel
- Return Value if Cell is Blank
- How to Calculate in Excel If Cells are Not Blank
- Set Cell to Blank in Formula in Excel
5. Identifying Blank Cells with ISBLANK Function in Excel
In this method, we will identify blank cells with the ISBLANK function. The ISBLANK function is considered an information function. This function checks whether a cell is blank or not. It returns TRUE if a cell is blank and FALSE if the cell is non-blank. We are using the following dataset for this example which is slightly modified from the previous one.
So, just go through the steps to perform this action.
STEPS:
- In the beginning, select cell D5. Input the following formula in that cell:
=ISBLANK(C5)
- Next, press Enter.
- We get the value FALSE in cell D5 as cell C5 is not blank.
- Then, select cell D5. Drop the mouse pointer to the selected cell’s bottom right corner, where it will convert into a plus (+) sign, as shown in the image below.
- After that, to copy the formula of cell D5 in other cells click on the plus (+) sign and drag the Fill Handle down to cell D10. We can also double-click on the plus (+) sign to get the same result.
- Now, free the mouse click.
- Finally, we can see the above action returns TRUE if the cell is blank and FALSE if the cell is non-blank.
Read More: How to Remove Blank Cells in Excel
6. Detecting Blank Cells Using Find Option
We can also find blank cells of a dataset by using the Find option from the excel ribbon. To illustrate this method we will use the following dataset. You can take a look at the below screenshot of the dataset.
So, let’s see the steps to find blank cells using the Find option.
STEPS:
- First, select the cell range (B4:E9).
- Next, go to the Home tab.
- Select the option “Find & Select” from the Editing section of the ribbon.
- From the drop-down select the option Find
- In short: go to Home > Find & Select > Find
- Then, a new dialogue box named “Find and Replace” will appear.
- After that, in that box set the following values for mentioned options:
Find what: Keep this box blank.
Within: Select the option Sheet.
Search: Select the option “By Rows”.
Look in: Select the option Values.
- Now, click on “Find All”.
- Finally, we can see the list of all blank cells from the selected range (B4:E9).
7. Applying Excel Filter to Find Blank Cells from Specific Column
Another way to find blank cells from a worksheet is to apply the Filter option. You can use the Filter option to find blank cells for a specific column. This method is not perfect for finding blank cells from any particular data range. To explain this method we will continue with the dataset that we used in the previous example.
Let’s see the steps to apply the Filter option to find blank cells in excel.
STEPS:
- Firstly, select the cell range (B4:E9).
- Secondly, go to the Home tab.
- Select the option Filter from the “Sort & Filter” section of the excel ribbon.
- So, the above command applies a filter to the selected data range. We can see the filter icons in the header cells.
- Next, click on the filtering icon of cell C4 with value Monday.
- Then, check only the Blanks option from the menu.
- Now, press OK.
- Lastly, we can see only the blank cells in column C.
NOTE:
This method finds blank cells for individual columns instead of the whole data range. So, if we want to find the blank cells from the column with the heading Tuesday we have to filter this column individually for blank cells like the previous example.
8. Discovering First Blank Cell in Column
Suppose, we have a column with so many blank cells in our dataset. But instead of finding all blank cells together we just want to find the first blank cell from the column. In this segment, we will demonstrate two ways to find the first blank cell from a single column
8.1. Spotting First Blank Cell in Column with Formula
In this method, we will use a formula to find the first blank cell from a column. We have given a screenshot below of our dataset. There are blank cells in column C. We will find the first blank cell from that column and will return the cell number in cell G9.
So, let’s see the steps to perform this action.
STEPS:
- First, select cell G7. Insert the following formula in that cell:
=MIN(IF(C4:C15="",ROW(C4:C15)))
- Next, press Enter if you are using Microsoft Excel 365 otherwise press Ctrl + Shift + Enter.
- Finally, we can see that the row number of the first blank cell is 8.
🔎 How Does the Formula Work?
- IF(C4:C15=””,ROW(C4:C15)): This part find out the row numbers of all blank cells in range (C4:C15).
- MIN(IF(C4:C15=””,ROW(C4:C15))): From all the row numbers of blank cells this part returns the minimum row number 8 which is also the first blank cell.
8.2. Using VBA Code to Find First Blank Cell
The use of VBA (Visual Basic for Application) code is another convenient way to find the first blank cell in excel. To explain this method we will continue with our previous dataset.
So, let’s see the steps to find the first blank cell using the VBA code.
STEPS:
- In the beginning, right-click on the active sheet.
- Next, click on the option “View Code” from the available options.
- So, the above command opens a blank VBA module.
- Insert the following code in the blank module:
Sub First_Blank_Cell()
Dim rg As Range
Dim rngWork As Range
On Error Resume Next
xTitleId = "First Blank Cell"
Set rngWork = Application.Selection
Set rngWork = Application.InputBox("Range", xTitleId, rngWork.Address, Type:=8)
For Each rg In rngWork
If rg.Value = "" Then
MsgBox "Blank Cell in " & rg.Address
End If
Next
End Sub- Now click on Run or press F5 to run the code.
- Then, a new dialogue box will appear. Go to the input box named Range and insert the data range value ($B4:$C$15).
- Press OK.
- Finally, a message box shows us that the cell number of the first blank cell is $C$8.
Read More: Excel VBA: Find the Next Empty Cell in Range
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
In the end, this tutorial will show you how to find blank cells in excel. Use the practice worksheet that comes with this article to put your skills to the test. If you have any queries, please leave a comment below. We will try our best to respond to you as quickly as possible. Keep an eye out for more creative Microsoft Excel solutions in the future.
Related Articles
- How to Check If Cell Is Empty in Excel
- How to Delete Blank Cells and Shift Data Up in Excel
- If Cell is Blank Then Show 0 in Excel
- How to Apply Conditional Formatting in Excel If Another Cell Is Blank
- If a Cell Is Blank then Copy Another Cell in Excel
- How to Remove Unused Cells in Excel
- How to Remove Blank Cells from a Range in Excel
- How to Make Empty Cells Blank in Excel
- How to Deal with Blank Cells That Are Not Really Blank in Excel
- Excel VBA: Check If Multiple Cells Are Empty
- How to Find Blank Cells Using VBA in Excel
- Return Non Blank Cells from a Range in Excel
- Data Clean-Up Techniques: Fill Blank Cells in Excel
- Excel VBA: Delete Row If Cell Is Blank
- How to Delete Blank Cells and Shift Data Left in Excel
