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