The Google Sheets function “QUERY” is one of the handiest functions in a Google Sheets wizard’s toolbox. And one of the great things about QUERY is that it can interpret the first row of data as a header row. But what if you don’t want the header row at all?
There are a few ways to prevent headers from being generated in the output.
But before we get into the weeds, let’s take a look at the raw dataset we’ll be working with.
| A | B | C | |
| 1 | Name | Gender | Age |
| 2 | Bob | Male | 22 |
| 3 | Sharon | Female | 33 |
| 4 | Jerry | Male | 44 |
| 5 | Martha | Female | 55 |
Exclude headers from input
First, you can simply exclude the header row from the input and set the third parameter in your QUERY function to 0 (which tells the QUERY function that there are 0 header rows in the range).
And here’s our query to remove the header row by excluding it from our range and setting the headers parameter to 0.
=QUERY(A2:C, "SELECT A, B, C", 0)And here’s the result we get.
| Bob | Male | 22 |
| Sharon | Female | 33 |
| Jerry | Male | 44 |
| Martha | Female | 55 |
Notice that the range in our query is set to A2:C rather than A:C. Also, notice the addition of the third parameter which is set to 0.
Exclude headers from output
The second option is to use the entire range and apply an OFFSET clause to your query, again also setting the third parameter in your function to 0.
=QUERY(A:C, "SELECT A, B, C OFFSET 1", 0)| Bob | Male | 22 |
| Sharon | Female | 33 |
| Jerry | Male | 44 |
| Martha | Female | 55 |
The OFFSET clause skips the first X number of rows in the result or your query. For example, OFFSET 3 would skip the first 3 rows returned in the resulting dataset. In our case, we’re skipping just one row in the result: the header row.
In this particular instance, we’re treating the first row as though it weren’t a header row. Here’s what I mean. We’ve included all rows A:C. And when we set the third parameter to 0, we’re telling the QUERY function that there are no header rows in that range. Then, through our OFFSET clause, we tell QUERY, “Go and get all the data from A:C. But when you print out the result, don’t print out the first row.”
These two solutions are both great solutions. However, there is one case where the QUERY function will generate headers automatically even if you remove the header row from your input, set headers to 0, and apply an OFFSET clause. That case is when using data manipulation functions such as SUM(), AVG(), or NOW(). When using these functions, QUERY will automatically add a header row specifying the function used. And no amount of changing the range, setting headers to 0, or applying offsets will get rid of it.
=QUERY(A2:C, "SELECT B, AVG(C) GROUP BY B OFFSET 1", 0)| avg | |
| Female | 49.5 |
| Male | 27.5 |
Fortunately, there’s a workaround. It’s a bit of a hack, really, but it gets the job done.
Remove auto-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 so:
=QUERY(A2:C, "SELECT B, AVG(C) WHERE NOT B = '' GROUP BY B LABEL AVG(C) ''", 0)Here we’ve excluded the header row from our input range (A2:C). We’re applying the AVG() aggregation function to column C. We’re only retrieving those rows that have data (WHERE NOT B = ”). And, to get rid of that pesky header that gets automatically generated (because we’re using the AVG() function), we’re applying an empty LABEL to each instance of data manipulation, i.e. AVG(C). Now that all headers are empty, Google Sheets prints out a range with no headers.
| Female | 49.5 |
| Male | 27.5 |
Outstanding! There it is. A QUERY result in our Google Sheet that has no headers at all (even when using data manipulation functions). Awesome.
Remove auto-generated headers – THANK YOU! This was driving me nuts!
Same here! Thanks ‘davemeindl’!
This also works. It takes the inner QUERY as a range and lists all except the 1st row:
=QUERY(QUERY(A2:C, “SELECT B, AVG(C)”, 0), “SELECT * OFFSET 1”, 0)
Hey Gabor. Good suggestion. Your solution just requires a little tweaking in order to work. You’ll need a GROUP BY clause in the inner QUERY (since you’re using an aggregate function), and a WHERE clause to eliminate empty rows.
=QUERY(QUERY(A2:C, "SELECT B, AVG(C) WHERE NOT B = '' GROUP BY B", 0), "SELECT * OFFSET 1", 0)Interesting. I am using my shorter version and it fine as it is.
… the OFFSET 1 in the outer loop skips the header (for me).
It works for me! Thanks.
Brilliant! A perfect, and perfectly described fix to dealing with query headers.
Thanks so much for the direct and succinct solution.
Perhaps these suggestions don’t work for pivots within a query such as:
= QUERY(‘Class hours’!A2:C11,”select A, sum(C) WHERE A= ‘” & A5 & “‘ group by A pivot B”,0)
Here, I am referencing the content of A5 within “Class hours” tab in order to create a pivot table with content from column C in the same tab. The pivot columns are labelled with the contents of the column B on which the pivot is performed.
There are two problems: (a) the actual result, i.e. SUM(C) is returned on row below, one columns to the right, while the pivot labels are returned in the same row as the query function cell, (b) in the next row, I need to pull in the content of A6 to perform the same query. This I can’t do as the previous query has taken up two rows.
I came across the following possible workaround that works to change date formats https://infoinspired.com/google-docs/spreadsheet/format-query-pivot-header-row-in-google-sheets/
This has parallels to my issue, but doesn’t solve the problem. Any suggestions will be really great. Thanks.
Wrapping your query inside another query should get the job done. In your inner query, set the label of column A to an empty value and set the “headers” parameter to 1. In your outer query, set the “headers” parameter to 0.
Using the example table in my post, the formula might look like:
=QUERY(QUERY(A:C,"SELECT A, SUM(C) WHERE A != '' GROUP BY A PIVOT B LABEL A ''",1),"SELECT * WHERE Col1 != ''",0)Using your formula:
=QUERY(QUERY('Class hours'!A2:C11,"select A, sum(C) WHERE A= '" & A5 & "' group by A pivot B label A ''",1),"SELECT * WHERE Col1 != ''",0)Hi,
I am having to issues trying to complete a task:
(1) How do you remove headers from an imported table using the following: =transpose(importhtml(“https://wallmine.com/asx/”&A6,”Table”,$B$4)).
A6=ticker and $B$4 =table number. I need the header for the first row only, then from row three I need only the data, as I have multiple stocks I need the data on.
(2) As I have 19 table to get data on, is there a way to add all tables ie 1:19 in the one function rather than doing 19 importhtml functions for each row. I tried to use Arrayformula, but it appears that you cannot use this with the importhtml function.
Hi Ron. Happy to help. Could you share a copy of your sheet so I can take a look?
Hi Dave,
Thanks for the quick response, this is the link to my google sheet: https://docs.google.com/spreadsheets/d/1-lrEZo67feRRa60tnG9mM0WOgSvjAhAAEMMlciqKIpI/edit#gid=0. I have solved issue (1) by query and offset as follows: =query(transpose(importhtml(“https://wallmine.com/asx/”&A7,”Table”,$B$4)),”select * offset 1″,0).
If you could advise me if it is possible to extract data for all the 19 tables in one function, that would be great. I tried looking for an xPath to use with importxml, but could not find one, but then again I am not an expert at looking at xPath values.
Hi Dave,
As I have now found the xPaths to enable me to reduce the number of xPaths from 19 to two, , I am happy with that, so you can now close this issue.
Regards,
Hey, Ron. Glad you got it figured out!
If Excel reads like this: =XLOOKUP(D43,$D$44:$D$46,$E$44:$E$46,,-1)*D43
How do i translate this for Google Sheets?
XLOOKUP is virtually the same as VLOOKUP. However, VLOOKUP does not support “last to first item” searching, i.e. to return the last result rather than the first. So, to return the last result, you’d simply sort the range by row number in descending order. Then perform a VLOOKUP.
=VLOOKUP(D43,SORT($D$44:$E$46,ROW($D$44:$D$46),FALSE),2,FALSE)My query just suddenly stopped bringing over the header row – messing up our “live” spreadsheets. I can’t figure out why it is doing this. The formula has been live and working fine for months.
Kayla, can you provide the link to the sheet? Or at the very least, the formula you’re working with?
This is great. Can you help me remove the header from my output? My data sheet is Google Form Responses and looks like this:
Col A -Timestamp
B-Select Name- 8th Graders
C-Select Name- 7th Graders
D-Choose an Activity
E & F- Blank
G- Combined Names from Columns B & C
My formula is trying to pull the names from col G and the activity (D) sorted and unique and filtered by today’s date
This is what I have and it works but I want it without headers:
=Unique(QUERY(Responses!A:G,”select G, D where A >= date ‘”&TEXT(TODAY(),”yyyy-mm-dd”)&”‘ order by G”,1))
Any suggestions? So much appreciated (been working on this formula all weekend)
THANK YOU! This helped out so much
Glad to hear you found it helpful
Late to the party but wanted to take a moment to express my gratitude for the solution to remove auto-generated headers. Extremely helpful, thank you.
Glad you hear that you’ve found it helpful.
To remove the headers from min(), max(), etc… rather than run two queries you can enforce a blank label to remove the header:
=query({A1:B10}, select max(Col1) where Col2 = ‘mydata’ label max(Col1) ””,0)
How can i OFFSET a column instead? So it starts displaying data from column 2 and on…
This is done in your SELECT statement.
SELECT B, CThe SELECT statement above would select columns B and C, skipping column A.