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

Method 1 – Use Go To Special Dialogue Box to Find Blank Cells in Excel

In the below 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. We’ll use to “Go to Special” method to find the blank cells in this dataset.

Find Blank Cells in Excel with Go To Special Dialogue Box

STEPS:

  • Select the cell range (B4:E9).

Find Blank Cells in Excel with Go To Special Dialogue Box

  • Go to the Home tab.
  • Select the option “Find & Select” from the Editing section of the Excel ribbon.
  • Choose “Go To Special” from the drop-down.

Find Blank Cells in Excel with Go To Special Dialogue Box

  • Check the Blanks option and press OK.
  • Alternatively, you can use a keyboard shortcut to perform the above methods. Press Ctrl + G to open the “Go-To” dialogue box. Next press Alt + S to open the “Go To Special” dialogue box before using Alt + K to check the Blanks option.

  • You can now see that the above command finds and selects all the blank cells in the cell range (B4:B9).


Method 2 – Use Excel COUNTBLANK Function to Find Blank Cells

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

STEPS:

  • Select cell D11.
  • Insert the following formula in that cell:
=COUNTBLANK(B4:E9)

Use COUNTBLANK Function to Find Blank Cells in Excel

  • Press Enter.
  • The above command returns value 7 in cell D11. This means there are 7 blank cells in the cell range (B4:E9).


Method 3 – Find Blank Cells with Excel COUNTIF Function

The COUNTIF  function is also a statistical function. It 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

STEPS:

  • Select cell D11.
  • Write down the following formula in that cell:
=COUNTIF(B4:E9,"")

Find Blank Cells with Excel COUNTIF Function

  • Press Enter.
  • You’ll get the number of blank cells 7 in cell D11.

Read More: How to Find & Count If a Cell Is Not Blank


Method 4 – Apply Excel Conditional Formatting to Highlight Blank Cells

“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 used in the previous article.

Apply Conditional Formatting to Highlight Blank Cells in Excel

STEPS:

  • Select the cell range (B4:E9).

Apply Conditional Formatting to Highlight Blank Cells in Excel

  • Go to the Home tab.
  • 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

  • You’ll see a new dialogue box named “New Formatting Rule”.
  • From the “Select a Rule Type” section, choose the option “Format only cells that contain”.
  • Select the option Blanks in the “Format only cells with”section.
  • Click on Format.

Apply Conditional Formatting to Highlight Blank Cells in Excel

  • The “Format Cells” dialog box will appear.
  • Go to Fill
  • Select any fill color from the available colors and press OK.

  • The above action gets us back to the “New Formatting Rule” dialog box.
  • Use the Preview box of the Format option to see the newly added fill color.
  • Press OK.

  • You can now see that all the blank cells in the cell range (B4:E9) are highlighted.

Read More: How to Highlight Blank Cells in Excel


Method 5 – Identify Blank Cells with ISBLANK Function in Excel

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

STEPS:

  • Select cell D5.
  • Input the following formula in that cell:
=ISBLANK(C5)

Identify Blank Cells with ISBLANK Function in Excel

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

Identify Blank Cells with ISBLANK Function in Excel

  • Select cell D5 again.
  • 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.
  • Copy the formula of cell D5 into the other cells by clicking on the plus (+) sign and dragging the Fill Handle down to cell D10. You can also double-click on the plus (+) sign to get the same result.

  • Release the mouse click.
  • You can see the above action returns TRUE if the cell is blank and FALSE if the cell is non-blank.


Method 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

STEPS:

  • Select the cell range (B4:E9).

Detect Blank Cells Using Find Option

  • 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

Detect Blank Cells Using Find Option

  • A new dialog box named “Find and Replace” will appear.
  • In that box, set the following values for the 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.

  • Click on “Find All”.

  • You can see the list of all blank cells from the selected range (B4:E9).


Method 7 – Apply an Excel Filter to Find Blank Cells from Specific Column

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

STEPS:

  • Select the cell range (B4:E9).
  • 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

  • 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

  • Click on the filtering icon of cell C4 with the value Monday.
  • Check the Blanks option from the menu.
  • Press OK.

  • You can now only see 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.


Method 8 – Discover the First Blank Cell in a Column

Method 8.1. – Spot the First Blank Cell in  a Column with a 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

STEPS:

  • 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

  • Press Enter if you are using Microsoft Excel 365. Otherwise press Ctrl + Shift + Enter.
  • You 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 finds 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.

Method 8.2 – Use VBA Code to Find the 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.

VBA Code to Find First Blank Cell in Excel

STEPS:

  • Right-click on the active sheet.
  • Click on the option “View Code” from the available options to open a blank VBA module.

VBA Code to Find First Blank Cell in Excel

  • 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
  • Click on Run or press F5 to run the code.

VBA Code to Find First Blank Cell in Excel

  • A new dialogue box will appear. Go to the input box named Range and insert the data range value ($B4:$C$15).
  • Press OK.

  • A message box shows us that the cell number of the first blank cell is $C$8.


Download Practice Workbook

You can download the practice workbook from here.


Related Articles


<< Go Back to Excel Cells | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mukesh Dipto
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo