When working with an array of different data sets you’ll undoubtedly come across the issue where you want to perform a count on your data (or something similar) and you want to exclude the blank or empty cells in your Google Sheets.
There are several ways to check if a cell is blank and you can use either type of these expressions to check. Here is a short list of operators and functions that can assist:
| Formula | Example Usage |
|---|---|
cell<>""
|
=IF(A1<>"", "NOT BLANK", "BLANK")
|
| LEN(cell)=0 |
=IF(LEN(A1)=0, "BLANK", "NOT BLANK")
|
ISBLANK(cell)
|
=IF(ISBLANK(A1), "BLANK", "NOT BLANK")
|
NOT(ISBLANK(cell))
|
=IF(NOT(ISBLANK(A1)), "NOT BLANK", "BLANK")
|
"<>"
|
=COUNTIFS(A:A, "<>")
|
COUNTBLANK
|
=COUNTBLANK(A:A)
|
All of the above formulas check if the cell contained in
A1
is blank or empty, or a range of cells is NOT empty in the case of the last couple of examples above.
Depending on what your conditional expression is doing will depend on which type of formula you will need.
Check If A Cell Is Empty Or Blank
If you need to check if a single cell is empty or blank, simply perform one of these types of checks:
| Formula | Example Usage |
|---|---|
cell<>""
|
=IF(A1<>"", "NOT BLANK", "BLANK")
|
| LEN(cell)=0 |
=IF(LEN(A1)=0, "BLANK", "NOT BLANK")
|
ISBLANK(cell)
|
=IF(ISBLANK(A1), "BLANK", "NOT BLANK")
|
NOT(ISBLANK(cell))
|
=IF(NOT(ISBLANK(A1)), "NOT BLANK", "BLANK")
|
Any of the above formulas in the table will help with your conditional expression to determine if a cell contains content.
Check If A Range Has Empty Or Blank Cells
If you need to check if a range contains empty or blank cells, then you’ll need to use formulas that use a range in their parameters.
Some popular examples are
COUNTIF
function,
COUNTIFS
and
SUMIFS
formulas. If you are using the following formulas and want to exclude empty or blank cells from your calculation, use the
expression
"<>"
in your condition parameters.
Otherwise, if you need to count the number of blank cells in a range to see how many empty rows, use the formula specific that meets that requirement, which is also found in Microsoft Excel:
COUNTBLANK
.
| Expression | Example Usage |
|---|---|
"<>"
|
=COUNTIFS(A:A, "<>")
|
COUNTBLANK
|
=COUNTBLANK(A:A)
|
Blank Formulas Don’t Work?!
If you find any of the above formulas aren’t working as expected, there are a couple of things you will want to check.
There have been a few times where I’ve stared blankly at a range of data cells wondering why I’m getting an odd result where all I can apparently see is a blank number of cells.
So, if you find you’re in the same predicament and can’t work out why a set of blank rows is displaying a result, here’s a quick checklist to help you find the cause of the problem:
1. Check Cell/Range Formatting
One of the easiest and sometimes the least obvious checks is to make sure all the formatting of the cells (or cell) you are operating on isn’t deceiving you with its colour.
Check the contents of the cell are not masking a hidden character that perhaps has the same colour as the background of the cells of your Google spreadsheet.
2. Check Cell Contents For Whitespace
Check that all cells don’t have some space character in their cell like a simple space character
" "
or even a carriage return:
CHAR(10)
.
One quick method I use to clean my data before operating on it is to ensure all whitespace is stripped from the cells. One helpful formula is the
TRIM()
function, which removes all padded white space on the cell’s contents.
However, if you don’t want to “clean” the data using the
TRIM()
formula, you should inspect each cell by using the
LEN(cell)
formula. If there is a single space or carriage return or tab character or empty string in the cell, the
LEN(cell)
formula will pick this up and report a value other than
0
.
3. Check Cell Structure
Check if cells are merged or contain error results, as these may cause issues with the right result you are anticipating.
4. Check Zero Cells
Although this might seem counter-intuitive, it should also be noted that cells containing zero values are not deemed to be blank or empty.
They contain something, even though the cell may contain a zero value. The formula checks performed above check that the cell contents contain
absolutely nothing at all,
but if the cell contains something like a
0
it would mean a cell does contain
something
and, therefore, is
NOT blank or empty
.
How To Return A Blank Value
What if you’re looking to return a blank value in a formula?
What is the best approach in ensuring others, or even your future self, don’t trip up over your spreadsheet structure should they want to perform an analysis involving the removal of blank or empty cells?
The best way to return a blank value is to not report anything.
For example, if I use the following formula and I want the FALSE version of my result to return a blank value in my Google Sheets spreadsheet, then I would write the function like this:
=IF(LEN(cell),"Hello world!",)
Notice that after the truthy result of
"Hello world!"
I close the true return value and then
immediately close
the
IF
function.
Should this formula then not find any content in the
cell
it is referencing its result will be a blank value.
This means should I perform further numerical calculations on this cell and test to see if it is blank it will report that it is a blank cell (even though there is a function in it!).
Blanks in
QUERY
Function
If you want to find out more about how to handle blank or empty cells in other functions such as the popular QUERY function, then this guide will help.
