by If you've been following this blog for a while, you may remember QUERY function for Google Sheets. I mentioned it as a possible solution for a couple of cases. But those are far from enough to uncover its full potential. Today, it's high time we get to know this spreadsheets superhero properly. And guess what – one equally noteworthy tool will also be there :)
Did you know that Google Sheets QUERY function is considered to be the most powerful one in spreadsheets? Its peculiar syntax favours tens of different operations. Let's try and break its parts down to learn them once and for all, shall we?
Syntax of Google Sheets QUERY function
At first glance, Google Sheets QUERY is just another function with 1 optional and 2 required arguments:
- data is the range to process. Required. Everything is crystal clear here.
Note. Only one small reminder here established by Google: each column should contain one type of data: textual, or numeric, or boolean. If there are different types, QUERY will work with the one that occurs the most. Other types will be considered as empty cells. Strange, but keep that in mind.
- query is the way to process the data. Required. This is where all the fun begins. Google Sheets QUERY function uses a special language for this argument: Google Visualization API Query Language. It's written in a way similar to SQL. Basically, it's a set of special clauses (commands) used to tell the function what to do: select, group by, limit, etc.
Note. The entire argument must be enclosed in double-quotes. Values, in their turn, should be wrapped in quotation marks.
- headers is optional for when you need to indicate the number of header rows in your data. Omit the argument (as I do below), and Google Sheets QUERY will assume it based on the contents of your table.
Now let's dig deeper into the clauses and whatever they do.
Clauses used in Google Sheets QUERY formulas
Query language consists of 10 clauses. They may frighten at first glance, especially if you're not familiar with SQL. But I promise, once you get to know them, you will get a powerful spreadsheet weapon at your disposal.
I'm going to cover each clause and provide formula examples using this list of imaginary students and their paper subjects:
Yep, I'm one of those weirdos who think Pluto should be a planet :)
Tip. Several clauses can be used within one Google Sheets QUERY function. If you nest them all, make sure to follow the order of their appearance in this article.
Select (all or specific columns)
The very first clause – select – is used to tell what columns you need to return with Google Sheets QUERY from another sheet or table.
Example 1. Select all columns
To fetch each and every column, use select with an asterisk – select *
=QUERY(Papers!A1:G11,"select *")
Tip. If you omit the select parameter, Google Sheets QUERY will return all columns by default:
=QUERY(Papers!A1:G11)
Example 2. Select specific columns
To pull only certain columns, list them after the select clause:
=QUERY(Papers!A1:G11, "select A,B,C")
Tip. The columns of interest will be copied in the same order you mention them in the formula:
=QUERY(Papers!A1:G11, "select C,B,A")
Google Sheets QUERY – Where clause
Google Sheets QUERY where is used to set the conditions towards the data you want to get. In other words, it acts as a filter.
If you use this clause, QUERY function for Google Sheets will search columns for values that meet your conditions and fetch all matches back to you.
Tip. Where can function without the select clause.
As usual, to specify conditions, there are sets of special operators for you:
- simple comparison operators (for numeric values): =, <>, >, >=, <, <=
- complex comparison operators (for strings): contains, starts with, ends with, matches, != (doesn't match / doesn't equal to), like.
- logical operators to combine several conditions: and, or, not.
- operators for blank / not empty: is null, is not null.
Tip. If you're upset or worried about having to deal with such a huge number of operators again, we feel you. Our Filter and Extract Data will find all matches and build QUERY formulas in Google Sheets for you if necessary.
Let's see how these operators behave in formulas.
Example 1. Where with numbers
I will add where to my Google Sheets QUERY from above to get the info on those planets that have more than 10 moons:
=QUERY(Papers!A1:G11,"select A,B,C,F where F>=10")
Tip. I also mentioned column F to fetch just to make sure the criterion is met. But it's completely optional. You don't have to include columns with conditions into the result:
=QUERY(Papers!A1:G11,"select A,B,C where F>=10")
Example 2. Where with text strings
- I want to see all rows where the grade is either F or F+. I will use the contains operator for that:
=QUERY(Papers!A1:G11,"select A,B,C,G where G contains 'F'")
Note. Don't forget to surround your text with quotation marks.
- To get all rows with F only, just replace contains with an equal sign (=):
=QUERY(Papers!A1:G11,"select A,B,C,G where G='F'") - To check the papers that are yet to be delivered (where the grade is missing), check column G for blanks:
=QUERY(Papers!A1:G11,"select A,B,C,G where G is null'")
Example 3. Where with dates
Guess what: Google Sheets QUERY has even managed to tame dates!
Since spreadsheets store dates as serial numbers, usually, you have to resort to the help of special functions like DATE or DATEVALUE, YEAR, MONTH, TIME, etc.
But QUERY has found its way around dates. To enter them properly, simply type the word date and then add the date itself formatted as yyyy-mm-dd: date '2022-01-01'
Here's my formula to get all rows with a Speech date before 1 Jan 2022:
=QUERY(Papers!A1:G11,"select A,B,C where B<date '2022-01-01'")
Example 4. Combine several conditions
To use a certain period of time as a criterion, you will need to combine two conditions.
Let's try and retrieve those papers that were delivered in Autumn, 2019. The first criteria should be a date on or after 1 September 2021, the second — on or before 30 November 2021:
=QUERY(Papers!A1:G11,"select A,B,C where B>=date '2021-09-01' and B<=date '2021-11-30'")
Or, I can select papers based on these parameters:
- before 31 December 2021 (B<date '2021-12-31')
- have either A or A+ as a grade (G contains 'A')
- or B/B+ (G contains 'B')
=QUERY(Papers!A1:G11,"select A,B,C,G where B<date '2021-12-31' and G contains 'A' or G contains 'B'")
Tip. If your head is about to explode already, don't give up just yet. There's a tool that is perfectly capable of building all these formulas for you, no matter the number of criteria. Jump right to the end of the article to get to know it.
Google Sheets QUERY – Group By
Google Sheets QUERY group by command is used to concatenate rows. However, you should use some aggregate functions in order to summarize them.
Note. Group by must always follow the select clause.
Unfortunately, there's nothing to group in my table as there are no recurring values. So let me adjust it a bit.
Suppose, all the papers are to be prepared by 3 students only. I can find the highest grade each student got. But since they are letters, it is the MIN function I should apply to column G:
=QUERY(Papers!A1:G11,"select A,min(G) group by A")
Note. If you don't use an aggregate function with any column in the select clause (column A in my example), you must duplicate them all in the group by clause.
Google Sheets QUERY – Pivot
Google Sheets QUERY pivot clause works the other way around, if I may say so. It transposes data from one column to a row with new columns, grouping other values accordingly.
For those of you dealing with dates, it can be a real discovery. You'll be able to get a quick glance at all the distinct years from that source column.
Note. When it comes to pivot, every column used in the select clause should be covered with an aggregate function. Else, it should be mentioned in the group by command following your pivot.
Remember, my table now mentions only 3 students. I'm going to make the function tell me how many reports each student made:
=QUERY(Papers!A1:G11,"select count(G) pivot A")
Google Sheets QUERY – Order By
This one is pretty easy :) It is used to sort the outcome by the values in certain columns.
Tip. All previous clauses are optional when using order by. I use select to return fewer columns for demonstration purposes.
Let's go back to my original table and sort reports by speech date.
This next Google Sheets QUERY formula will get me columns A, B and C, but at the same time will sort them by date in column B:
=QUERY(Papers!A1:G11,"select A,B,C order by B")
Tip. You can learn everything about sorting data (by date, colors, etc.) in Google Sheets in this article.
Limit
What if I told you, you don't have to bring each and every row into the result? What if I told you that Google Sheets QUERY can pull only a certain amount of the first matches it finds?
Well, the limit clause is designed to help you with that. It limits the number of rows to return by the given number.
Tip. Feel free to use limit without other previous clauses.
This formula will show the first 5 rows where the column with grades contains a mark (is not empty):
=QUERY(Papers!A1:G11,"select A,B,C,G where G is not null limit 5")
Offset
This clause is kind of opposite to the previous one. While limit gets you the number of rows you specify, offset skips them, retrieving the rest.
Tip. Offset also doesn't require any other clauses.
=QUERY(Papers!A1:G11,"select A,B,C,G where G is not null offset 5")
If you try and use both limit and offset, the following will happen:
- Offset will skip rows at the beginning.
- Limit will return a number of the following rows.
=QUERY(Papers!A1:G11,"select A,B,C,G where G is not null limit 3 offset 3")
Out of 11 rows of data (the first one is a header and QUERY function in Google Sheets does a nice job understanding that), offset skips the first 3 rows. Limit returns 3 next rows (starting from the 4th one):
Google Sheets QUERY – Label
Google Sheets QUERY label command lets you change header names of the columns.
Tip. Other clauses are optional for label as well.
Put the label first, followed by the column ID and a new name. If you rename few columns, separate each new pair of column-label by a comma:
=QUERY(Papers!A1:G11,"select A,B,C label A 'Name', B 'Date'")
Format
The format clause makes it possible to alter the format of all values in a column. For that, you will need a pattern standing behind the desired format.
Tip. The format clause can also play solo in the Google Sheets QUERY.
=QUERY(Papers!A1:G11,"select A,B,C limit 3 format B 'mm-dd, yyyy, ddd'")
Tip. I mentioned some date formats for Google Sheets QUERY in this blog post. Other formats can be taken directly from spreadsheets: Format > Number > More Formats > Custom number format.
Options
This one is used to set some additional settings for the outcome data.
For example, such command as no_values will return formatted cells only.
The quickest way to build QUERY formulas – Filter and Extract Data
However powerful the QUERY function in Google Sheets is, it may require a learning curve to get ahold of. It's one thing to illustrate each clause separately on a small table, and completely another to try and build everything correctly with a few clauses and a much bigger table.
That's why we decided to dress Google Sheets QUERY up in a user-friendly interface and make it the add-on: Filter & Extract Data.
Why Filter & Extract Data is better than formulas?
Well, with the add-on there's absolutely no need to:
- figure out anything about those clauses. It's really easy to create lots of complex conditions in the add-on: as many as you need despite their order to fetch as many matches as you need.
Note. At the moment, the following clauses were incorporated into the tool: select, where, limit, and offset. If your task requires other clauses as well, please comment below – perhaps, you'll help us improve ;)
- know how to enter operators: just pick the one from a drop-down list.
- puzzle over the correct way to enter date and time. The add-on lets you enter them as you used to based on your spreadsheet locale.
Tip. There's always a hint available in the tool with examples of different data types.
As a bonus, you'll be able to:
- preview both the result and the formula
- make quick adjustments to your criteria
- select a place for the result
- insert the result as both QUERY formula or as values
I'm not kidding, see for yourself:
I hope you will give Filter & Extract Data a chance and install it to your Google Sheets.
This tutorial will also help you get started.
And don't hesitate to share your feedback, especially if there's something about it you don't like!
Latest comments
Hi guys, thanks so much for this walk through, I learned a lot! I am wondering if there is a way to format the query results in a table right away. So for instance, one of the source data columns is a dropdown with multiple selects. When the data is displayed in the query results, the multiple selections are displayed as comma separated text. Is it possible to convert query data directly into a table, to retain the functionality and sorting/filtering?
Scenario: i have a stocklist with a range of suppliers. Some items I can get from multiple suppliers. So I created the query to populate a reordering list once items in the inventory fall below a threshold value. The query function is great, because i can select the columns for the item name, its code, quantity to order, the price and the suppliers - all the info I need when doing my reordering. However, if the results were in a table, then I would also be able to filter by suppliers.
For example: Items 1 and 2 can be purchased at Samsons, Items 3 and 4 can be ordered from Harveys and Item 5 can be ordered from both. In a table, I would sort or filter by supplier to get the list for each place. As it is at the moment, even if I apply sort and filter on the column headers, Item 5 doesn't 'work' because it shows in the results as "Harveys, Samsons" rather than the category (or tag) that is such a great function of the Google Sheets table multiple select dropdown.
This is surprisingly hard to explain but I hope it makes some sort of sense. If it is possible to autoformat Query results into a table that would be great to know, thank you!
Formulas in Google Sheets can not return a real Table object and preserve multi-select dropdown like “chips.” QUERY and all other function can returns plain values only, therefore the pill-shaped chips are flattened to the text “Harveys, Samsons,” and the automatic filter/sort that tables give you can’t be generated by a formula. You have to create the table after the data is in place, or work from the original data instead of a formula spill range. While those "cool features" like chips, dropdown and so on look great and visually make the table clearer, I use them only for dashboard because those features limit your data processing.
I am trying to pull the data from a column in another sheet if a range of columns contains the information in my reference cell and equals a number in another column. This is the formula I am using. What am I doing wrong?
=query(Sheet5!A:O, "select A where J1:O100 contains'"&$A2&"' and F=1",0)
Hi
It's a good question! You're really close, but there are a couple of important points to correct:
1. In QUERY(), you can’t use a range like J1:O100 in the where clause, QUERY expects a single column for each condition.
2. Also, CONTAINS only works for a single column, not across multiple columns at once.
To solve your problem, you need to use the 'and' and 'or' operators.
=QUERY(Sheet5!A:O, "select A where (J contains '"&$A2&"' or O contains '"&$A2&"') and F = 1", 0)
I highly recommend using our Filter & Extract add-on. It was especially designed to help with syntax and complex logic.
Is there a way to add a blank column in the middle of data:
=sort(query(importrange("xxxxxxxxxx","xxxxxxxx!A5:BO250"),"select * where Col39='Susan' OR Col44='Susan' OR Col49='Susan' OR Col54='Susan'"),2,false)
I need a blank column after col44 on the exported sheet.
Hello Retha,
To add an empty column between your columns with data, you will have to list all columns in your 'select' clause adding a blank one inside:
"select Col1, Col2, ... Col44, '', Col45"
=QUERY(' Login/Logout Raw'!A:P,"select P,A,N,J,L,M where A= 278 OR A= 530 OR A= 532 OR A= 551 OR A= 563 OR A= 565 OR A=580 OR A=621 OR A=669") can i use date format mm/dd/yyyy here ?? here column p date column
Hello,
Please see 'Example 3. Where with dates' in this section to learn how to enter dates in QUERY.
Hi Natalia,
Thank you for sharing this clear explanaition.
Please I need your advice to consolidate sheets in one sheet in google drive. Knowing that I have a lot of blank cells. What can I do in condition to keet it at it is.
Thanks in advance
Hi hanane,
You're most welcome!
Have you tried the tool shown in this article?
i need your help! I want query formula with time and date. Like after 8am my data automatically should remove?
With the date i am using already now I want to add time....
Hi! If I understand your task correctly, use the DATETIME function to include the time in querying entries. The data should have the following format: YYYY-MM-DD HH:mm:ss
Here is the query pattern:
SELECT columns WHERE datetime column > DATETIME 'start _date _and _time' AND datetime_column > DATETIME 'end_date_and _time'
My query is pulling the information I request. Can I update the query to only bring over the first 2 letters and not the full cell? Column P has States and their abbreviations. I just want NY, TX, not NY - New York and TX - Texas.
=query('data'a2:z300, "select P where U= 'State'")
Hi! You do not want to display the first two letters, but the abbreviation. You cannot do this in a query. You can get the full names and then use the VLOOKUP formula to retrieve the corresponding abbreviation from a separate table.
Hello,
I'am having problem with with my data, i want to search in 2 row, where row 1 for searching data dan 2nd row is data need to show, but have duplicate data and I want to show data with eliminating duplicate, is that possible. thank you.
Hello,
I'm sorry, your task is not clear. Please try to explain it in more detail. If you're concerned about duplicates, you will find different ways to get rid of them in this article.
Hi,
is it possible to use query:
=QUERY(kard!A4:AD;"select C where V = '" 10 "'and AD like '*' ";0)
but * will be all possibilities, or it could be a value selected by user.
I'm creating a report and the user can select 5 options, and all of them can have a value or be used like select all data.
How to do that?
Hi Rafal,
Have you tried it like this?
Hi Natalia, love the way you write!
Here goes my issue.
I have an official table containing the years a survey was performed. These data are arranged horizontally (A, B, C, ... etc). My idea was to get this data that will serve to compose the header of my statistical report (in another spreadsheet, course), but in descending order. So I used:
=query({ibge_data!B3:3};"select * order by 3 desc")
But it didn't work as expected. The problem, as you might have noticed, is that the ORDER BY clause doesn't work for sorting rows but only on columns (or at least if the data is arranged vertically);
So, after some insistence and testing, I managed to work around the problem with this:
=transpose(query({transpose(ibge_data!B3:3)};"select * order by Col1 desc"))
What gym huh?! Do you have any better ideas?
Hi Gumar,
Thanks for the feedback!
As for QUERY, I'm afraid, you'll have to use TRANSPOSE if you're to sort values in rows rather than columns.
how can I convert a column into text using Query+importrange formula in Select query, below is the formula details as its not working
=QUERY(IMPORTRANGE("","New Link Installations!A:AE"),"SELECT Col24, Text(Col26)",1)
Hello,
Sorry, I'm afraid I don't understand your task exactly. If you need to convert formulas to text, use the methods described here. If you need to convert dates, you may find the solution in this article.
Hello I am trying to write my function by selecting all of the values I have listed below and to not select 'Sold'. Can you see what is wrong with my formula?
"Select Col1, Col2, Col3, Col20, Col21 where Col2 contains 'Combine' or Col2 contains 'Truck' or Col2 contains 'ATV' or Col2 contains 'Sprayer' or Col2 contains 'Tractor' or Col2 contains 'Vehicles' and not Col3 contains 'Sold' "
Hello,
What error does your formula return? Try adding Order by as well to the end of your clause.
How would I correctly use the order by at the end of the clause?
How can I use group by when querying data from multiple sheets in the same googlesheet?
Hello Dennis,
Just as usual. The difference for multiple sheets is that you need to list the ranges from those sheets in the data argument, just like here.
I am doing a QUERY(IMPORTRANGE) from one sheet into another. I want the function to select rows from the source sheet where the values in the first and second imported rows equal values in two cell of the importing sheet. The value in the first cell, to match Col1, is a number.
The command =QUERY(IMPORTRANGE("18L1......","'RPCC Central Committee members'!C2:H12"),"select Col1,Col3,Col4,Col5,Col6 where Col1="&A4 &" "), it works fine to select rows where Col1 matches the value.
The value in the second cell, to match Col2, is a letter.
The command =QUERY(IMPORTRANGE("18L1.............","'RPCC Central Committee members'!C2:H12"),"select Col1,Col3,Col4,Col5,Col6 where Co21="&B4 &" ") produces the error: Error
Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: C
What am I doing wrong?
Hello Conrad,
I can see 'where Co21' in the second formula. This doesn't look correct and should be Col2 if I understand the task correctly. Please fix this first and see if the error disappears.
Hello! Thank you for this great resource. I'm wondering if it's possible to to have the function SELECT particular columns based on the text in the column header, rather than the column number. So that maybe if a column in the source spreadsheet is shifted (but retains the same column header), the Query function would still get the same data.
Thanks!
Hello Nick,
I'm afraid QUERY doesn't take column headers as identifiers. It works only with column IDs as in the examples in the article.
Hello
is it possible to get output if one of C5 or C6 is empty?
=QUERY(Sheet1!A1:G30, "select * where D='"&C5&"' and C='"&C6&"' ")
Any help is greatly appreciated. Thank you!
Hello Haris,
Yes. Please check out Example 4. Combine several conditions in this part of the tutorial.
As for your formula, I believe you need to replace and with or to retrieve the data based on either of the conditions.
- My main data tab has about 10 new rows added each day.
- My query tab has a query that pulls in certain things from each row, but still maintains each row and still adding about 10 new rows each day.
- To the right of my query, I have several columns that contain COUNTIF formulas, counting each time certain things happen in each row.
- The results from my COUNTIF formulas will feed into my Google Data Studio dashboard, and if I pull the formulas to the end of the page, zeros appear, which become a problem.
My question: Is it possible to have the COUNTIF formulas add new rows when the query adds new rows? Is there a way to turn my query into a table so the formulas fill in automatically, such as with excel tables? Or is there an alternate formula that I can pull to the bottom of the page and not get zeros?
Hi Amber,
There are a few things you can do:
Hello,
I am trying to write a query that only returns the columns where the matching row value is greater than 0.
Here is the part that works:
query('Sub Cap Mat Requirements'!$A$1:$P$422,"Select F, G, H, I, J, K, L, M WHERE A = '"&$C$3&"' AND
please could you help me finish it!
In one example I don't want column M returned as the row value is 0, but if I change the value in C3, then it may be column J that's 0 so that would be the column I would want omitted from the results.
Thanks in advance!
Hello Kahl,
I'm afraid your formula won't work. Here's another one for you to try:
=QUERY({A1:P422},"select "&ArrayFormula(JOIN(",","Col"&QUERY({TRANSPOSE({QUERY({A1:P422},"select * where Col1 = '"&C3&"' limit 1",1);ARRAYFORMULA(COLUMN(A1:P1)-1)})},"Select Col3 where Col2 <> 0",1)+1)),1)
Thank you!???
Does anyone know how to =Query from multiple sheets?
Hello Aekaterini,
I believe you will find the answer to your question here:
Google Sheets QUERY to import ranges from multiple sheets
Is there a way to limit the number of columns in the query pivot? I suppose the follow up to that would also be ordering it by the total of the pivoted columns
Hello George,
To return a particular number of columns, just specify those columns in the "select" command. Please see Example 2 in this part of the article.
Ordering is also possible and described here.