How to Find Blank Cells in Excel (8 Easy Ways)

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.


Download Practice Workbook

You can download the practice workbook from here.


8 Easy Ways to Find Blank Cells in Excel

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. Find Blank Cells in Excel with Go To Special Dialogue Box

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.

Find Blank Cells in Excel with Go To Special Dialogue Box

So, let’s see the steps to find blank cells using the “Go to Special” option.

STEPS:

  • Firstly, select cell range (B4:E9).

Find Blank Cells in Excel with Go To Special Dialogue Box

  • 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”.

Find Blank Cells in Excel with Go To Special Dialogue Box

  • 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 (8 Ways)


2. Use COUNTBLANK Function to Find Blank Cells in Excel

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.

Use COUNTBLANK Function to Find Blank Cells in Excel

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)

Use COUNTBLANK Function to Find Blank Cells in Excel

  • 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 (With Examples)


3. Find 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.

Find Blank Cells with Excel COUNTIF Function

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,"")

Find Blank Cells with Excel COUNTIF Function

  • 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 (7 Methods)


4. Apply Conditional Formatting to Highlight Blank Cells in Excel

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.

Apply Conditional Formatting to Highlight Blank Cells in Excel

So, let’s see step-by-step the use of “Conditional Formatting” to find blank cells.

STEPS:

  • Firstly, select cell range (B4:E9).

Apply Conditional Formatting to Highlight Blank Cells in Excel

  • 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”.

Apply Conditional Formatting to Highlight Blank Cells in Excel

  • 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.

Apply Conditional Formatting to Highlight Blank Cells in Excel

  • 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 (4 Fruitful Ways)


Similar Readings:


5. Identify 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.

Identify Blank Cells with ISBLANK Function in Excel

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)

Identify Blank Cells with ISBLANK Function in Excel

  • Next, press Enter.
  • We get the value FALSE in cell D5 as cell C5 is not blank.

Identify Blank Cells with ISBLANK Function in Excel

  • 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 (10 Easy Ways)


6. Detect 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.

Detect Blank Cells Using Find Option

So, let’s see the steps to find blank cells using the Find option.

STEPS:

  • First, select the cell range (B4:E9).

Detect Blank Cells Using Find Option

  • 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

Detect Blank Cells Using Find Option

  • 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).

Read More: How to Find and Replace Blank Cells in Excel (4 Methods)


7. Apply Filter Option to Find Blank Cells in Excel 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.

Apply Filter Option to Find Blank Cells in Excel from Specific Column

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.

Apply Filter Option to Find Blank Cells in Excel from Specific Column

  • So, the above command applies a filter to the selected data range. We can see the filter icons in the header cells.

Apply Filter Option to Find Blank Cells in Excel from Specific Column

  • 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.

Related Content: How to Autofill Blank Cells in Excel with Value Above (5 Easy Ways)


8. Discover 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. Spot 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.

Spot First Blank Cell in Column with Formula

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)))

Spot First Blank Cell in Column with Formula

  • 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. VBA Code to Find First Blank Cell in Excel

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.

VBA Code to Find First Blank Cell in Excel

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.

VBA Code to Find First Blank Cell in Excel

  • 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.

VBA Code to Find First Blank Cell in Excel

  • 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 (4 Examples)


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

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo