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!
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🎉
Thanks for the comment! Yes I posted it as a “complete guide” pushing experienced users to it, but beginners can maybe just watch the first couple of minutes for it. Maybe I’ll make a beginners video 🤔
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.
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'")
@@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.
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
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 [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!
Nah it doesn’t allow for it unfortunately. Here are some ideas though… stackoverflow.com/questions/14796620/google-spreadsheet-query-join-equivalent-function
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,
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.
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.
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 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/
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?
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
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
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
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.)
@@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?
(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!!
@@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!!
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
direct explanation, no wasting time, what i am looking for
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!
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🎉
Thanks for the comment! Yes I posted it as a “complete guide” pushing experienced users to it, but beginners can maybe just watch the first couple of minutes for it. Maybe I’ll make a beginners video 🤔
@@learnspreadsheets sure bro.
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!
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
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
Thank you for this fascinating demonstration.
No worries! Glad you like it 😃
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!
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
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 full video my study &
thanks
Glad you like it!
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
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
This is good video about query fu, 👍
Glad you like it! Thanks!
GREAT video! Thank you.
Glad you like it!
Thanks so much for the tutorial!!!
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!
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 😃
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
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 job. Thank for sharing. I suscribe
No problem! Glad you found it useful!
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.
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 😃
Really very helpful video & nice explanation sir🇮🇳
Love from india 🇮🇳
Thanks for the feedback! I worked hard on that one 😃
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
é 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!!
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
very great tutorial, thx
Thanks! Glad you one it!
Thank you, it was interesting
No problem, glad it’s useful!
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/
great stuff man
Yup! Love this function 😃
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
Wonderful❤
Glad you like it!
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
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
Fantastic 😊
Glad you like it!
very solid vid ty!
Sir I need your help regarding Google Sheet
Hi ok I can offer some paid consulting if that’s what you need for sure!
Great ! Thank u so much
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?
(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 😃
I love learning excel!
Yay! Who doesn’t 😃
I want to learn google sheet
Great idea! I love google sheets 😃, these dynamic arrays are brilliant
Thanks!!!!
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
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
I am from India
Great! Glad you’re watching!
great stuff man