If you’re a seasoned user of Google Sheets, chances are you’re already familiar with the “QUERY” function. It’s an essential tool that allows you to query and manipulate data in your spreadsheets. One of the great strengths of QUERY is its ability to interpret the first row of data as a header row. But what if you don’t want to display this header row at all?
Exclude Headers from the Input
Firstly, you can simply exclude the header row from the input and set the third parameter of your QUERY function to 0, indicating that there are no headers in the range. Here’s an example of how to remove the header row by excluding it from your range and setting the headers parameter to 0:
=QUERY(A2:C, "SELECT A, B, C", 0)Take a look at the resulting output:
Notice that the query range is set to A2:C instead of A:C. Also, observe the addition of the third parameter, which is set to 0.
Exclude Headers from the Output
The second option is to use the entire range and apply an OFFSET clause to your query, also setting the third parameter of your function to 0. Here’s an example:
=QUERY(A:C, "SELECT A, B, C OFFSET 1", 0)The OFFSET clause allows you to skip the first X rows of the result set or your query. In our case, we’re skipping just one row in the result: the header row.
In this specific case, we treat the first row as if it’s not a header row. We include all rows from A:C. And when we set the third parameter to 0, we tell the QUERY function that there are no header rows in this range. Then, with our OFFSET clause, we instruct QUERY: “Fetch all data from A:C, but when you print the result, don’t highlight the first row.”
Both of these solutions work great. However, there’s a case where the QUERY function will automatically generate headers even if you remove the header row from your input, set headers to 0, and apply an OFFSET clause. This occurs when you use data manipulation functions like SUM(), AVG(), or NOW(). When you use these functions, QUERY will automatically add a header row specifying the function used. And no range changes, headers set to 0, or offsets will get rid of it.
=QUERY(A2:C, "SELECT B, AVG(C) GROUP BY B OFFSET 1", 0)Fortunately, there’s a workaround. It’s a bit of a hack, but it gets the job done.
Remove Automatically Generated Headers
To remove the automatically generated header row from your result when using a data manipulation function with QUERY, set an empty label for each of the data manipulation functions, like this:
=QUERY(A2:C, "SELECT B, AVG(C) WHERE NOT B = '' GROUP BY B LABEL AVG(C) ''", 0)In this example, we excluded the header row from our input range (A2:C). We applied the AVG() aggregation function to column C. We only retrieve rows that contain data (WHERE NOT B = “”). And to get rid of that annoying header row that’s automatically generated (because we’re using AVG()), we applied an empty label to each instance of the data manipulation function, i.e., AVG(C). Now that all the headers are empty, Google Sheets prints a range without headers.
Excellent! Look at the result of a query in our Google Sheets without any headers at all (even when using data manipulation functions). Awesome!
Sign Up to Learn More Google Sheets Secrets
If you want to learn more about Google Sheets secrets and discover tips to make the most out of this platform, you can sign up at Crawlan.com. There, you’ll find articles, guides, and tutorials to help you become a Google Sheets expert and improve your efficiency in your daily tasks.
Don’t forget to share these tips with your friends who could also benefit from them. The more, the merrier!
This article was originally written in French and has been carefully adapted for our bolamarketing.com brand. For more exciting content and Google Sheets expertise, visit Crawlan.com.
