Learn Google Sheets QUERY Function: Step-by-Step Guide

Published on

The Google Sheets QUERY function empowers you to efficiently manipulate your data for various purposes with ease. It aids in extracting essential information from complex datasets and facilitating the creation of reports, charts, and more.

A notable advantage of this function is its capability to execute queries in the Google Visualization API Query language, enabling diverse data manipulations.

In essence, the QUERY function stands as the ultimate function for data manipulation in Google Sheets, and this tutorial comprehensively covers everything needed to master it.

QUERY Function: Syntax and Arguments

Let’s begin with the syntax of the QUERY function in Google Sheets.

Unlike most other functions, understanding how to use it isn’t straightforward just by reviewing the arguments. Not to worry, though. You’ll gain a clear understanding through my examples and step-by-step guides.

Syntax:

QUERY(data, query, [headers])

Arguments:

data: The range of cells (table) on which to perform the query. It’s essential to avoid mixed-type data in columns to ensure accurate results.

What is mixed-type data?

For instance, in column A, if you have item codes like the sequence 1111, 1112, 1113, etc., ensure that you format all of them as either numbers or texts, but avoid mixing both types. Essentially, a column designated as a number type should exclusively contain numbers, a date type column should solely contain dates, and a text type column should contain only texts.

query: The query you want to run must be enclosed in double-quotes. Alternatively, you can enter the appropriate text in a cell and refer to that.

headers: The number of header rows in your data (table). Typically, there will be one header row or none. If this argument is omitted or set to -1, the header value is guessed based on the content of the source data.

Example:

=QUERY(A1:F1000, "Select A, C", 1)

In this example, A1:F1000 represents the data, “Select A, C” is the query, and 1 indicates the header.

The formula returns columns A and C.

To master the Google Sheets QUERY function, one must concentrate on understanding the query argument, which is a broad topic.

Simplifying the ‘Query’ Argument

When you read a QUERY formula written by someone, the most confusing part will be the query segment within quotation marks. This is because it contains language clauses (keywords), data manipulation functions, and language elements.

Attempting to learn them all in one tutorial is not practical. Therefore, our approach here is to guide you in learning essential data manipulation techniques using the Google Sheets QUERY function.

In the process, I will direct you to where to find advanced tutorials on this blog to master one of the most powerful functions in Google Sheets. Here we go!

Sample Data for Testing the QUERY Function

We will consider sample data spread across six columns and sixteen rows in the range A1:F16. The field labels in A1:F1 are as follows:

ABCDEF
1NameDOBAge GroupGenderDate of JoiningAge

It’s in the ‘Sheet1’ tab. You can obtain a copy of the full data by clicking the button below.

Sample Data

We will use the data found on the sample Sheet to learn the QUERY function. Before starting, please scroll up and review the syntax.

There are three arguments in the syntax. The first and last ones are data and headers, respectively.

So, as per my sample Sheet, the data will be A1:F (open range) or A1:F16 (closed range), and the headers will be 1. Below, we will focus on the query argument.

Selecting Columns in the QUERY Function (SELECT Clause)

This section covers how to select columns in the QUERY function.

Basic Usage Examples of Google Sheets QUERY Function

In our sample data above, consisting of six columns, you can manipulate and select specific columns using the SELECT clause (keyword) in the QUERY function.

=QUERY(Sheet1!A1:F16, "select A, B, F, C, D, E", 1)

The formula above returns the columns in a different order.

=QUERY(Sheet1!A1:F16, "select A,  E", 1)

This formula is used for returning only columns A and E.

If you replace “select A, E” with “select *”, all columns of the data source table are returned in their original order.

An alternative to returning all columns in their original order is the following formula:

=QUERY(Sheet1!A1:F16,, 1)

Here, the query argument is omitted. In this formula, only the data and headers are specified.

If you specify 16 in the headers instead of 1, the formula will return a 1-row x 6-column output, with all values as a header row. For more information, you can read about it here: The Flexible Array Formula to Join Columns in Google Sheets.

Selecting Results from Aggregation Functions, Scalar Functions, and Arithmetic Operators

