How to use the ISBLANK function
What is the ISBLANK function?
The ISBLANK function returns TRUE if the argument is an empty cell, returns FALSE if not.
Table of Contents
1. Introduction
What is a blank?
A blank refers to an empty cell, however, you can create a formula that returns nothing so it looks like the cell is empty when it is actually populated with a formula.
You can also populate a cell with a single or multiple blanks or spaces which are not visible. This makes the cell look empty but it is not.
What is an empty cell?
An empty cell is a cell that contains nothing at all.
What is a Boolean value?
A Boolean value in Excel is a value that can only be TRUE or FALSE. It represents binary logic and is the result of a logical expression using logical operators or a result of a few Excel functions that I'll discuss below.
Mastering Boolean logic and logical expressions is key to manipulating data and controlling workflow in Excel.
Other IS functions
| Excel Function | Description |
|---|---|
| ISBLANK(value) | Returns TRUE if the value is empty, FALSE otherwise |
| ISERR(value) | Returns TRUE if the value is any error value except #N/A, FALSE otherwise |
| ISERROR(value) | Returns TRUE if the value is any error value, FALSE otherwise |
| ISEVEN(value) | Returns TRUE if the value is an even number, FALSE for odd numbers |
| ISFORMULA(reference) | Returns TRUE if the cell contains a formula, FALSE otherwise |
| ISLOGICAL(value) | Returns TRUE if the value is a logical value (TRUE/FALSE), FALSE otherwise |
| ISNA(value) | Returns TRUE if the value is the #N/A error, FALSE otherwise |
| ISNONTEXT(value) | Returns TRUE if the value is not text, FALSE if it is text |
| ISNUMBER(value) | Returns TRUE if the value is a number, FALSE otherwise |
| ISODD(value) | Returns TRUE if the value is an odd number, FALSE for even numbers |
2. Syntax
ISBLANK(value)
| value | Required. The cell you want to check if empty. |
3. Example 1
The image above shows different scenarios in column B and how the ISBLANK function handles them in column D. Column C contains what the cells in column B contain.
-
- Cell B3 is an empty blank cell, the ISBLANK function in cell D3 returns boolean value TRUE. Formula in cell D3:
=ISBLANK(B3)
- Cell B4 contains a formula which returns nothing specified by an equal sign and two double quotes. All formulas begin with an equal sign. Formula in cell D4:
=ISBLANK(B4)
Cell D4 returns FALSE, cell B4 is not blank.
- Cell B5 contains a space character which is of course not visible. Formula in cell D5:
=ISBLANK(B5)
Cell D5 returns FALSE, cell B4 is not empty blank.
- Cell B6 contains abc which is a text value. Formula in cell D6:
=ISBLANK(B6)
Cell D6 returns FALSE, cell B6 is not an empty blank cell.
- Cell B7 contains a formula. Formula in cell D7:
=ISBLANK(B6)
Cell D7 returns FALSE, cell B6 is not an empty blank cell.
- Cell B8 contains 5467 which is a numerical value. Formula in cell D8:
=ISBLANK(B6)
Cell D8 returns FALSE, cell B6 is not an empty blank cell.
- Cell B3 is an empty blank cell, the ISBLANK function in cell D3 returns boolean value TRUE. Formula in cell D3:
The ISBLANK function returns FALSE if a formula returns nothing, see picture above in cell B4 and B7. The ISBLANK function correctly identifies cells containing formulas as not empty even though they return nothing.
3.1 How to check if a formula returns nothing
The ISBLANK function returns FALSE if a cell contains a fomrula theat evaluates to "" nothing. If this is not desired then use the equal sign to check if a formula returns nothing, use the following formula:
This returns TRUE if a formula returns nothing "" and FALSE if a formula returns a value.
4. Example 2
To complete a report on a scientific study you need to quantify the instances of missing data. How would you approach this task?
Cells B3:B14 contain months from January to December. Cell range C3:F14 contains numerical values. Formula in cell H3:
This is an Excel 365 dynamic array formula, it spills values automatically to adjacent cells as far as needed. The output array in cell range H3:H14 contains integers that represent the number of blank empty cells on the same row.
The first row has 1 empty blank cell, cell H3 returns 1 which corresponds the number of empty cells in C3:F3.
Explaining fomrula
Step 1 - Specify argument in BYROW function
The BYROW function puts values from an array into a LAMBDA function row-wise.
Function syntax: BYROW(array, lambda(array, calculation))
BYROW(C3:F14,....)
The first argument is a cell reference to C3:F14. The BYROW passes the values in C3:F14 row-wise to the LAMBDA function.
Step 2 - Specify argument in LAMBDA function
The LAMBDA function build custom functions without VBA, macros or javascript.
Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)
BYROW(C3:F14,LAMBDA(a,...))
The argument in the LAMBDA function is a which is a variable. It will contain the values row by row.
Step 3 - Check if blank cells and convert boolean value to corresponding numerical value
We defined variable a in the step above, we can now use it to check if the values are empty blanks.
ISBLANK(a)*1 becomes ISBLANK({758,317,"",744})*1
becomes {FALSE, FALSE, TRUE, FALSE}*1
The SUM function ignores boolean values, we need to convert them to their numerical equivalents. This is easy simply multiply by 1.
{FALSE, FALSE, TRUE, FALSE}*1 returns {0,0,1,0}
Step 4 - Add numerical values and return a total by row
The SUM function allows you to add numerical values, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers.
Function syntax: SUM(number1, [number2], ...)
BYROW(C3:F14,LAMBDA(a,SUM(ISBLANK(a)*1)))
The sum function calculates a total per row, for example SUM(ISBLANK(a)*1) becomes
SUM({0,0,1,0}) and returns 1 which is the first value in the array. The BYROW function then reiterates these calculations using new values row-wise until all values have been processed.
It returns the following array: {1;2;1;2;3;0;3;4;1;3;2;0} displayed in cell range H3:H14 shown in the image above. The values correspond to the count of blank values in C3:F14 row by row.
'ISBLANK' function examples
In this blog post I will demonstrate methods on how to find, select, and deleting blank cells and errors. Why […]
This article demonstrates Excel formulas that allows you to list unique distinct values from a single column and sort them […]
This article demonstrates ways to list unique distinct values in a cell range with multiple columns. The data is not […]
Functions in 'Information' category
The ISBLANK function function is one of 19 functions in the 'Information' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form