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 is to add the condition IS NOT NULL to the column where the blank or empty cells are found. By applying the condition IS NOT NULL in a QUERY formula would make it look something like this:
=QUERY(DataRange, "SELECT * WHERE Col1 IS NOT NULL", 1)
How Does It Work?
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 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 NULLapply 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
Result From IS NOT NULL
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.