Above, we have explored selecting existing fields/columns in the table without any manipulation. Below, let’s see how to process the values in the columns and return results.

Scalar Function Year in SELECT Clause:

=QUERY(Sheet1!A1:F16, "select A, year(E)", 1)
Scalar Function in SELECT clause

This formula returns the names in column A and the year of joining in column E. Column E in the source data table contains the date of joining.

Aggregation Function Avg in the SELECT Clause:

You can use this formula when you want to return the average of values in column F, which contains the age.

=QUERY(Sheet1!A1:F16, "select avg(F)", 1)

Scalar Function Year and Subtraction Operator in the SELECT Clause:

To find the age at the time of joining, we can subtract the year of DOB from the year of joining. Here is an example.

=QUERY(Sheet1!A1:F16, "select A, year(E)-year(B)", 1)

Note: We are only considering years. Therefore, the age won’t be entirely accurate due to the whole months/years issue.

You can learn more about scalar functions, arithmetic operators, and aggregation functions in a later part of this tutorial below.

Column Identifiers

As the above examples demonstrate, column identifiers are typically one or two-character column letters, such as A, B, C, …. AA, AB, etc.

However, in certain scenarios, we may need to use identifiers like Col1, Col2, etc. Do you know when exactly this is necessary?

If your data is within a physical range like A1:F16, the column identifiers will be the standard capital letters – A, B, C, etc.

However, when dealing with an expression, such as the output of other formulas, the first column in the range becomes Col1, the second column becomes Col2, and so forth.

While discussing the selection of columns in the Google Sheets QUERY function, the following formula illustrates inserting a column between A and B, filled with hyphens:

=QUERY(Sheet1!A1:F16, "Select A, '-', B", 1)

Filtering Rows Using Google Sheets QUERY Function (WHERE Clause)

Above, we explored how to use the SELECT keyword in the Google Sheets QUERY function for selecting columns.

Now, what about filtering rows?

To achieve this, we should utilize the WHERE clause (keyword).

In the WHERE clause, we are required to use comparison operators (simple or complex) or logical operators (AND, OR & NOT) for filtering purposes.

In our sample data, to filter the gender “M”, we can use the following formula:

=QUERY(Sheet1!A1:F, "Select A, D where D='M' ", 1)
Google Sheets Query Function for Filtering Rows

The formula below filters names whose ages are 12:

=QUERY(Sheet1!A1:F, "Select A where F=12 ", 1)

The subsequent Query filters names whose joining dates are 01/01/2015:

=QUERY(Sheet1!A1:F, "Select A where E=date '2015-01-01'", 1)

The above three Query formula examples serve two purposes:

  1. To demonstrate how to use the WHERE keyword in the Google Sheets QUERY function.
  2. To illustrate the use of different types of values (literals), i.e., text, number, and date, for comparisons.

While these formulas provide insight into the usage of the WHERE keyword in the Google Sheets QUERY function, for comprehensive data manipulation, additional tutorials are HIGHLY recommended. Please explore the tutorials below in your leisure time.

Filtering Data: Resources

Literals:

Comparison Operators:

Logical Operators:

Other Filtering Tips

In the above examples, I haven’t given examples of how to filter a TRUE or FALSE Boolean value column because I don’t have such a column in my sample data.

If column G contains TRUE or FALSE, then use WHERE G=TRUE or WHERE G=FALSE.

Important:

String matching is case-sensitive in the QUERY function. Use UPPER() or LOWER() scalar functions to perform a case-insensitive string match.

Example:

=QUERY(Sheet1!A1:F, "Select A where lower(D)='m' ", 1)

You may have noticed one thing in the formula examples under the WHERE keyword.

I have used the open range Sheet1!A1:F instead of Sheet1!A1:F16 (closed).

It’s because we were filtering based on conditions that can remove blank rows from the result. To filter out blank rows straightaway, you can use IS NOT NULL as below.

=QUERY(Sheet1!A1:F, "Select A, E, F where A is not null", 1)

Grouping and Aggregation (GROUP BY Clause)

We have learned how to select and filter columns. This section delves into how to group and aggregate data using the QUERY function.

