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.

**Table of Contents**hide

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

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 (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**.

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

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

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

**Similar Readings:**

**Null vs Blank in Excel****How to Remove Blank Lines in Excel (8 Easy Ways)****Return Value if Cell is Blank (12 Ways)****How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas****Set Cell to Blank in Formula in Excel (6 Ways)**

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

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

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

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

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.

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

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

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