Google Sheets QUERY Function Tutorial - SELECT, WHERE, LIKE, AND, OR, LIMIT statements - Part 1
Вставка
- Опубліковано 30 чер 2017
- Video tutorial series about QUERY function in Google Sheets.
In this tutorial we'll cover how to use QUERY function in Google Sheets to pull filtered results out of your main dataset. We'll use SELECT, WHERE, LIKE, AND, OR and LIMIT statements to filter data by number value, text & text that contains a certain string in it.
Google Sheets
www.google.com/sheets/about/
Website:
www.chicagocomputerclasses.com/
EXCELLENT VIDEO THIS IS EXACTLY WHAT I NEED!!!
Now I understand more the power of GSheets,
I enjoy 19:29 minutes, super simple and straight forward. Super valuable, I love it!
This is one of this video that I saved in my own private playlist.
Query functions are amazing!
Thanks!
Amazing! I just built a query from client data to show the client, their preferences, and plan type for clients who are either recent starts or restarts so my team can give extra attention to our newbies. Thank you!
After 2 weeks of searching, this is exactly what I wanted! Thanks so much!!!
Looking forward to part 2. Thank you.
thanks, best video series on this topic so far, very helpful
Agreed !
Good video! I usually open the same spreadsheet in two browser tabs as easier to switch between (shift-tab), rather than using Google sheets tabs.
Just a suggestion. Maybe share your data sheet in the about section so that people can follow along using the queries.
Great video!
Wow, this is game changing for me. Love the video. Thank you.
I've watched hours and hours of your videos. Thanks for sharing!
I really appreciate all of your videos. I learn lot many things from it. Wherever I stuck, I refer your videos. Please keep posting this kind of knowledge.
Thanks
If you want to watch more video about Google sheets, look on my channel STM Solution, i have many videos what you will like.
Your videos are great! I'm learning so much. Thanks a lot.
Awesome.... very clearly presented.... had no idea this functionality was even available in Google Sheets.
Many Thanks
THANK YOUUUUUUUUUUUU THIS IS EXACTLY WHAT I WAS LOOKING FOR
+- 13:00 limit results to... Very helpful!!! Thanks
I love your tutorial very much, you are so good at it, and always trying something no one else ever thought of. Very stimulating experience. Thank you
me too. Thanks U so much for sharing
Your videos are absolutely amazing. They have saved me so much time and aggravation because you explain it so clearly!
I completely agree. I'm so grateful for that guy. He made everything I was looking for easy to understand. Also, super creative in combining functions, scripts, & the likes.
Awesome tutorial. Helped me understand Queries, thank you!
Thanks a lot, very very helpful and comprehensive video for relevant topic
Another great video! Thank you
Thank you, these videos are so useful! ❤️💖
Thank you so much for detailed tutorial video. You saved my time. I will tweet about it
Always a great teacher!!
Love your Content; You're just amazing
Keep up the good work
Powerful tools! Thank you so much, bro.
Крутое видео! Cool tutorial
Great tutorial. Going help me a lot making good use of google sheets in my work flow. Thanks.
I want you to know that you are the best at what you do!
GREAT TUTORIAL! Super helpful! Easy to follow and is exactly what I was looking for.
Great to hear!
Thank you, it make my work a lot easier
YOU SIR, Are a Genius!!!!!
Thank You!
One of the best channel for learning google sheet.
Great video, thank you!
Extremely Excellent !
these explanations are very clear, thank you very much
This is the best video for Query Function. But it would be great if u provide the google sheet the same for practice.
Thanks for the video. You saved me lots of trouble.
Thanks for this video... VERY VERY VERY HELPFUL !!!
Really good video
very clear and concise, thankyou
GREAT DESCRIPTIONS!
Excellent!
Muito bom! Parabens pelo conteudo.
Very good video! Very helpful. Congrats for share this content.
I couldn't figure out why my reference dates were not pulling through correctly and I just forgot to add the parentheses 🤦🤦. You sir are a life saver🤗
Thank you so much for the lesson
so clear so net . thank you for this post
You are the best!!!
Excellent Work. You made my system way better with this Tutorial. Best of Luck
Thanks man!
Thanks. Learn a lot.
Excellente! Tks
Excellent way to explain things. You made my day..Wish you the best.
Thank you so much great video!
Thank you soooooo much, life saver :)
Your videos are amazing and they helped me a lot. Thank you so much!!!
Glad you like them!
very helpful tutorials
Thank you.
19:29 I relly need this
Good query explanation!
Thank you so much for the video. Its so useful to us.
very helpful thanks for the tuts
finally gets started one minute into video
Thank You +Learn Google Spreadsheets for this series!
Thank you so much... your videos are very helpful to me.. I learnt alot..
CTRL SHIFT PgUp/PgDown to switch sheet tabs btw. Great video!
My keyboard doesn't have PgUp/PgDn :) Thanks!
@@ExcelGoogleSheets I use ALT up arrow to move back tap...
Thank you so much!
Helps alot, thanx
This is a very helpful video indeed. Is there a limit with how many times I can use the command "AND"?
Is it possible to create hyperlinks to the query results that takes me back to the original location of certain data.
I'm binging your vidoes right now. Very helpful and easy to understand!
:)
Very good your explanation. Thanks.
But, and if I need include one calculated field like after select two fields (string, number) then I need calculate the % of each numbered register by sum of results? (select A,B where "one condition" and include field (as '%') using B/sum(B))
How can I do it?
hey man, great stuff I'm trying to find an answer for and issue: how can you make updates on the referenced table (table with the query) without breaking it?
or different approach to make a separate column outside of the query, but to keep inline with the other rows. because currently when the query is updated the rows don't match on the new column that been made hope I'm clear cheers :)
Wow thank you very much
All your videos are incredibly helpful, so thanks for all the work you put into it. However, regarding your examples of utilizing the QUERY function, I'm not sure how this approach is any different than using a Pivot Table. Or, perhaps that's not the point of this video, and you're simply showing people a different approach to filtering data. Either way, I found it insightful, empowering, and quite practical. Just wondering, though: Would there be limitations to accomplishing the same type/degree of filtering by using a Pivot Table instead? Keep up the outstanding work, and thank again!
I don't think it's the same. You can do Pivot Tables using QUERY function, but you can't do QUERY function with Pivot Table. That being said, if you were able to solve your problem with Pivot Table and it works, then you should keep using it.
You used YEAR function to extract corresponding year from Column A. If we don't do that and manually type year as text, the query function recognizes the year data as text and we can use single ' ' to define the text after where statement? (5:40)
Great tutorial. In this video, you have a 2nd spreadsheet called "Query Language Reference". How do you generate that file. It would be very handy to have that as a quick reference as you have done. Thanks.
No doubt, the best videos on Google Spreadsheets!!!
Thanks for the material!
Is there any difference instead of using ORDER BY in the query function, use the SORT( query(...) ,1,true ) ?
Thanks!
Not Really, just an extra unnecessary step.
Hello there
I have formula in on column. and want to aplyy it to all other neighborhood columns. how i can do it?? by clicking and moving it to all othersdoesnt work because it changes cells in formula in wrong way
Cool video
how can i select mismatch between two columns? for example how do i get all data in column A that is not found in column B?
thank you very much, I only have one inquiry can the query function be use when the data is continuously updating, I mean i have a data entry and it keeps populating everytime. thanks again
Yes you can Noli, but just keep the range open like if you are querying some range , for example instead of =QUERY(sheet1!A1:G40, use =QUERY(sheet1!A1:G So don't mention the row number and just keep the last column name. This will make your query function dynamic.
Great video, as always! I have a problem, I don't understand the difference between query and array, to me looks like you can achieve the same results using one or another. I'm sure I'm missing something though...😅 Thanks for the great resources!
Is there a way to label columns that are aggregated using "Label" in the query statement?
Your videos are amazing and they helped me a lot. please can we get a copy of the excel sheet for practice? Thank you so much!!
How do you do a query like you have shown here that puts data in all columns based on one column criteria (e.g. name)? For example the several people entered the data and you just want to pull all of their data into a new tab.
amazing
Excellent Thanks a lot
👍
Sir i do have a question if i choose headers as 1 or -1 i get the same answer so what’s difference between that
Hello
I have google sheet with some data in multiple column in my data there are some blank cell. Now I want to use query function for combine all column . When I am trying to use this function this also add blank cells. Please tell me how i can use this for filter blank..
Thank You for Sharing...had quick question in my data one column has number format as "R1890" when I use query function it returns output as " " ,can you please help me how to resolve this issue.
how could you use the QUERY function to return on this data only the top sales reps for each state?
Hi! thanks for this tutorial!. Inside the function, you're writing the statement in another cell. Is it possible to write the data source in another cell? (In this other cell I'm writing the tab where the query supose to search for data)
Yes. You will need to use INDIRECT function and CONCATENATE.
First of all Happy new 2018 and congrats for your amazing job . Contents you are sharing are amazing. I have one question. Is it possible to merge two different google sheets according one specific criteria? For example I have one module collecting name of project and another module collecting other information with the same name of project. I would like to create with query function a new sheets with using Name of Project as criteria of sorting. Thanks
Hi, how are you, greetings from Panama?
I was looking for the second part of your tutorial, since I see that you have other tabs with the names of summary and subtotal. I
am carrying a database of participants in seminars and I need to do a
query where they can add columns of seminar hours for a same personal
identity number.
I notice that you make
several querys but repeat the marks several times, I would like to know
if what I am trying to explain in the paragraph above is possible or if
there is a second part of your tutorial.
Thank you.
Hi sir can we put formula in sheet title? From cell to sheet title in order to edit easily the names of a plenty sheet at a time
If i make a color to particular cell, i need that color has to change/update on compiled sheet. Please guide
Hi, could you help me with this,
I have 4 columns with checkboxes "true or false values", and a columns with text,
I want to use Query to count true values on all 4 columns and group them in text column.
Thank you so much for this! I got the QUERY to work but I would like to know how to remove the data from the original sheet once it's moved to the next sheet.
Copy and paste values only to remove the formula and keep the results, and then go and delete the old data.
If you don't like doing this then you'll have to create Apps Script.
How to use query when the data is alphanumeric. I tried using query and importrange but would not recognize alphanumeric character
I'm looking for guidance on how to pull out the following
I want to "select A,H,I,J,K,L,M,N,O where G contains 'HDW'" but I also want to SUM(M) and GROUP by L
If I write as follows
Select H,I,J,K,L, SUM(M) where G contains 'HDW' group by L,K,J,I,H
It doesn't do the group by correctly. I end up with duplicates of some of the items still. Any help would be great.
Thanks! Can you let me know if it's possible to write the query text that INCLUDES additional external cell references in the 'text' cell as you've demonstrated? I can't seem to get it to work - for example the follow query string returns an error, but works perfectly when pasted directly into the formula. Select * Where Col1=' "&C4&" '. Thanks! Very happy to pay for your advice as I can not find the answer to this ANYWHERE!
OF COURSE! Having just asked the question I've worked it out. ="Select * Where Col1='"&C4&"'" Thanks again FANTASTIC content.
Can you create an example in-which the where clause is using a text value to search for. If I do this, I only get 1 match returned. But if I hard code the where clause i.e. where C = 'Test1' I get all the rows where C = Test1. If i use something like this where C = '&B3&' where B3 has the value Test1 I only get one row of data returned.