For this purpose, we utilize the GROUP BY clause (keyword combination), which is also a comprehensive topic, akin to the WHERE clause.

Let’s explore a few basic examples first. Then, I will guide you on where to find advanced usage examples of this feature.

Key Points:

Here are important considerations when using the GROUP BY keyword combination in the Google Sheets QUERY function:

  • It aggregates values across the rows.
  • A single row is created for each distinct combination of values in the GROUP BY clause.
  • The data is automatically sorted by the grouping columns.
  • All the column identifiers specified in the SELECT clause must be part of the GROUP BY clause. However, you can include unspecified column identifiers as well.

Examples Using Aggregation Function

The following formula groups the data by age and returns the count.

=QUERY(Sheet1!A1:F16, "Select F, count(F) group by F", 1)

And this one groups data by age and date of joining and returns the count.

=QUERY(Sheet1!A1:F16, "Select F, E, count(F) group by F, E", 1)

I’ve used the count aggregation function in the formulas. We can use many other aggregation functions. I’ll cover that later.

If you use an open range, remember to use the WHERE keyword to filter out blank rows in the aggregation.

=QUERY(Sheet1!A1:F, "Select F, count(F) where F is not null group by F", 1)
Aggregating Data (Data Manipulation Techniques)

Examples Using Scalar Function

=QUERY(A1:F, "Select year(E), count(E) where E is not null group by year(E)", 1)

It groups the data by the year of joining and returns the count.

The formula uses the YEAR() scalar function to extract the year from the date of joining in column E.

Examples Using Arithmetic Operators

=QUERY(A1:F, "Select year(now())-year(E), count(E) where E is not null group by year(now())-year(E)", 1)

You can see the use of the subtraction arithmetic operator in the above formula. It groups by the number of years since joining and returns the count.

We must use the Query language clauses (keywords or keyword combinations) in a particular order and we are learning them in that order in this tutorial. You can read more about that here: What is the Correct Clause Order in Google Sheets Query?

Grouping and Aggregation: Resources

Most of them are similar in use. You may search this blog for tutorials on TODATE, QUARTER, UPPER, LOWER, and DATEDIFF functions.

Pivot Data Using the QUERY Function (PIVOT Clause)

I can help you understand the PIVOT clause (keyword) with three examples.

The following Google Sheets Query formula (in cell H1 in the screenshot below) groups the date of joining and gender and returns the count.

=QUERY(Sheet1!A1:F16, "Select E, D, count(E) group by E, D", 1)

So the result contains three columns: Date of Joining, Gender, and count Date of Joining.

Grouping and Pivot Difference in Google Sheets Query Function

Now look at the Query formula (in cell H9 in the screenshot above) that uses the PIVOT keyword.

=QUERY(Sheet1!A1:F16, "Select E, count(E) group by E pivot D", 1)

I’ve omitted column D in the SELECT and GROUP BY clauses, instead used in PIVOT.

Here also, the first column in the output will be the Date of Joining (column E). Then there will be two more columns since the pivot column D (Gender) contains two distinct values, i.e., “M” and “F.” The count (aggregated values) will be under these two columns.

The pivot reports are handy for preparing bar, line, column, or pie charts (data visualization) in Google Sheets.

Can we use the PIVOT keyword without the GROUP BY keyword in the QUERY function?

Yep! Here is an example.

=QUERY(Sheet1!A1:F16, "Select count(A) pivot E", 1)

Please test it yourself.

Here are some additional resources to further explore the PIVOT clause in the QUERY function in Google Sheets.

Sorting Data (SORTBY Clause)

With the help of the ORDER BY clause (keyword combo), we can sort the rows in the source data by the values in specified columns.

Example #1 (Order by item is a column Identifier):

=QUERY(Sheet1!A1:F16, "select A, B, F order by A asc", 1)

It sorts the names in column A in ascending order. The following formula sorts the DOB in column B in descending order and then the names in column A in ascending order.

=QUERY(Sheet1!A1:F16, "select A, B, F order by B desc, A asc", 1)

Example #2 (Order by item is the output of the count aggregation function):

