Everything on Google Sheets QUERY Formula
With 9+ years in SEM and data automation, Piyush Nair helps businesses and individuals simplify processes using Google Sheets, Excel, Apps Script, and other productivity tools, turning data into actionable insights
How this article is generated : This article was written by a human author and AI is used in proofread.
Introduction
SQL knowledge is a plus but not mandatory to know how to use query in google sheets. This article includes all sort of examples, common issues and their solutions. Bookmark this blog as a one stop solution as it comprises knowledge of my entire career related to Google Sheets.
Let’s checkout the list of topics covered in this article
- What is Query function in Google sheets and understanding its Syntax
- How to use Query function to pull data from one tab to another tab in the same Google Sheet
- How to use Query function to pull data from one sheet to another Google Sheet
- Use of SELECT syntax in QUERY formula
- Use of WHERE syntax in QUERY formula
- Use of ORDERBY syntax in QUERY formula
- How to filter rows using QUERY formula with text based condition
- How to filter rows using QUERY formula with date range condition
- How to filter rows using QUERY formula between two dates
- How to QUERY formula with a cell reference
- QUERY formula returning no headers or duplicate headers
What is Query function in Google sheets and understanding its Syntax
Query formula in Google Sheets is a very powerful tool to have in your data analysis arsenal. It is similar to SQL language as both has same purpose which is to bring limited amount of data which is required in your data analysis and that too in a specific required format.
=QUERY(data, query, [headers])
Data : It means where is your entire data dump upon which we are applying the QUERY formula
query : Here you enter your query which is similar to SQL language
Headers : It is optional as Google Sheets automatically detects header count but I must say always use this field in complex query
Example of Simple Query formula in Google Sheets
How to use Query function to pull data from one tab to another tab in the same Google Sheet
In Raw-Data tab we have data as below
Now we want to query entire data present in “Raw-Data” tab into Sheet “QUERY” tab
How to use Query function to pull data from one sheet to another Google Sheet
when your raw data tab and tab in which you are applying query formula are in same Google Sheet its easy, but what to do when your raw data in present in another Google Sheet ?
Lets checkout what to do in that situation
We have to use IMPORTRANGE formula inside QUERY in data input and rest remains same
IMPORTRANGE(Complete sheet URL, Data Range)
Complete sheet URL = Just copy the Google Sheet url where your raw data is present and paste it in the QUERY formula between double quotes
Data Range = Select the entire data set on which you want to import into QUERY formula
Below is your output where have applied QUERY formula with IMPORTRANGE formula
After applying above formula if you receive below error then click on #REF! and click on Allow Access
Use of SELECT syntax in QUERY formula
SELECT clause is the backbone of query formula and without it QUERY does not know which column to fetch. So we can say if we without SELECT clause QUERY function cannot work. Let’s see its uses
SELECT ALL Data in QUERY
SELECT clause is always within double quotes. SELECT ALL means fetch all the columns you have in the defined data range.
We use * ( star symbol) to denote ALL. Hence in below example after Data Range we are mentioning “select *” , it means bring all the columns you have in ‘Raw-Data’!$A$1:$H$11 without any filters or conditions
=QUERY(‘Raw-Data’!$A$1:$H$11,”select *”,1)
SELECT only 2 Columns
Now let’s say we want only Columns B and H from the Raw-Data tab then formula will be
Use of WHERE syntax in QUERY formula
Now lets understand how to merger WHERE with SELECT and filter Columns and Rows together. From Raw-Data tab I want Customer Name, Category , Quantity and Price but only where Category = Electronics
Use of ORDER BY syntax in QUERY formula
ODER BY clause can only be written after SELECT and WHERE clause. ORDER BY clause is use to sort the data by any column of your choice with any any order (Ascending or Descending). For Ascending you have mention ASC and for descending you have to mention DSC inside the select query. After all the columns in select you have to mention order by clause and then column 1 sorting order, column 2 sorting order and so on.
Lets see how to use it –
How to filter rows using QUERY formula with text based condition
As we discussed above to filter rows in query output we have to use WHERE clause, lets check advance WHERE clause. We will filter rows based on 2 in text based condition and 1 value based condition and how to formulate the query function.
In this we are filtering rows for multiple categories : Electronics, Furniture and Clothing and Price > 500
How to filter rows using QUERY formula with date range condition
Always remember that when you want a date filter then you have to mention “date” before the date as in below formula and rest logic is same
How to filter rows using QUERY formula between two dates
In this overall logic of and and or is same but you will that every time i enter a date in the query i always date term before it. It is very important if you your date filters to work accurately
How to QUERY formula with a cell reference
All the formula above were static which means the conditions were hard coded in the query but what is the conditions will vary like in case of a dashboard where user will enter the details in a cell using a drop down and want to check data. Lets check how to embed a cell reference and make your query function a dynamic formula.
Important : If the data is a number like in this case B2 in this you don’t have to put single quote before and after ” like we are doing in case of B1. Else you output will be blank
QUERY formula returning no headers or duplicate headers
When you have multiple headers then importance of header option in query formula increases. By default Google Sheets consider first row as a header otherwise you have to specify to get get data accordingly
When you don’t enter any value in header section in the query formula
When you enter 1 in header field in query formula
Conclusion
This article include all kinds of scenarios which I have encountered in my 9 years of my career and honestly you should try QUERY fucntion in Google Sheets definitely save lot of your time.
More Blogs!!
| Learn Google Sheets – Basic to Advance |
| How Does VLOOKUP Work ? |
| Everything about Pivot Table in Google Sheets |