[\/et_pb_text][et_pb_text module_id=”syntax” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Syntax<\/h2>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=QUERY(data, query, [headers])[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
- \n
- data<\/strong> \u2013 is the reference to the range of cells on which we want to query upon.<\/li>\n
- query<\/strong> \u2013 is the text using which the QUERY function churns out the information we are looking for from the data set. Since it is expected to be a string, it has to be enclosed within a set of quotes. Or, it can also be a reference to a cell, where the query text is stored.<\/li>\n
- headers<\/strong> \u2013 is an optional parameter that indicates the number of header rows at the top of the data. If left out, Google Sheets guesses the value based on the content within the data<\/strong>.<\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_text module_id=”clauses” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Clauses and operators<\/h2>\n
The Query language used in Google Sheets QUERY function is a text-based language similar to SQL. The query uses clauses to perform actions. Below is a list of the possible clauses that you can use to perform a wide variety of actions on your data.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-1.png” alt=”google sheets query 1″ title_text=”google sheets query 1″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
If you use the QUERY function a lot, you\u2019ll also find that you make use of a number of logical operators to set conditions within the function. Below is a list of logical operators that you can use.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-2.png” alt=”google sheets query 2″ title_text=”google sheets query 2″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”basic” _builder_version=”4.20.2″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Basic QUERY functions<\/h3>\n
First of all, to understand how the function is put to use, consider the following sample data. It consists of information corresponding to a list of students who have enrolled in various courses at a university. In all the upcoming examples, I\u2019ll enter the function in the cell H1. It will also be displayed in the function bar in the snapshots.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-3.png” alt=”google sheets query 3″ title_text=”google sheets query 3″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example #1: Select columns based on a condition<\/h4>\n
I will start off with a very basic demonstration, and add more complexity throughout the article in order to help you understand the function step-by-step. To start with, I\u2019ll use the QUERY function to fetch the names of the students who are residing on campus.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=QUERY(A1:F15,”Select A where F = ‘Yes'”,1)<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-4.png” alt=”google sheets query 4″ title_text=”google sheets query 4″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Before moving on, I\u2019ll go over some of the basics of QUERY in this first example. To start with, notice that the data selected includes the headers \u2013 these will be used as part of the output from the function. You can see that the function returned not only the names of the students who are residing on campus, but also the Name header, which is very helpful for keeping track of what the results mean!<\/p>\n
After selecting the data, the main part of the query is in quotation marks as a text string command. I\u2019ve used two of the clauses listed in the chart above: Select and Where. The \u2018select\u2019 command is used to return columns \u2013 telling the function to \u201cSelect A\u201d leads to an output of the entire column A.<\/p>\n
The \u2018where\u2019 clause allows you to add a condition. Here, I\u2019ve added the condition that the F column should equal \u201cYes\u201d. For the rows that the F column is \u201cNo\u201d, the function ignores that data and does not include it in the output.<\/p>\n
Combining the two clauses into one query, I\u2019ve typed \u201cSelect A where F = \u2018Yes\u2019\u201d. The is essentially telling the function to return all the names (from column A) of students who live on campus (data in column F is \u2018Yes\u2019).<\/p>\n
Tip: If you\u2019re selecting all the columns in the data set, you can type Select * to quickly achieve this instead of listing out each column!<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example #2: Select columns based on a condition<\/h4>\n
Now I\u2019ll show the same example, but finding the students who are NOT on campus.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=QUERY(A1:F15,”Select A where F <> ‘Yes'”,1)<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-5.png” alt=”google sheets query 5″ title_text=”google sheets query 5″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
The only difference here is that I\u2019ve changed the logical operator in the \u2018where\u2019 clause to <>, which means \u201cnot equal\u201d. So I am asking the function to give me the list of names of students that do NOT have a \u2018Yes\u2019 in the F column.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example #3: Select multiple columns<\/h4>\n
Now I\u2019ll fetch the names, ages, departments of the students whose have taken more than 7 courses.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=QUERY(A1:F15,”Select A, B, C where D > 7″,1)<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-6.png” alt=”google sheets query 6″ title_text=”google sheets query 6″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Again, I start with the Select clause, but this time I select multiple columns by listing them separated by commas. I\u2019ve chosen columns A, B, and C to be selected. I\u2019ve also changed the Where clause to filter out the data of students who have less than 7 courses.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example #4: Multiple WHERE conditions<\/h4>\n
I will now take this a step further. I\u2019ll bring up the names, departments, and join dates of the students aged 25 or below who have joined the university between 25-Dec-2016 and 20-Jan-2017. Please note, in the query text, the dates always have to go with yyyy-mm-dd format, enclosed within single quotes.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=QUERY(A1:F15,”Select A, C, E where B <= 25 and E >= data ‘2016-12-25’ and E <= date\u00a0 = ‘2017-01-20′” ,1)<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-7.png” alt=”google sheets query 7″ title_text=”google sheets query 7″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
As you can see, you can add multiple Where conditions simply by separating them with \u201cand\u201d. This allows you to filter data in any way you can imagine!<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example #5: Referencing cells<\/h4>\n
What if you need to reference the date from a cell? No problem there! You can do so with the help of concatenating operators and a text function. By doing this in the example below, you will get the names and join dates of the students who joined after 1-Jan-2017.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=QUERY(A1:F15,”Select A, E where E > date ‘” & TEXT(I1,”yyyy-mm-dd”)&”‘”,1)<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-8.png” alt=”google sheets query 8″ title_text=”google sheets query 8″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
An important thing to note when using dates in your query is that dates can only be in the yyyy-mm-dd format, and must be enclosed in single quotation marks and preceded by the string \u201cdate\u201d. So to denote January 1 2020 in your query, you\u2019d need to type: date \u20182020-01-01\u2019.<\/p>\n
[\/et_pb_text][et_pb_text module_id=”arithmetic” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Arithmetic and Aggregation Functions<\/h3>\n
In addition to the various clauses listed above, you can use regular arithmetic functions within your queries. Operations like addition, subtraction, multiplication, and division, as well as aggregation function sum, average, count, max, and min are all fair game.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example #6: Multiplicaction and addition<\/h4>\n
I\u2019ll start off by demonstrating a few simple arithmetic functions \u2013 multiplication and addition. In this example, I want the age to be in months instead of years (so I\u2019ll multiply by 12) and I want to add one credit for each student.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=QUERY(A1:F15,”Select C, (B*12), (D+1)”,1)<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-9.png” alt=”google sheets query 9″ title_text=”google sheets query 9″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
As you can see, all you have to do is perform the operation on the column of your choosing. The output isn\u2019t very pretty, but we\u2019ll discuss how to change the headers and formatting later on in this guide.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example #7: Average<\/h4>\n
Now I\u2019ll show an aggregation operation \u2013 the average function. The average of a column is accessed with the abbreviation \u2018avg\u2019. You also need to tell the function which items to average together \u2013 i.e. how you want the data grouped. That\u2019s why I\u2019ve also told the function to group the data by column C (dept), using the Group by clause.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=QUERY(A1:F15,”Select C, avg(B) group by C”, 1)<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-10.png” alt=”google sheets query 10″ title_text=”google sheets query 10″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
You\u2019ve now seen the QUERY function use basic arithmetic functions like multiplication and addition, as well as aggregation operations such as average.<\/p>\n
[\/et_pb_text][et_pb_text module_id=”complex” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Complex QUERY functions<\/h3>\n
Now that you\u2019ve seen some basic examples using the Select and Where clauses to filter a data set, as well as some arithmetic functions, I\u2019ll show some more complex examples using a variety of clauses and operations.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example #8: Select, Sum, and Group by <\/h4>\n
You can use a combination of Select, Sum, and Group By to list out all the departments and the display the number of courses taken from each department. The Group By clause is used alongside aggregation functions (such as Sum) to tell the function how the data should be grouped and summed \u2013 otherwise the aggregation function doesn\u2019t work.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=QUERY(A1:F15,”Select C, sum(D) group by C”, 1)<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-11.png” alt=”google sheets query 11″ title_text=”google sheets query 11″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example #9: Label and Sort<\/h4>\n
In the previous example, you\u2019ll notice that the QUERY function returned the second column with the header \u201csum Courses\u201d. Honestly, this is a bit awkward \u2013 luckily, you can fix that by renaming it. In addition to that, I will also use the second column (now renamed to \u2018Courses Taken\u2019) to sort in ascending order. Here\u2019s how to do it.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=QUERY(A1:F15,”Select C, sum(D) group by C order by sum(D) label sum(D) ‘Courses Taken'”, 1)<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-12.png” alt=”google sheets query 12″ title_text=”google sheets query 12″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example #10: Count<\/h4>\n
Can you display the number of instances in which student is taking courses from each department? Of course! The QUERY function has you covered here as well \u2013 you can use the count operator to achieve this.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=QUERY(A1:F15,”Select C, count(D) group by C label count(D) ‘# Instances'”, 1)<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-13.png” alt=”google sheets query 13″ title_text=”google sheets query 13″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Instead of using the Sum operator to add up all the courses taken, I\u2019m using the Count operator to count the number of occurrences of a student taking any number of credits within each department. I\u2019m also again using the Select, Group by, and Label clauses to get the output I want.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example #11: Limit and Order by<\/h4>\n
The Limit clause limits the results to a specified number. For example, in the screenshot below I limit the output to 10 rows. This clause is often used in conjunction with the Order by clause, which puts the data into ascending or descending order.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=QUERY(A1:F15,”Select A, B order by B asc limit 10″, 1)<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-14.png” alt=”google sheets query 14″ title_text=”google sheets query 14″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Here, I\u2019ve used the Order by clause to sort the data from lowest age to highest age, then used the Limit clause to limit the output to 10 \u2013 giving me the 10 youngest students.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example #12: Pivot<\/h4>\n
The Pivot clause essentially allows you to create your own pivot tables using the QUERY function. In the example below, I\u2019ve taken a previous example and pivoted it by column A. The result is that the names of the students are now the headers, with the data I\u2019ve selected in the rows below each name.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=QUERY(A1:F15,”Select C, sum(D) group by C pivot A”, 1)<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-15.png” alt=”google sheets query 15″ title_text=”google sheets query 15″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Using the Pivot clause is a somewhat more advanced use of queries, but if you play around with it yourself, you\u2019ll discover the many uses of it!<\/p>\n
[\/et_pb_text][et_pb_text module_id=”using” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Using ‘OR’ in queries<\/h3>\n
So far, you\u2019ve seen a lot queries that use \u2018and\u2019 to add additional criteria to the query. But you can also use \u2018or\u2019, just like in other functions in Google Sheets such as IF statements.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example #13: Using OR in a query<\/h4>\n
This example is a replica of an earlier example, but instead of specifying that the age needs to be 25 or younger AND the join date must be between the dates specified, I am specifying that any data point that meets EITHER of these criteria, will pass the filter and appear in the output.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=QUERY(A1:F15,\u201dSelect A, C, E where B <= 25 or E >= data \u20182016-12-25\u2019 and E <= date\u00a0 = \u20182017-01-20\u2019 ,1)<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-16.png” alt=”google sheets query 16″ title_text=”google sheets query 16″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”headers” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Headers<\/h3>\n
So far, all the examples have been with a single row of headers in the data, and a \u20181\u2019 in the header parameter within the QUERY function. Now, I\u2019ll show you what to do if you have headers spanning multiple rows.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example #14: Multiple rows of headers<\/h4>\n
The header<\/strong> parameter is an optional input that comes in handy when your headers span across multiple rows. In such cases, this parameter helps you easily combine the headers in one single row, as shown below.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=QUERY(A1:F16,”Select A, B, C, D where F <> “No””, 2)<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-17.png” alt=”google sheets query 17″ title_text=”google sheets query 17″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
All you have to do is put a \u20182\u2019 as the header<\/strong> parameter after the query! However many rows your headers span across, just put that number in the header<\/strong> parameter \u2013 it\u2019s as simple as that!<\/p>\n
[\/et_pb_text][et_pb_text module_id=”query” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Query multiple tabs or sheets<\/h3>\n
What happens when your data is spread across multiple tabs on your spreadsheet, or even on a different sheet? You can still use the QUERY function across these multiple tabs and sheets!<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Example #15: Query across tabs or sheets<\/h4>\n
In this example, you can see how to use the QUERY function across multiple tabs. The key is to have the data in the same format in all sheets \u2013 so in this example, the ages need to in the second column in both sheets. Then, the format changes a bit \u2013 instead of referring to columns by their letter (column A, B, etc.) you\u2019ll need to refer to them by number (Col1, Col2, etc.).<\/p>\n
Lastly, you\u2019ll also need to enclose the data ranges in curly braces {} and separate them with a semicolon. Different tabs or sheets are identified with the sheet name followed by an exclamation mark, then the range of cells within the sheet. All this makes more sense when you look at the example below!\n<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=QUERY({Sheet4!A1:F7;Sheet3!A1:F9}, “Select Col1”)<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/google-sheets-query-18.png” alt=”google sheets query 18″ title_text=”google sheets query 18″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Without a doubt, this is one of the complex functions to master in Google Sheets. It could be well worth your time to get a good handle on this one, since it is without a doubt one of the most potent tools in Google Sheets. We encourage you to further explore the QUERY<\/a> function here<\/a>.<\/p>\n
Make sure to check out this relevant blog post about How to create pivot tables in Google Sheets<\/a>.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
The Google Sheets\u00a0QUERY function is a very powerful and versatile tool. So much so that you can call it a one-stop-shop for all your logical, lookup, summation, counting, averaging, filtering and sorting requirements. The function fetches specific information from a data set through a query statement, much like fetching result sets from a database using […]<\/p>\n","protected":false},"author":40,"featured_media":43311,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"The QUERY<\/a> formula in Google Sheets is quite a powerful and versatile function. So much so that, we can call it a one-stop-shop for all the logical, lookup, summation, counting, averaging, filtering and sorting requirements. It helps us fetch specific information from a data set, through a query statement. Much like fetching result sets from a database using queries. The syntax of the query language<\/a> used in this formula is similar to SQL.\n
Syntax<\/h3>\nQUERY(data, query, [headers])<\/span><\/strong>\n
- \n \t
- data<\/strong><\/span> - is the reference to the range of cells on which we want to query upon.<\/li>\n \t
- query<\/strong><\/span> - is the text using which the QUERY formula churns out the information we are looking for from the data set. Since it is expected to be a string, it has to be enclosed within a set of quotes. Or, it can also be a reference to a cell, where the query text is stored.<\/li>\n \t
- headers<\/strong><\/span> - is an optional parameter that indicates the number of header rows at the top of the data. If left out, Google Sheets guesses the value based on the content within the data<\/strong><\/span>.<\/li>\n<\/ul>\n
Usage: Query Formula<\/h3>\nFirst of all, to understand how the formula is put to use, let us consider the following sample data. It consists of information corresponding to a list of students who have enrolled into various courses at a university. For all the demonstration purposes, we'll enter the formula in the cell G1. And, it will be displayed in the formula bar in the snapshots.\n\n
![\"QUERY](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/query1.png\")
\nExample # 1:<\/h5>\nWe will start off with a very fundamental demonstration. So, we use the QUERY formula to fetch the names of the students who are residing on campus.\n\n
![\"QUERY](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/query2.png\")
\nExample # 2:<\/h5>\nHaving dealt with the basic example, let us now try fetching the names of the students who are NOT residing on campus.\n\n
![\"QUERY](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/query3.png\")
\nExample # 3:<\/h5>\nNow, we will fetch the names, ages, departments of the students whose have taken more than 7 courses.\n\n
![\"QUERY](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/query4.png\")
\nExample # 4:<\/h5>\nWe will now attempt taking this a step further. We bring up the names, departments, join dates of the students aged 25 or below, and have joined the university between 25-Dec-2016 and 20-Jan-2017. Please note, in the query text, the dates always have to go with yyyy-mm-dd format, enclosed within single quotes.\n\n
![\"QUERY](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/query5.png\")
\nExample # 5:<\/h5>\nWhat if we need to reference the date from a cell? No problem there! We will get around with the help of\u00a0 concatenating operators and a text function. Therefore, in the example below, we will get the names and join dates of the students whose joined after 1-Jan-2017.\n\n
![\"QUERY](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/query6.png\")
\nExample # 6:<\/h5>\nIs there a way to list out all the departments and the display number of courses taken from the respective department? Yes, there is! And, we might as well understand the power and versatility that the QUERY formula offers.\n\n
![\"QUERY](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/query7.png\")
\n\nYou will notice that the QUERY formula returned the second column with the header \"sum Courses\". Honestly, it is a bit awkward to have that for a header. But, we can fix that and rename it. Not only that, we will also use the second column (now renamed to 'Courses Taken') to sort in ascending order. Here is how we do it.\n\n![\"QUERY](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/query8.png\")
\nExample # 7:<\/h5>\nCan we display the number of instances of each of the departments? Of course, we can! The QUERY formula got us covered here as well.\n\n
![\"QUERY](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/query9.png\")
\nExample # 8:<\/h5>\nConsequently, we will now experiment with the third parameter. While this is an optional input, it might come handy when we come across headers that span across multiple rows. In such cases, this parameter helps us combine the headers in one single row, as shown below.\n\n
![\"QUERY](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/query10.png\")
\n\nWithout a doubt, this is one of the complex formulas to master in Google Sheets. Get a good hang on this one and it could serve you as one of the most potent tools. We encourage you to further explore the QUERY<\/a> formula here<\/a>.\n\nCheck out a relevant blog post about \"Why You Need To Structure Your Spreadsheets Like A Database<\/a><\/strong>\".","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-2762","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/2762","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/comments?post=2762"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/2762\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/43311"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=2762"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=2762"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=2762"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} - query<\/strong><\/span> - is the text using which the QUERY formula churns out the information we are looking for from the data set. Since it is expected to be a string, it has to be enclosed within a set of quotes. Or, it can also be a reference to a cell, where the query text is stored.<\/li>\n \t
- query<\/strong> \u2013 is the text using which the QUERY function churns out the information we are looking for from the data set. Since it is expected to be a string, it has to be enclosed within a set of quotes. Or, it can also be a reference to a cell, where the query text is stored.<\/li>\n