=QUERY(Sheet1!A1:F, "Select F, count(F) where F is not null group by F order by count(F) desc", 1)

Note: You can use two types of items in the ORDER BY keyword: 1) Column identifiers. 2) Result of aggregation functions, scalar functions, or operators.

Limiting the Number of Rows (LIMIT Clause)

The purpose of the LIMIT clause (keyword) in the Google Sheets QUERY function is to limit the number of returned rows.

In the formula just above, we can use LIMIT 3 to constrain the number of rows returned by it to 3 rows, excluding the header.

Example:

=QUERY(Sheet1!A1:F, "Select F, count(F) where F is not null group by F order by count(F) desc limit 3", 1)

Offset Rows in Google Sheets QUERY Function (OFFSET Clause)

The purpose of the OFFSET clause (keyword) is to skip a given number of first rows.

Example #1:

=QUERY(Sheet1!A1:F, "Select * offset 3", 1)

Example #2:

=QUERY(Sheet1!A1:F, "Select F, count(F) where F is not null group by F order by count(F) desc limit 3 offset 1", 1)

In the second example above, we have also used the LIMIT keyword. The Query first offsets rows and then applies the limit.

Labeling and Formatting Query Result (LABEL and FORMAT Clauses)

Sometimes the Query formula may return ‘ugly’ headers (labels). Please see the H1 formula result in the top part of the screenshot below.

Renaming Labels in the Header

The LABEL clause (keyword) helps set the label for one or more columns. So we can rename the ‘ugly’ label as follows.

=QUERY(Sheet1!A1:F16, "select A, year(E)-year(B) label year(E)-year(B) 'Age at the time of Joining'", 1)

It will replace the label difference(year(Date of Joining)year(DOB)) with Age at the time of Joining.

Related: Understand the Label Clause in Google Sheets Query.

Conclusion

This post comprehensively covers everything you need to master the QUERY function in Google Sheets. Feel free to drop any doubts or questions in the comments below.

Reference: Query Language Reference (Version 0.7)

Thanks for your stay. Enjoy.

Related:

  1. The Alternative to SQL IN Operator in Google Sheets Query (Also Not IN).
  2. How to Use Date Values (Date Serial Numbers) in Google Sheets Query.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

How to Retrieve the Last Record in Each Group in Excel

As part of data analysis, you may need to retrieve the last record from...

Filter Data with Multi-Select Drop-Downs in Google Sheets

If you've started using multi-select drop-downs in Google Sheets, you may encounter challenges when...

More like this

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

Filter Data with Multi-Select Drop-Downs in Google Sheets

If you've started using multi-select drop-downs in Google Sheets, you may encounter challenges when...

