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 :) Show
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 functionAt first glance, Google Sheets QUERY is just another function with 1 optional and 2 required arguments: =QUERY(data, query, [headers])
Now let's dig deeper into the clauses and whatever they do. Clauses used in Google Sheets QUERY formulasQuery 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 columnsTo fetch each and every column, use select with an asterisk – select *
Tip. If you omit the select parameter, Google Sheets QUERY will return all columns by default:
Example 2. Select specific columnsTo pull only certain columns, list them after the select clause:
Tip. The columns of interest will be copied in the same order you mention them in the formula:
Google Sheets QUERY – Where clauseGoogle 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:
Tip. If you're upset or worried about having to deal with such a huge number of operators again, we feel you. Our Multiple Vlookup Matches 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 numbersI will add where to my Google Sheets QUERY from above to get the info on those planets that have more than 10 moons:
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:
Example 2. Where with text strings
Example 3. Where with datesGuess 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 '2020-01-01' Here's my formula to get all rows with a Speech date before 1 Jan 2020:
Example 4. Combine several conditionsTo 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 2019, the second — on or before 30 November 2019:
Or, I can select papers based on these parameters:
Tip. If your head is about to explode already, don't give up just yet. There's a tool that is perfectly capable to build 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 ByGoogle 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:
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 – PivotGoogle 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:
Google Sheets QUERY – Order ByThis 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:
LimitWhat 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):
OffsetThis 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.
If you try and use both limit and offset, the following will happen:
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 – LabelGoogle 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:
FormatThe 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.
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. OptionsThis 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 – Multiple Vlookup MatchesHowever 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. Why Multiple VLOOKUP Matches is better than formulas? Well, with the add-on there's absolutely no need to:
As a bonus, you'll be able to:
I'm not kidding, see for yourself. Though this GIF was sped up, it took me less than a minute to fine-tune all criteria and get the result: If you're curious enough, here's a detailed video showing how the add-on works:
Also, feel free to check out its tutorial page or home page. You may also be interested inWhich of the following operators can be used to retrieve rows containing null values in a specific column select one?When two conditions are joined by the AND logical operator, only one of the conditions must be TRUE to be included in the query results. Logical operators are evaluated in the order of NOT, AND, and OR. To find rows containing a NULL value in a specified column, you must use the search condition of = NULL.
Which of the following clauses is used to indicate a particular sort sequence?The ORDER BY clause is used to get the sorted records on one or more columns in ascending or descending order.
Which of the following operators can be used to combine search conditions?You can use the AND and OR operators to combine two or more conditions into a compound condition. AND, OR, and a third operator, NOT, are logical operators. Logical operators, or Boolean operators, are operators designed to work with truth values: true, false, and unknown.
Which of the following symbols is a wildcard character that can represent any number of characters?Examples of wildcard characters. |