# 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):

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 .

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.