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