35 COMMENTS

  1. Hi Prashanth,

    I have a Google Sheets file (SPREADSHEET 02) where I am using a simple QUERY + IMPORTRANGE function to import data from another Google Sheets (SPREADSHEET 01).

    =QUERY(IMPORTRANGE("SPREADSHEET 01 URL","Sheet1!A1:E"),"SELECT * WHERE Col4='✓'")

    Let’s say I am doing a check to each result in a new column (column 5) in SPREADSHEET 02 by adding the text “DONE”.

    Now, if the row 2 in SPREADSHEET 01 turns the “X” to “✓”, it will ruin my checking progress.

    My question is: Is there a function that can fix this issue?

    • Hi, Nick,

      It’s possible! For example, I want to extract values in row # 5.

      Range (Data): D1:E (D1:E1 contains field labels).

      Solution # 1: Using Offset and Limit caluses.

      =query(D1:E,"Select D,E limit 1 offset 3 ",1)

      or

      =query({D1:E},"Select Col1,Col2 limit 1 offset 3 ",1)

      The formula offsets three rows + header row. So you will get the fifth row.

      Solution # 2: By adding row number column.

      =ArrayFormula(query({row(D1:D),D1:E},"Select Col2,Col3 where Col1=5",1))

  2. I have a query that I’m trying to eliminate returning 0s, but because they’re not quite 0 (infinite number).

    I can’t seem to filter them out of my query correctly. Is there a way to get around this?

  3. How can we sort a column in descending order in the query function for % values? 100 % is not getting at the top.

    =QUERY('Negative_Report - Data'!A6:M141,"Select A,H Order by H DESC LIMIT 20")

    H is the percentage column.

  4. Hello Prashanth,

    May I ask you to help me with this formula?

    I’ve copied it from another GS spreadsheet and thought that is working well, but recently found that this formula is using similar results to make a “decision” of what to select. I’m almost sure that it’s because of Wildcard symbols.

    I was googling something like “google sheets query formula equals to comparison operator” but haven’t found anything useful.

    Here is the formula:

    =IF(ISTEXT(O3),QUERY('Sheet2'!$A$2:'Sheet2'!$X$200, "select C where A like '%" & O3 &"%' limit 1",""))

    It is selecting from C, but if in A column it finds “02.03.00.124” firstly and “02.03.00.12” after, so the result of C would be from first A (02.03.00.124).

    I just need something like “select C where A equals to “O3”, but it doesn’t work, obviously 🙂

  5. I have 2 Columns.

    “SEO 12”
    “SEO, Retails 12,34”
    “E-commerce 56”

    I need to return values from Column A containing 12. I need to get “SEO” and “SEO, Retails”.

    How do I do it?

    • Hi, Abhidith Shetty N,

      Use the CONTAINS clause.

      =query(A1:B,"Select * where A contains '12'")

      You can find related tutorials (CONTAINS, MATCHES, and LIKE complex comparison operators) on this blog.

      The FILTER function will also work.

      =filter(A1:B,regexmatch(A1:A,"12"))

  6. Hello there. Nice tutorial.

    I am attempting a query without multiple where clauses. This works as I expect.

    =QUERY(TravelPlus!A2:U110,"SELECT G WHERE ((lower(A) = 'john smith')) AND ((lower(B) = 'july 26 - august 25')) limit 1", false)

    Then, I’d like to replace the second clause ‘july 26 – august 25’ with a cell reference. Based on searching around, I think it should be this, but I receive a formula parse error.

    =QUERY(TravelPlus!A2:U110,"SELECT G WHERE ((lower(A) = 'john smith')) AND ((lower(B) = "'&A1&'")) limit 1", false)

    Any idea what I might be doing wrong?

    • Hi, Chris C,

      You were very close!

      Here is the correct Query formula.

      =QUERY(TravelPlus!A2:U110,"SELECT G WHERE lower(A) = 'john smith' AND lower(B) = '"&A1&"' limit 1", false)

      Search the term “literals” (using Ctrl+F [browser search not my blog search]) within this Query function tutorial and you can find a related link.

  7. Can you combine a WHERE clause with a MATCH? For example, I want the column being evaluated to be a variable in a dropdown. I’ve tried this and it returns a #N/A (empty result).

    =query({$C$2:$F},"Select Col1 where 'Col"& match($A$2,$C$2:$F$2,0)&"' ''",1)

    If I highlight just the match function, it gives me a correct result (1, 2 or 3) and if I swap the match out with, say, Col2, the query works fine.

    Does match not work in a where clause of is my syntax wrong?

    • Hi, Chris Rosendin,

      Here is the correct usage of the Match spreadsheet function within the Query Where clause.

      =query({$C$2:$F},"Select Col1 where Col"&match($A$2,$C$2:$F$2,0)&"<> ''",1)

  8. I’m having an issue when I use this function with IMPORTRANGE. It doesn’t copy over any cells if they have special characters, for example, Zipcodes or phone numbers with ( ) or -

    Those cells do come over fine if I just do IMPORTRANGE so I am wondering what the workaround is to still get that when using Query to only pull data over "where Upper (col4)='X'"

    Thanks.

    • Hi, Chanel,

      If you have permission to edit the source file;

      Format the column that contains the Zipcodes, special characters, etc. to text (Format > Number > Plain text).

      Then use the Query as per the below example.

      =query(importrange("URL","Sheet1!A1:E10"),"Select * where upper(Col5)='X'")

      Please use Col5, not col5.

      If don’t, use the To_text function within your Query ‘data’

      Example:

      I am importing the column range A1:E10 if column E contains “X”. In this, column D contains special characters.

      =ArrayFormula(query(to_text(importrange("URL","Sheet1!A1:E10")),"Select * where upper(Col5)='X'",1))

      This has one drawback. It will convert all the outputs to text format.

  9. Do you know of a workaround for the missing SQL IN condition in the Google Sheets Query Function, e.g. "SELECT * FROM tbl_country WHERE country_code IN ('UK', 'DE','AT')"?

  10. I have a question. I’d love help. Long story short, I have a sheet that shows lists of students and the bus that they ride on for our school.

    Example: Teacher A’s Class
    Column A: Student
    Column B: Bus Number

    On a separate sheet, I want to create a query so that only the students on Bus 1 show up on that sheet. Then another sheet where only students on Bus 2 show up. I am struggling to figure it out. Any help or recommendations for how to learn this would be awesome!

    • Hi, Rachel,

      I assume the list of students is in Sheet1 (tab name) and the first row in that contains the column names (field labels).

      Here is the Query to filter the Students on Bus 1. Enter this Query in Sheet2.

      =query(Sheet1!A1:B,"Select A where B=1",1)

      If the bus numbers are text strings like Bus 1, Bus 2 then use the below Query.

      =query(Sheet1!A1:B,"Select A where B='Bus 1'",1)

      You can change B=1 to B=2 or B='Bus 1' to B='Bus 2' to filter the students on Bus 2.

      Best,

  11. Hi, I am working on a translation glossary of terms for myself for my job and was hoping to use the Query function to separate and show the term you’re looking for by typing it in.

    Unfortunately, I find this guide to be pretty vague and the descriptions of how to use each function/equation quite lacking. Am I just not using the right function, or am I using this function incorrectly?

    query(DataList!C1:D1,"select A,B,C,D where C='"&E3:E1000&"'")

    C1:D1 is where I want my search box, E3:E1000 are the cells in which I have the tags (denoted by # much like on Instagram) pertaining to each vocabulary term. C3:D1000 would be the locations for the terms in each language.

    Thank you.

    Michelle

    • Hi, Michelle V.,

      This part of the Query Where clause is wrong.

      where C='"&E3:E1000&"'

      You can only specify one cell (one criterion) this way like;

      where C='"&E3&"'

      If you want to use several criteria, then you may use the Matches regular expression match in Query.

      To give you a correct formula, I may want to see your Sheet. If your Sheet contains personal data, instead share a demo Sheet with some mockup data.

      Best,

  12. I’m trying to combine data from multiple tabs to create a master product database. There is a chance we will add new products in the future and I want to know if there is a way to get the “data” section of the Query Function to automatically expand with the new tab names, rather than adding the tab names into the master query equation. I’m open to creating a database of tab names, but thus far have not had success getting the data section to recognize text within a cell as an address.

  13. I’m trying to query multiple fields but having an issue. i.e,

    =QUERY(Sheet1!A2:BQ,"select B,C,U,W where AC='Yes'")

    and

    =QUERY(Sheet1!A2:BQ,"select B,C,AE,AG where AM ='Yes'")

    So if AC is true I need 5 fields, if AM is true I need the same 2 fields and 3 different fields, etc. (there are 5 in total). And I’d like to them order by B desc. I can do multiple separate query’s but not all together. I thought I could use an or but it’s not working.

    • Hi, Mustom,

      It was time taking still I have managed create an example Sheet based on your formula. Your formulas seem working independently. But I could not find the logic. I am unsure whether the same row in AC and AM contains “Yes”.

      I may be able to help if you leave an example sheet and in that the output that you are expecting.

      Best,

    • Hi, Nico,

      I understand you are talking about Query Headers. It’s optional and useful when your data has headers in multiple rows. Also, there is a tricky way of using it to combine columns – The Flexible Array Formula to Join Columns in Google Sheets.

      Now regarding your question, my answer is “it does work”. But you may use a comma instead of semicolon but that depends on your locale setting (File menu > Spreadsheet setting).

      Without seeing what you are trying to achieve and your locale, I can’t comment further.

      Thanks.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.