The ISBLANK Function in excel is used to check whether a cell is empty and return a Boolean value of TRUE if the cell is empty, and FALSE if the cell contains any data or formula. As a standalone function you may struggle to see the use of this function. But just like many other functions, its value shines through when combined with other functions such as the IF, AND, OR functions.
In today’s post, we would take you through all there is to know about the ISBLANK Function, along with some examples. Let’s begin our journey with the Syntax & Argument for the function.
Syntax & Argument
The syntax of the ISBLANK Function is as follows:
=ISBLANK(value)
The ISBLANK Function has just one argument which is the value. Let’s see what this is below.
- Value: Represents the cell or range to check for blankness.
Yup, that’s it, just one argument for the ISBLANK function. Let’s move on to the Usage notes.
Usage Notes
- The ISBLANK Function returns TRUE if a cell is blank, and False if it contains any value of formula. This means that even if a formula evaluates to an empty string (” “), the ISBLANK function would return a FALSE.
- The argument for the ISBLANK function can be a single cell or a range of cell. It is however important to enclose the range in parenthesis to ensure that the function evaluates each cell individually. For example the formula “=ISBLANK(A1:A5)” will return an array of TRUE or FALSE for each cell.
- The ISBLANK can be used with other functions for versatility in modeling. For example, we could combine the IF function with the ISBLANK function to check for blank cells and return a value if blank, we can also combine with the SUMPRODUCT function to perform calculations based on blank cells within a range.
ISBLANK Function Examples
Basic Example
Let’s assume that we want to check whether cell B5 is blank, and cell B5 contains no value at all, our function will return TRUE.
If on the other hand cell A5 has a value in it, and we check with the ISBLANK function, our formula will return FALSE.
ISBLANK and NOT Function Combination
If you are familiar with the English language, you would know that the word “NOT” is a negative word used to mean the opposite of something. In excel, it serves the same purpose, it is used to test whether the value in a cell is different from what the user specified within the function. Now combine that with the ISBLANK function, then it would be used to test if a cell is not blank.
To test if a cell is not blank, we only need to nest the ISBLANK function in a NOT function as seen below:
ISBLANK and IF Function Combination
The IF function allows the user to return a custom value if the logical test calculates to be true, and another value if the logical test calculates to be false. By using the ISBLANK function as part of the logical test for the IF statement, the user is able to test if a cell is empty and return a value if true and another value if false.
Now we have two cells (cells C3 and C4), one blank and the other with text. Let’s use the IF Function to return a value if the cells are blank and another value if the cells are not blank. Our value if blank would be empty and occupied if the cell is not blank. The formula to use is as follows:
Testing for Empty Strings
In the usage notes for the ISBLANK Function, we noted that the ISBLANK Function returns false if any value is found in a cell, even if it is a formula calculating to an empty string (” “). Visually, this is a blank value but the ISBLANK function sees it otherwise as it actually contains a formula that just returns a blank value. For example, the formula =IF(B2=”Apple”,”phone”,” “) returns an empty string if the value in cell B2 is not “Apple”. If the formula is inputted in say cell C2, the ISBLANK function would view cell B2 as not blank.
This might be an issue because we know cell C2 should return as blank, what then can we do? A way to ensure that we are able to segregate empty cells including empty strings is by using the IF & LEN Function. The LEN function counts the number of characters in a text string, see our detailed post on the LEN Function here.
This means that if we use the LEN formula below on our initial example of cell C2, it returns true.
Combine the above with the IF Function rather than the ISBLANK function for an accurate result of cells that are empty or not. The syntax with the IF function would be as follows:
