QUERY Complete guide: Google Sheets' most complex function

QUERY takes an input range of data, and can return an output in a table based on criteria specified using SQL-like code. Use cases are huge, the most common is the ability to return a filtered dataset e.g. columns D and B where E = London. The FILTER function can do similar things but you cannot specify which columns you want and the headers don't come across.

If you are already using FILTER/SORT and other dynamic arrays in Google Sheets, you may find the built in options too limiting and want to stretch it further and that is where QUERY's flexibility comes in. If you aren't a coder don't worry it’s not too hard to pick up, the instructions here should enable you to do enough without taking a long time to practice.

We will cover all nine of QUERY's clauses, which must be written in this order when you use a combination: 1. SELECT, 2. WHERE, 3. GROUP BY, 4. PIVOT, 5. ORDER BY, 6. LIMIT, 7. OFFSET, 8. LABEL, 9. FORMAT

Group by /Pivot works with SUM, COUNT, AVG, MIN, MAX and filtering is explored with advanced operators such as contains, dates, AND/OR/NOT (SQL's like is also possible for fuzzy matching but contains is easier to use for many).

Table of Contents:

00:00 - Introduction
00:40 - Intro/Select
02:21 - Filter/Where clause
03:12 - Filter CONTAINS
03:49 - Using dates
04:37 - AND/OR
05:48 - Refer to cell/dropdown
09:01 - Rename columns with label
09:39 - LIMIT
10:02 - OFFSET
10:45 - Combine sheets dynamically QUERY
13:01 - Headers
13:27 - Order by
14:03 - Group by
15:25 - Pivot
16:18 - FORMAT number style
17:19 - CLAUSE/function order

[victor]

this video certainly not for beginner query user. i have learn it for some times. gladdly i found this swift and brief tutorial. to extend my knowlegde . tq bro🎉

[duncantalbott]

Probably the most useful guide for Google Sheets Queries I have come across! Great job! Definitely going to save this to reference later on 😁

[jonalynricafranca]

I've watched tons of tutorials about Query Function, but your tutorial is the simplest way to follow. Thank you. I'm a new subscriber now. More to come!

[RickettsClown]

This is exactly the overview I have been looking for. Thank you for explaining the starter basics so clearly!

[frankvanderlinden]

Your tutorial was really educational. Thnx for all the effort you put in this lesson.

[tranclife]

fantastic video, content was clearly explained in a swift manner, not wasting a single second of viewers time.
really informative and helpful, thank you and may god bless you with happiness and joy.

[masaudamin]

I love "Query" in Google Sheets and you explained it briliantly!! Actually I have a question: I want to return the label "week number" in my query but it returns double (in two columns) can you please help me what can be the reason?
My query: =query(data, "select A, B, C, D, E, F, G, H, I, avg (I) where B is not null group by A, B, C, D, E, F, G, H, I order by B ASC label avg(I) 'week number'")

[coldavenue]

Amazing David, amazing. You codensed so much stuff in wachtable 18 minutes. Bravo!

[ttone]

Thank you... It was long but very useful.

[anthonymaina]

Great tutorial learnt so much, you should have shared a google sheet with all various clauses and finally on the same workbook the dashboard you showed with query referring to data from cells.

[balkrishnarana]

Thank you for this fascinating demonstration.

[lafamillecarrington]

Loads of helpful information, clearly presented. Your edits at the end of sections are a bit (no, a LOT) sudden.

[COD_Danyel]

Great job. Thank for sharing. I suscribe

[StephenPiela]

At 8:02 you are explaining how to add the filters to your query. How did you set the string output in cell G10 as your variable in the query? You cut JUST before clicking on that cell

[Elisa_B]

Bonsoir; MERCI pour ce tuto vidéo avec les bases de QUERY vraiment bien expliquées en si peu de temps.
Du coup, je me suis abonnée à votre chaîne.
Google Translat fr > UK
Good evening; THANK YOU for this video tutorial with the basics of QUERY really well explained in such a short time.
So I subscribed to your channel.

[ginnetaabaidoo]

3:38 How could the command that you used to include "Londo" also include the ones that had "London" they weren't in the command with it at all

[bhfkvqi]

Thanks Mr. David for a complete package in one go. please guide if we can format cell borders through query function.

[nyit_noreen]

Great video. I'd love to see more on how you set up the sheet in Refer to cell/dropdown section. I'm attempting to build something similar but with an IF statement for All items in the dropdown. I can't get an Order by item to work with the IF statement.

[Miss-Kitty-Cat]

Super helpful video, thanks so much for making it!

[paalhn]

You say that with the FILTER function you're limited to the order of the columns in the tablw, but that is wrong. Other than that, good video