Ignore Blank Cells in QUERY Function Google Sheets

The QUERY function in Google Sheets is a powerful function that helps to operate on a range of data, however, on a current project I needed the QUERY function to ignore rows where a certain column was empty. Here was how I was able to get the desired output.

To ignore blank or empty cells using Google Sheet’s QUERY function add the condition IS NOT NULL in the WHERE clause for the column where the blank or empty cells are found.

An example of applying the condition IS NOT NULL in a QUERY formula would look something like this:

=QUERY(DataRange, "SELECT * WHERE Col1 IS NOT NULL", 1)

How Does It Work?

Suppose we have the following data on assets we’d like to purchase that have different depreciation rates, but we only want to return the items where there is a depreciation value (implying purchases with no depreciation will be written off in the year they are purchased):

Depreciation schedule — Remove blank cells from `QUERY`

In the above diagram we have the following function:

=QUERY(A2:E8, "SELECT * WHERE B IS NOT NULL", 0)

Here’s what each of the parameters passed into the QUERY function mean:

First Parameter – Data Range

Set the data range the QUERY function will be operating on, in the example above the QUERY is performed on the data inputs for each of the large purchase rows.

Second Parameter – Query Filter

Apply a filter on the data range by using Google’s Query language. In our example, here’s what the statement translates into:

SELECT * selects everything from the range;
WHERE B IS NOT NULL apply the filter on column B and ignore cells in this column that are not blank or empty.

Third Parameter – Header Rows

Although our data set does contain a header row ( row A ) we don’t want to return this in our result, therefore, we set this value to 0 .

`IS NOT NULL` Result

The result of our formula are values from the original data set which need to be depreciated over several years.

We can further apply additional logic to our QUERY function to perhaps SUM the value of all assets purchased containing the same depreciation value.

Summary

In this post, you have explored the powerful QUERY function and how it can filter your data by excluding rows where it contains cells that are blank or empty.

By applying the condition IS NOT NULL in our QUERY filter you can easily remove these from your results.

If you’d like to learn more about QUERY functions, especially how you can merge multiple rows into one you should check out our illustrated step-by-step article .

Learn more about how to ignore blank cells in other functions, such as the popular IF, IFS, SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS (etc) functions here .