How do you check if a cell is empty or blank in Google Sheets?
There is a handy function called
ISBLANK which enables you to check if a cell is empty.
What Does Empty Really Mean?
In Google Sheets there are two ways of having an empty cell, one way is by defining an empty string
"" and another way is by having nothing in that cell.
To check that a cell meets these criteria of being “empty” we use the
ISBLANK function, like so:
|3||No not empty|
ISBLANK formula contains only one parameter and this would point to the cell being checked if it is empty.
In the above example we checked the value of the cells contained in column A, where column A contained the following results:
CELL A1contained a
nullvalue – this is deemed to be an empty cell (as the outcome of cell B1 showed)
CELL A2contained an empty string – this also is deemed to be an empty cell.
FALSE– but as this is something this cell is deemed not to be empty.
How To Check If Cell Is NOT Empty
If I wanted to check if a cell was NOT empty I would pass the
ISBLANK formula into the
NOT formula, like so:
Here are the same examples with the
ISBLANK formula wrapped with
How To Check If Range Of Cells Are Empty
What if I wanted to broaden my check on whether one cell is empty, to whether a range of cells is empty.
ISBLANK formula allows for a range of values to be passed into its only parameter and with the range entered it can check if the result is blank.
How To Check If Multiple Ranges Are Empty
Checking one contiguous range is fairly straightforward, as demonstrated above, but what if there are multiple ranges?
We could combine all the ranges into separate ISBLANK calls and wrapping these in an AND function, something like so:
=AND(ISBLANK(range1), ISBLANK(range2), ...)
This is good if there are only a handful of ranges and they’re of varying sizes.
Cell Is Empty But
ISBLANK Says It Isn’t??
There may be times where the result of the
ISBLANK formula doesn’t appear to give the right result, why is that?
First, check if there’s something in the cell by running the following function:
This will tell you if there actually is something in the cell that will confirm the result from
Now that you have a value within this cell it could be one of two things:
- The cell contains spaces or carriage returns; and/or
- The font colour of the cell is the same as that of the cells’ background.
To get the character in a cell use the
CODE on the cell to display what the ASCII code is for the contents in that cell.
In this article, we looked at how to find out whether a cell or range contained (or didn’t contain) blanks.
The easiest way to check if a cell is empty is to use the
ISBLANK function and to pass a cell or range into the sole parameter of the formula.