Blank cells in Excel

Microsoft Excel is spreadsheet software designed by Microsoft in 1987. It is used to perform arithmetic calculations in various fields like statistical, engineering and financial. Many times, we export data from different platforms to Excel. Sometimes, because of this there occurs formatting issues and even some cells are left blank. Or otherwise as well, while working with Excel is better to check and remove the blank cells.

Now the question arises, how to do it? This tutorial we will learn the various method that will quickly help us to find Blank cells in Excel.

What is a Cell in Excel?

Excel is represented in the form of a grid, and a single or separate grid is called a cell. A cell location in excel is indicated by its name, which is a combination of column name and row number. Usually, the column name comes first, followed by the row number.

In the above spreadsheet, the selected cell is referred to as D6. The column name is D, and the row number is 6.

What is a Blank cell?

“A cell which does not contain any alphabet or numeric values is called a blank cell.”

The above-indicated cells are blank.

How to check a blank Cell?

1. Combining IF and ISBLANK Function

Using the formula, it is easy to check whether a cell is blank or occupied. Using IF with the ISBLANK function helps to identify the empty cell.

Syntax

ISBLANK (value)

Below given are the steps to check a blank cell by combining IF and ISBLANK function:

1. Select the cell where you want to display the result.
2. Type the formula =IF (ISBLANK (cell name),”Blank”, ”Not blank”)
3. For example, in the below image, the C6 cell is checked by using the formula =IF (ISBLANK (C6), “Blank”, “Not blank”)
4. The result is displayed as “Not Blank” in the cell E6.
5. Drag and repeat this formula to check every cell with different cell name.

2. Using ISBLANK Function

The ISBLANK function is another method to check whether the cell is blank or non-blank

Syntax

ISBLANK (value)

Below given are the steps to check a blank cell by using ISBLANK function:

1. Select the cell where you want to display the result.
2. In the selected cell, type the formula as =ISBLANK (cell name)
3. For example, to check the cell B3, type the formula as =ISBLANK (B3) and press Enter
4. If the cell is blank, the result will be displayed as TRUE, if the cell is non-blank the result will be displayed as False.
5. Drag and repeat these steps to check the other cell

In the above chart, B3 and D2 are empty cells. Hence the result is true. The rest of the tables are filled with values therefore, the result is False.

3. Using Find Command

Using the Find command, one can check whether the cell is empty or occupied. Following are the steps to check a blank cell by using find command:

1. Select the desired cell range in the spreadsheet.
2. Press the shortcut keys Ctrl+F.
3. A dialog box will appear in the screen as shown below.
4. In that choose Find All option, and it will display the cell which is blank.

4. Excel Conditioning Format

• Select the range of cells.
• In the Style Tab choose Conditional Formatting > Highlight Cell Rules> More Rules.
• In that choose Format only cells that contain, and select Blank option in Format only with. A preview is shown with the desired colour.
• Press OK. The empty cells are displayed with the highlighted colour.

• The result is shown below.

5. CountBlank Function

CountBlank is a statistical function that is used to count the number of empty cells present within a selected range.

Syntax

COUNTBLANK (range)

Following are the steps to check a blank cell by using Countblank function in Excel:

• Select the cell range in the spreadsheet.
• Select the particular cell where the result can display.
• Enter the formula as =COUNTBLANK (cell range)
• Press Ok. The result will display in the selected cell.

• As a result, it has returned Zero as an output since there were no blank cell.

6. COUNTIF

The COUNTIF function is a statistical function. It is used to count the number of cells that meet a specified criterion.

Syntax

COUNTIF (range, criteria)

Below given are the steps to check a blank cell by using COUNTIF function:

• Select the cell where the result can display
• Type the formula COUNTIF (range, criteria) and Press Enter.
• The below image shows that the formula is typed in the result column.

• The number of blank cells is displayed in the result column.

7. SUMPRODUCT Function

The sum product function is used to perform sum calculations. It comprises subtraction, multiplication and division, and it serves as the measure of the selected range or arrays.

Syntax

=SUMPRODUCT (array1,[array2],[array 3],…)

1. Select the cell where you want to display the output
2. Type the formula as =SUMPRODUCT (--(cell range=””))>0
3. The result will be displayed as true as it contains empty cell