QUERY Complete guide: Google Sheets' most complex function
ฝัง
- เผยแพร่เมื่อ 8 มิ.ย. 2024
- 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).
If you prefer article form, I go through these features on this article I wrote: beebole.com/blog/google-sheet...
Another video on several of Sheets' Dynamic array functions is also useful here: • Google Sheets: Dynamic...
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 - แนวปฏิบัติและการใช้ชีวิต
This is exactly the overview I have been looking for. Thank you for explaining the starter basics so clearly!
That’s so great you like it! Thanks!
Probably the most useful guide for Google Sheets Queries I have come across! Great job! Definitely going to save this to reference later on 😁
This is so lovely to read! Thanks so much for this note
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!
Yay! Glad you found it useful, it’s a super complex tool so this video took me a while to pull together!
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.
That’s so kind! Thanks so much, I do what I can so glad you appreciate it
Your tutorial was really educational. Thnx for all the effort you put in this lesson.
No worries! Glad you like it. It’s a lot! As you say
Thanks so much for the tutorial!!!
Thank you for this fascinating demonstration.
No worries! Glad you like it 😃
Super helpful video, thanks so much for making it!
No worries! Glad you find it useful, thanks for saying it. Took me a while to pull together!
Amazing David, amazing. You codensed so much stuff in wachtable 18 minutes. Bravo!
Thanks! Glad you like it
Check out my g sheets dynamic array vid, I condensed even more stuff in that one!
@@learnspreadsheets Hey David, could you make a video about how to make the =googlefinance function dynamic. In column C is my ticker symbol, in column D my price of the stock. I want the Col D to pull down dynamically whenever new tickers are added to col C.
I found some solution with the help of a script. community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727
Secondly I am searching for a solution for importing .xls files from web directly to google sheets.
GREAT video! Thank you.
Glad you like it!
very solid vid ty!
great stuff man
Thank you... It was long but very useful.
Thanks, I’m glad you enjoyed it despite the length. There’s a lot to get through!
Great job. Thank for sharing. I suscribe
No problem! Glad you found it useful!
Great ! Thank u so much
Great full video my study &
thanks
Glad you like it!
Thank you, it was interesting
No problem, glad it’s useful!
very great tutorial, thx
Thanks! Glad you one it!
Thnx. A ton 👍
Glad you like this one! I have another one on dynamic arrays in sheets which is also handy & one on how to make a query builder in sheets
This is good video about query fu, 👍
Glad you like it! Thanks!
Fantastic 😊
Glad you like it!
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.
Très comptent que tu apprécie ça, ça m’a pris de temps pour faire le vidéo! Je parle français alors ça va 😃
Thanks!!!!
Really very helpful video & nice explanation sir🇮🇳
Love from india 🇮🇳
Thanks for the feedback! I worked hard on that one 😃
Wonderful❤
Glad you like it!
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.
Hey! Thanks for the feedback, I have a couple of other. Videos on drop down lists in g sheets. Google sheets rises above excel in a few areas, notably data validation and the QUERY function,
pls make the video about it. i want that topic the timesheet.
Thanks Mr. David for a complete package in one go. please guide if we can format cell borders through query function.
Hi sadly you cannot apply formats as they don’t pass through a function
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.
Thanks for the feedback and the recommendation, I made this tutorial a while ago but recently I have started sharing workbooks for them
@@learnspreadsheets Just happened across this video; so impressive. How can one access the workbooks? Thanks for the lessons so far!
Thanks Gregg, if you subscribe & email me to david@xlconsulting-asia.com I can share it
I love learning excel!
Yay! Who doesn’t 😃
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'")
Thanks! I’m glad you like it, sorry but I’m not sure unfortunately, week numbers are hard to deal with in general
Hi, thank you for posting. Could you clarify something please. I have watched this for cell reference text in query """&&""" but I see you have something different, both are valid?
Hi I’m glad you liked it, what I use in my video works I can guarantee, other syntax is also valid for some instances
Hi just wondering if there is a way to join data sets by a certain column? Many thanks
Nah it doesn’t allow for it unfortunately. Here are some ideas though… stackoverflow.com/questions/14796620/google-spreadsheet-query-join-equivalent-function
06:14 Yeah, but how can I sort them in that dropdown? (e.g. alphabetically, or numerically for numbers)
08:19 Is there any way to specify a default value shown on that dropdown instead of just a blank cell with a little arrow?
12:22 What if some of the columns returned from the query are empty? Is there any way to omit such empty columns from being returned by the query? (But only if they are actually empty in the RETURNED list of data; in the original data set, these columns might have contained something in some rows, but after filtering with "where", they got empty.)
Sorry but I would need to get more info to understand, there are many more detailed guides to QUERY that may be able to help
@@learnspreadsheets Hey man, how are u doing? Ik I'm 1 year too late, but in case you see this reply, could you plz help me out with this formula?? I had success on elaborating a formula that could import selected columns from another spreadsheet using IMPORTRANGE and QUERY. This is the formula: - =SORT(QUERY(IMPORTRANGE("type_url";"'Tab1'!A2:K200");"select Col3, Col7, Col2, Col10, Col8 where Col1 is not null order by Col3 asc"); 1; VERDADEIRO) - . But now I need to capitalize the first letter of each word from the col3, only the col3. I've tried to user PROPER function in various ways, and still nothing... Do you know if there's some way to do it keeping this formula?
the best summary video about Query Function
I have one issue with this function wich is in "Combine sheets dynamically QUERY" at 12:20 when you added "Add" in the first data it showes up in the middel of combined data.
Is there a way where it shows up at the end?
Hello! Thanks for the kind words! You can rearrange the columns in QUERY using Select & then listing out the columns in order
Check out my other video on making a query output which will give more insights into the process I use! th-cam.com/video/FTKJZIrHfzQ/w-d-xo.htmlsi=XLaAsBHosCtgkpoj
é trazer junto o formato da célula possível? (por exemplo a cor de fundo da célula pai)
Formatting cannot come with the cells via the Formula unfortunately
@@learnspreadsheets thanks!!
Hello David, Outstanding tutorial. thank you very much. I am looking for some help trying to run a power query web connection in google sheet with IMPORTHTML but I am failing and need help. Can you assist? If you could drop me a note and let me know how I can get in touch.
Great to read this comment Frederick! Im glad you found it useful. Sorry but my experience with IMPORTHTML is quite limited, you can email me on david@xlconsulting-asia.com to see but I don't have too much experience as mentioned
Loads of helpful information, clearly presented. Your edits at the end of sections are a bit (no, a LOT) sudden.
Glad you like the video, & thanks for feedback on the edits, there was a lot to get through on this video so I opted to cut time at the end of sections but I’ll take feedback on board for future videos
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
I set the criteria in a formula that is then linked to the cell. So one formula uses & etc to return the criteria I want to make work in query and then another formula is taking the query function with the full criteria
@@learnspreadsheets so it would essentially be
=QUERY(range, &CHAR(42)&G10&CHAR (42))
@@learnspreadsheets [edited] I originally requested the formula in cell G10 since I couldn't manage it to work. However, this was due to another reason (namely: string search parameters must be put between single quotes). It works perfectly with a formula such as: =query(range;G10). Thanks for this very instructive video, I've been looking a long time for this functionality!
Nice one! Glad you could make it work 😃
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
Hi good question. If you use CONTAINS or LIKE it will include anything which contains those characters regardless of whether there are other characters in the cell or not
Hi David. Thank you for the tutorial. It was very useful.
Can you please help me with this.
=QUERY(C:R,"select * where C = '"&A3&"' AND I = '"&A4&"' ")
The above formula is working fine, but my actual requirement is I sometimes want to return data were data is filtered only for I and not for C. I don't want to use the IF function as there would be more than one AND operator in my final QUERY function.
PS: I did read the comments trail to see if something similar has been asked before, but it returned blank. Looking forward to your reply.
I'm glad you find it useful, this should help with what you are looking for: infoinspired.com/google-docs/spreadsheet/and-or-and-not-in-google-sheets-query/
(PT-BR)
Olá professor! Excelente conteúdo! Me ajudou muito!
Consegui aplicar numa planilha, no entanto, surgiu uma dúvida.
Como corrigir o "erro" #N/D, quando um dos critérios não for atendido? Estou utilizando esta fórmula, onde B1 refere-se à turma (por ex.: 6º ANO) e E1 refere-se à disciplina (por ex.: Matemática):
=QUERY(CONSULTA_DB!B4:L;"select * where J Contains '"&B1&"' and F Contains '"&E1&"' ")
No entanto, quando seleciono outra disciplina, que não possui registro nessa turma (por ex.: Artes), a célula onde está a função Query retorna a mensagem acima (#N/D)
Muito obrigado!!
Thanks for the comments but I don’t speak Portuguese sorry
@@learnspreadsheets Hello how are you? I used Google translator to re-report my question.
-------
Hello teacher! Excellent content! Helped me a lot!
I managed to apply it in a spreadsheet, however, a question arose.
How to fix #N/A "error" when one of the criteria is not met? I'm using this formula, where B1 refers to the class (eg 6th grade) and E1 refers to the subject (eg Mathematics):
=QUERY(QUERY_DB!B4:L;"select * where J Contains '"&B1&"' and F Contains '"&E1&"' ")
However, when I select another subject, which has no record in that class (eg Arts), the cell where the Query function is located returns the message above (#N/A)
Thank you very much!!
I would maybe wrap it inside an iferror to replace an error with 0 or a “” blank
@@learnspreadsheets Good idea! Thanks for the feedback and the suggestion.
Yay no problem! Glad it helped 😃
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
Thanks for the feedback, I probably didn’t explain it well - sorry, the FILTER function returns the same columns you selected originally. If you want certain columns to be hidden or reordered you need to add CHOOSECOLS or INDEX or another function with it
Sir I need your help regarding Google Sheet
Hi ok I can offer some paid consulting if that’s what you need for sure!
I am from India
Great! Glad you’re watching!
I want to learn google sheet
Great idea! I love google sheets 😃, these dynamic arrays are brilliant
Doesn't work
Sorry but my methods do work, it’s easy to make mistakes when writing the code & getting single & double quotations mixed up, I hope you can follow the tutorial & get it working
great stuff man
Yup! Love this function 😃