In Google Sheets, the QUERY function may return empty rows unintentionally if there are any blank rows in the source data. To avoid this issue, you can use the where clause to remove those blank rows. This tutorial will guide you through the process of removing these blank rows by modifying your query. Let’s start by taking a look at an example query that returns blank rows.
You can follow along with these examples by making a copy of the Google Sheet with the examples.
Example Query with Empty Rows
Starting formula: =QUERY(A1:C,"select *",1)
The range on the left, surrounded by yellow in the picture above, is the source data for the query, and the data on the right, enclosed by green, is the output. Both ranges have empty rows starting at row 6. If you enter any data below the query output, it will change the query output to an error.
But before we go too far, let’s look at the syntax of the query function.
=QUERY(data, query, [headers])
data– Location of the dataquery– The query statement[headers]– The number of header rows at the top of thedata
Now let’s discuss how to prevent these blank rows. We’ll change the query portion of the formula to remove the blanks.
Removing Empty Rows from the Output
We’ll add a where clause that instructs the formula not to return null (empty) rows.
The new formula is =QUERY(A5:C,"select * where A is not null",1). This new formula trims the blank rows from the query output. Now, you can place text below the output without causing any problems.
Video Tutorial
Watch this query tutorial’s video for a visual walkthrough.
