How To Ignore Blank And Empty Cells In Google Sheets

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")
"<>" =COUNTIFS(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")
Formula to check if a single cell is empty or 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, "<>")
If you’re assessing empty or blank cells over a range, use these expressions

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.

Photo of author
Ryan Sheehy
Ryan has been dabbling in code since the late '90s when he cut his teeth exploring VBA in Excel. Having his eyes opened with the potential of automating repetitive tasks, he expanded to Python and then moved over to scripting languages such as HTML, CSS, Javascript and PHP.