How to Identify Blank Cells in Excel

Find the technique you need

When developing formulas in Excel you often need to know when a cell is blank. There are different ways to check if a cell is blank, the one you chose will depend on what you are trying to achieve.

There are two types of blanks cells. The first is an empty cell. The second is a cell that has a formula that returns a blank cell (see examples below). Depending on what you are trying to do, you may need to treat these two types of blank cell differently.

Formulas that return a blank cell

Below are examples of formulas that can return a blank cell.

=IF(A1=0,"",A1)
=IFERROR(A1/B1,"")

The first formula checks if A1 is zero. If it is, then it displays a blank cell using

""

Otherwise it displays the content of A1. Using two quotation marks is how you instruct Excel to display a blank cell.

The second formula uses the IFERROR function. This handles all of Excel’s functions. If A1/B1 returns an error then a blank cell is displayed. If not, the calculated result of A1/B1 is displayed.

Checking for Blankness

There are three common ways to check if a cell is blank. Cell A1 is used as the cell to check in the examples below.

1. ISBLANK Function

Excel has a built-in function to check for blankness. The ISBLANK function returns a TRUE or a FALSE. It will only return TRUE if the cell being examined is empty. A cell that contains a formula is not blank and hence will return FALSE.

Formula example

=IF(ISBLANK(A1), "Blank","Not Blank")

2. Check for Two Quotation Marks

You can compare the cell with two quotation marks to check if it is blank. I use this technique most often. It treats an empty cell and a cell with a formula returning a blank cell the same.

=IF(A1="","Blank","Not Blank")

3. Check for Zero Length

You can use the LEN function to determine how long the entry in the cell is. If it equals zero it means the cell is blank or returning a blank cell. Empty cells and cells with a formula returning a blank are treated the same.

=IF(LEN(A1)=0,"Blank","Not Blank")

Warning: Some users insert a space character between the quotation marks to display a blank cell

" "

This is different to a bank cell and will return a length of 1. It will not be treated as blank by any of the techniques above.

See the table below for the results of using these techniques.

blank cell examples

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *