QUERY Complete guide: Google Sheets' most complex function

Поділитися
Вставка
  • Опубліковано 5 жов 2024

КОМЕНТАРІ • 111

  • @victor25021983
    @victor25021983 22 дні тому +1

    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🎉

    • @learnspreadsheets
      @learnspreadsheets  17 днів тому +1

      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 🤔

    • @victor25021983
      @victor25021983 15 днів тому

      @@learnspreadsheets sure bro.

  • @duncantalbott9463
    @duncantalbott9463 2 роки тому +10

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

    • @learnspreadsheets
      @learnspreadsheets  2 роки тому

      This is so lovely to read! Thanks so much for this note

  • @jonalynricafranca6625
    @jonalynricafranca6625 3 роки тому +4

    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!

    • @learnspreadsheets
      @learnspreadsheets  3 роки тому +1

      Yay! Glad you found it useful, it’s a super complex tool so this video took me a while to pull together!

  • @RickettsClown
    @RickettsClown Рік тому +2

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

  • @frankvanderlinden7322
    @frankvanderlinden7322 3 роки тому +3

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

    • @learnspreadsheets
      @learnspreadsheets  3 роки тому

      No worries! Glad you like it. It’s a lot! As you say

  • @tranc3life
    @tranc3life 2 роки тому

    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.

    • @learnspreadsheets
      @learnspreadsheets  2 роки тому

      That’s so kind! Thanks so much, I do what I can so glad you appreciate it

  • @masaudamin6699
    @masaudamin6699 2 роки тому +2

    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
      @learnspreadsheets  2 роки тому

      Thanks! I’m glad you like it, sorry but I’m not sure unfortunately, week numbers are hard to deal with in general

  • @coldavenue2325
    @coldavenue2325 3 роки тому +1

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

    • @learnspreadsheets
      @learnspreadsheets  3 роки тому

      Thanks! Glad you like it

    • @learnspreadsheets
      @learnspreadsheets  3 роки тому

      Check out my g sheets dynamic array vid, I condensed even more stuff in that one!

    • @coldavenue2325
      @coldavenue2325 3 роки тому

      @@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.

  • @balkrishnarana5278
    @balkrishnarana5278 6 місяців тому

    Thank you for this fascinating demonstration.

  • @ttone2330
    @ttone2330 Рік тому +1

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

    • @learnspreadsheets
      @learnspreadsheets  Рік тому

      Thanks, I’m glad you enjoyed it despite the length. There’s a lot to get through!

  • @lafamillecarrington
    @lafamillecarrington Рік тому +1

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

    • @learnspreadsheets
      @learnspreadsheets  Рік тому +1

      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

  • @awarnessofenvironment61
    @awarnessofenvironment61 10 місяців тому

    This is good video about query fu, 👍

  • @paalhn
    @paalhn 4 місяці тому +1

    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

    • @learnspreadsheets
      @learnspreadsheets  4 місяці тому

      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

  • @GenNextAnalyst
    @GenNextAnalyst 2 роки тому +1

    GREAT video! Thank you.

  • @anthonymaina882
    @anthonymaina882 3 роки тому +1

    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.

    • @learnspreadsheets
      @learnspreadsheets  3 роки тому +1

      Thanks for the feedback and the recommendation, I made this tutorial a while ago but recently I have started sharing workbooks for them

    • @greggswaim1088
      @greggswaim1088 2 роки тому

      @@learnspreadsheets Just happened across this video; so impressive. How can one access the workbooks? Thanks for the lessons so far!

    • @learnspreadsheets
      @learnspreadsheets  2 роки тому

      Thanks Gregg, if you subscribe & email me to david@xlconsulting-asia.com I can share it

  • @akshaymishra633
    @akshaymishra633 6 місяців тому +1

    Wonderful❤

  • @kajasutha
    @kajasutha 2 роки тому

    Great full video my study &
    thanks

  • @pepsantiago
    @pepsantiago 3 роки тому

    Thanks so much for the tutorial!!!

  • @Miss-Kitty-Cat
    @Miss-Kitty-Cat 2 роки тому

    Super helpful video, thanks so much for making it!

    • @learnspreadsheets
      @learnspreadsheets  2 роки тому

      No worries! Glad you find it useful, thanks for saying it. Took me a while to pull together!

  • @Elisa_B
    @Elisa_B 2 роки тому

    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.

    • @learnspreadsheets
      @learnspreadsheets  2 роки тому

      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 😃

  • @ginnetaabaidoo4669
    @ginnetaabaidoo4669 Рік тому +1

    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

    • @learnspreadsheets
      @learnspreadsheets  Рік тому

      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

  • @zainierizal3807
    @zainierizal3807 2 роки тому +1

    very great tutorial, thx

  • @user-bh3fk3vq6i
    @user-bh3fk3vq6i 2 роки тому

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

    • @learnspreadsheets
      @learnspreadsheets  2 роки тому

      Hi sadly you cannot apply formats as they don’t pass through a function

  • @StephenPiela
    @StephenPiela 2 роки тому +1

    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

    • @learnspreadsheets
      @learnspreadsheets  2 роки тому

      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

    • @StephenPiela
      @StephenPiela 2 роки тому

      @@learnspreadsheets so it would essentially be
      =QUERY(range, &CHAR(42)&G10&CHAR (42))

    • @netschaap
      @netschaap 2 роки тому

      @@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!

    • @learnspreadsheets
      @learnspreadsheets  2 роки тому

      Nice one! Glad you could make it work 😃

  • @COD_Danyel
    @COD_Danyel Рік тому

    Great job. Thank for sharing. I suscribe

  • @malikastar9265
    @malikastar9265 Рік тому

    Thank you, it was interesting

  • @hsksearch3
    @hsksearch3 3 роки тому +1

    Great ! Thank u so much

  • @nyit_noreen
    @nyit_noreen 3 роки тому +1

    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.

    • @learnspreadsheets
      @learnspreadsheets  3 роки тому

      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,

    • @stephenmarkagripo4288
      @stephenmarkagripo4288 2 роки тому

      pls make the video about it. i want that topic the timesheet.

  • @masthanaso
    @masthanaso Рік тому

    Fantastic 😊

  • @mehboobmaniyar1890
    @mehboobmaniyar1890 3 роки тому +1

    Really very helpful video & nice explanation sir🇮🇳
    Love from india 🇮🇳

    • @learnspreadsheets
      @learnspreadsheets  3 роки тому

      Thanks for the feedback! I worked hard on that one 😃

  • @GV-gn3mj
    @GV-gn3mj Рік тому

    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?

    • @learnspreadsheets
      @learnspreadsheets  Рік тому +1

      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

  • @romarnicholasmandap3253
    @romarnicholasmandap3253 3 роки тому +1

    great stuff man

  • @mourin1982
    @mourin1982 3 роки тому +1

    Hi just wondering if there is a way to join data sets by a certain column? Many thanks

    • @learnspreadsheets
      @learnspreadsheets  3 роки тому

      Nah it doesn’t allow for it unfortunately. Here are some ideas though… stackoverflow.com/questions/14796620/google-spreadsheet-query-join-equivalent-function

  • @arpitchokhani2549
    @arpitchokhani2549 Рік тому +1

    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.

    • @learnspreadsheets
      @learnspreadsheets  Рік тому +1

      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/

  • @willperito
    @willperito Рік тому +1

    é trazer junto o formato da célula possível? (por exemplo a cor de fundo da célula pai)

    • @learnspreadsheets
      @learnspreadsheets  Рік тому

      Formatting cannot come with the cells via the Formula unfortunately

    • @willperito
      @willperito Рік тому

      @@learnspreadsheets thanks!!

  • @saplingqwason
    @saplingqwason 3 роки тому

    very solid vid ty!

  • @vivekmusafir4492
    @vivekmusafir4492 Рік тому

    Thnx. A ton 👍

    • @learnspreadsheets
      @learnspreadsheets  Рік тому

      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

  • @alidzoutillage
    @alidzoutillage Рік тому

    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?

    • @learnspreadsheets
      @learnspreadsheets  Рік тому

      Hello! Thanks for the kind words! You can rearrange the columns in QUERY using Select & then listing out the columns in order

    • @learnspreadsheets
      @learnspreadsheets  Рік тому

      Check out my other video on making a query output which will give more insights into the process I use! ua-cam.com/video/FTKJZIrHfzQ/v-deo.htmlsi=XLaAsBHosCtgkpoj

  • @vishaltanwar8318
    @vishaltanwar8318 2 роки тому +1

    Sir I need your help regarding Google Sheet

    • @learnspreadsheets
      @learnspreadsheets  2 роки тому

      Hi ok I can offer some paid consulting if that’s what you need for sure!

  • @reaseynhim1413
    @reaseynhim1413 3 роки тому

    I love learning excel!

  • @hopefullyw
    @hopefullyw 2 роки тому

    Thanks!!!!

  • @frederickbanda4696
    @frederickbanda4696 2 роки тому

    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.

    • @learnspreadsheets
      @learnspreadsheets  2 роки тому

      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

  • @regiskoepsell7794
    @regiskoepsell7794 2 роки тому +1

    (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
      @learnspreadsheets  2 роки тому +1

      Thanks for the comments but I don’t speak Portuguese sorry

    • @regiskoepsell7794
      @regiskoepsell7794 2 роки тому +1

      @@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!!

    • @learnspreadsheets
      @learnspreadsheets  2 роки тому +2

      I would maybe wrap it inside an iferror to replace an error with 0 or a “” blank

    • @regiskoepsell7794
      @regiskoepsell7794 2 роки тому +1

      @@learnspreadsheets Good idea! Thanks for the feedback and the suggestion.

    • @learnspreadsheets
      @learnspreadsheets  2 роки тому +1

      Yay no problem! Glad it helped 😃

  • @bonbonpony
    @bonbonpony 3 роки тому

    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
      @learnspreadsheets  3 роки тому +1

      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

    • @erikscs3924
      @erikscs3924 Рік тому

      ​@@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?

  • @seewali1
    @seewali1 3 роки тому +1

    I want to learn google sheet

    • @learnspreadsheets
      @learnspreadsheets  3 роки тому +1

      Great idea! I love google sheets 😃, these dynamic arrays are brilliant

  • @awarnessofenvironment61
    @awarnessofenvironment61 10 місяців тому

    I am from India

  • @BlueSuedeHughes
    @BlueSuedeHughes Рік тому +1

    Doesn't work

    • @learnspreadsheets
      @learnspreadsheets  Рік тому

      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

  • @romarnicholasmandap3253
    @romarnicholasmandap3253 3 роки тому

    great stuff man