Certain formulas in Google Sheets require the referenced cell to contain a value, else an error will show up...
We can prevent this from happening by adding a way to scan whether the referenced cell contains a value or not. One way is to use the ISBLANK function.
In this tutorial, we will learn how to use the ISBLANK function on one cell, on an array of cells (in two ways), and together with a formula that requires a value on the reference cell.
A handy function you can use to check whether a cell is empty or not (without clicking on it) is the ISBLANK function. The ISBLANK function checks whether a cell is empty or not, returning either TRUE or FALSE:
=isblank(cell_reference)
It is useful because it actually allows you to check whether the cell is really empty or it contains either stray spaces or an apostrophe. The image below shows you how the result will look, depending on the contents of the cell.
While ISBLANK also allows an array of cells as the reference, it has known issues when setting an array of cells as the reference of the formula, giving FALSE value even if the cell has no apostrophe nor stray spaces.
Besides, if we apply the ISBLANK function to an array of cells, we would want to see individually which cells are really empty or just appear empty. This is what we will learn in this section.
There are two ways to do so. One is to insert ISBLANK function on one cell, reference the first cell of the array, and then copy-drag that cell to cover the entire range:
ISBLANK can be combined with other functions. The other way to generate the same array as above is to put it as an argument inside ARRAYFORMULA and specify the range of the array as the reference of ISBLANK function:
=ARRAYFORMULA(isblank(B3:E7))
The result would look like the same:
We can further spice up our spreadsheet by combining ISBLANK function with IF function. With our same example above, we want the Google Sheets to print “Something’s written” if the cell contains something while it will print “Really empty” if the cell is indeed empty.
To implement that, we write the following formula:
=IF(ISBLANK(B3)=TRUE, "Really empty", "Something's written")
And our array will look like this:
Finally we can now try using the ISBLANK to keep from getting an error from a formula that requires its reference cell to have a value.
For our final example, we wish to calculate the average sales per order of the teams listed. If either of the two values required, the total sales or the total number of orders is missing, the formula will not be able to correctly calculate the average sales per order metric.
We wish to have the formula indicate what is the missing quantity for each entry. To do so, we set the following conditions:
To implement this, we will use the IFS function that allows multiple conditions. We set one condition and one result for each of the three given scenarios above. Our formula will look like this:
=IFS(AND(ISBLANK(H19)=TRUE,ISBLANK(I19)=FALSE),"Missing Total Sales",
AND(ISBLANK(H19)=FALSE,ISBLANK(I19)=TRUE),"Missing Total Orders",
AND(ISBLANK(H19)=FALSE,ISBLANK(I19)=FALSE),H19/I19,
AND(ISBLANK(H19)=TRUE,ISBLANK(I19)=TRUE),
"Missing Total Sales and Total Orders")
And the result will look like this:
This is a pretty powerful combination of ISBLANK and IFS functions, but as you can see the formula already got quite long, with four conditions for two given reference cells. Nonetheless we have demonstrated how useful ISBLANK function is especially when combined with other functions in Google Sheets.
To help you further understand how ISBLANK functions work, you can check the sample sheet here: ISBLANK Sample Sheet.
We hope this article has helped you and given you a better understanding of how to use the ISBLANK function in Google Sheets. You might also like our articles on how to transpose data in Google Sheets and how to use the Google Seets UNIQUE function.
To optimize your workflow, we recommend reading our guide on how to build an email list from Google Sheets.
