Let’s say you work for a company that tracks employee attendance. You have a column where employees mark whether they attended a training session by writing “Yes” or leaving it blank if they didn’t.
To quickly see who missed the training, you can use the ISBLANK function. This function helps you find all the empty cells in the column, showing who didn’t mark their attendance.
What is the ISBLANK Function?
ISBLANK function in Google Sheets is an information function that tells you if a cell is empty. Think of it as a quick way to check if someone forgot to fill in their part of a form or a sheet. When you use ISBLANK, it looks at a specific cell and returns “TRUE” if the cell is empty and “FALSE” if there’s something in it. In simple words, it’s for checking empty cells.
Syntax
The syntax for ISBLANK is simple and easy to understand; all you need to do is refer to the cell you want to test.
ISBLANK(value)
value: This is the cell you want to check. It can reference a single cell like A1, or even a direct value, though it makes more sense when using cell references.
As I said, the function is really easy to use; you need to refer to the cell, and that’s it. But make sure to refer to the valid cell reference in the function; referencing non-existent cells can cause errors like #REF!
Quick Tips to Use ISBLANK in Google Sheets
Here are a few quick tips on how to use ISBLANK in multiple ways.
- ISBLANK returns TRUE only if the cell is empty.
- Cells that appear empty but contain spaces or non-printable characters can cause ISBLANK to return FALSE. You can use TRIM for that.
- ISBLANK is often used with the IF function to create conditional formulas.
- Use ARRAYFORMULA to use ISBLANK for an entire range.
- You can use ISBLANK for conditional formatting and data validation.
In this tutorial, we will learn all these in detail and easy-to-follow steps.
Example of using ISBLANK to Empty Cell
Let’s take the example we have discussed above. To check for the empty cells next to the column where employees mark their attendance, you can write a formula using ISBLANK.
You can use the formula =ISBLANK(B2) in column C. It will return “TRUE” if the cell in B2 is blank, meaning the employee didn’t attend. If B2 is filled, it returns “FALSE.” Doing” this for each row quickly shows which employees missed the training.
Check Blank Cell for Multiple Cells (Range)
In the above example, we enter the formula in cell B2 and then drag it to the entire column. But you can use ARRAYFORMULA combined with the ISBLANK to apply it to the entire cell range at once. Yes, ARRAYFORMULA lets you apply a function to a range of cells at once.
When you use =ARRAYFORMULA(ISBLANK(B2:B6)) in cell C2, you quickly get a TRUE or FALSE value for each cell in the range B2:B6, showing whether each cell in the range is empty or not.
Dealing with Cells that Appear Blank
One interesting aspect of ISBLANK that you need to understand before you start using it in your work is how to handle cells that appear blank but are not blank.
A cell might look empty but contains spaces or non-breaking spaces (generated by copying text from the web or other sources).
To handle this problem, you can combine ISBLANK with the TRIM:
=ISBLANK(TRIM(B2))
=ARRAYFORMULA(ISBLANK(TRIM(B2:B6)))
This ensures that cells containing only spaces are also considered empty so that you get the real result from the formula. This is especially important when counting non-blank cells or blank cells using a formula.
Note: Make sure to check for merged cells before using the ISBLANK. Though it is perfectly fine with the merged cells, it can create a problem when you try to drag down the formula of the cells below.
Using ISBLANK with IF
Combining ISBLANK with the IF allows you to create more dynamic and conditional checks based on whether a cell is empty. Instead of having TRUE and FALSE, you can have a better value in the result based on the result of ISBLANK.
In cell C2, enter the formula: =IF(ISBLANK(B2), “Absent”, “Present”), and after that, drag the formula down up to the C6 to apply the formula to test all the cell in the column B upto the cell B6.
The formula checks if cell B2 is empty using ISBLANK. If B2 is empty, the IF returns “Absent”; otherwise, it returns “Present”.
In the same way, you can also create a condition using ISBLANK and IF while checking if a cell contains a particular value.
=IF(ISBLANK(B2),"No Status",IF(B2="Yes","Present","Absent"))
This formula is a powerful way to evaluate cell value. It first checks if cell B2 is empty using ISBLANK(B2). If B2 is blank, the formula returns “No Status,” indicating no status is available. This is useful for quickly identifying incomplete attendance entries. By handling blank cells first, the formula ensures that your data set remains clear.
If cell B2 is not blank, the formula checks if it has the word “Yes”. If it does, it returns “Present” to indicate the person is present. If the cell contains any other value, it returns “Absent”.
Count Blank Cells with ISBLANK
You can combine ISBLANK with SUMPRODUCT to quickly count the empty cells.
=SUMPRODUCT(ISBLANK(A1:A10)*1)
Using ISBLANK within SUMPRODUCT can take an array, as we did with ARRAYFORMULA. In the formula, ISBLANK checks each cell in the range B1 to B6, returning TRUE if a cell is empty and FALSE if it is not.
By multiplying these boolean results by 1, TRUE values are converted to 1 and FALSE values to 0. SUMPRODUCT then sums these values, giving you the total count of blank cells.
In the End
ISBLANK has amazing uses, especially with other functions like IF, SUMPRODUCT, and ARRAYFORMULA.