Categories
Cloud Apps

Ignore Blank Cells in QUERY Function Google Sheets

When using the QUERY function in Google Sheets how can we ignore certain rows based on the criteria of some cells being blank?

If you have a series of data where you’d like to apply the QUERY function over, but want to ignore certain rows if a particular cell is blank or empty then you would write your formula as follows:

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

The way you can have the Google Sheets QUERY function ignore blank or empty cells is by using the string IS NOT NULL in your formula.

Example

As an example, suppose we had the following data on assets we’d like to purchase that have differing 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):

QUERY Function Remove Empty Cells
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 for which the QUERY function will be performing its task.

In the example above the QUERY is performed on the data inputs for each of the large purchase rows.

Second Parameter – Query Filter

Applying a filter on the data range by using a form of SQL language. In our example, here’s what the statement translates into:

  • SELECT * – select everything from the range;
  • WHERE B IS NOT NULL – apply the filter on column B where the cell is not null (is 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.

Solution

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

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

Conclusion

In this post we’ve explored the powerful QUERY function and how we can filter our data by excluding rows where they contain cells which are blank or empty.

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

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

Leave a Reply