Google Sheets QUERY Function Tutorial - SELECT, WHERE, LIKE, AND, OR, LIMIT statements - Part 1
HTML-код
- Опубликовано: 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!!!
I've watched hours and hours of your videos. Thanks for sharing!
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 great! I'm learning so much. Thanks a lot.
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.
Awesome.... very clearly presented.... had no idea this functionality was even available in Google Sheets.
Many Thanks
Wow, this is game changing for me. Love the video. Thank you.
thanks, best video series on this topic so far, very helpful
Agreed !
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.
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.
+- 13:00 limit results to... Very helpful!!! Thanks
Awesome tutorial. Helped me understand Queries, thank you!
Love your Content; You're just amazing
Keep up the good work
GREAT TUTORIAL! Super helpful! Easy to follow and is exactly what I was looking for.
Great to hear!
Thank you so much for detailed tutorial video. You saved my time. I will tweet about it
Looking forward to part 2. Thank you.
Great tutorial. Going help me a lot making good use of google sheets in my work flow. Thanks.
Thanks a lot, very very helpful and comprehensive video for relevant topic
Thank you, these videos are so useful! ❤️💖
Excellent Work. You made my system way better with this Tutorial. Best of Luck
I want you to know that you are the best at what you do!
Just a suggestion. Maybe share your data sheet in the about section so that people can follow along using the queries.
Great video!
Powerful tools! Thank you so much, bro.
Excellent way to explain things. You made my day..Wish you the best.
One of the best channel for learning google sheet.
THANK YOUUUUUUUUUUUU THIS IS EXACTLY WHAT I WAS LOOKING FOR
Always a great teacher!!
Another great video! Thank you
these explanations are very clear, thank you very much
Muito bom! Parabens pelo conteudo.
Very good video! Very helpful. Congrats for share this content.
Thanks for this video... VERY VERY VERY HELPFUL !!!
Thank you, it make my work a lot easier
very clear and concise, thankyou
Your videos are amazing and they helped me a lot. Thank you so much!!!
Glad you like them!
Thanks for the video. You saved me lots of trouble.
I'm binging your vidoes right now. Very helpful and easy to understand!
:)
Extremely Excellent !
YOU SIR, Are a Genius!!!!!
Thank You!
This is the best video for Query Function. But it would be great if u provide the google sheet the same for practice.
Great video, thank you!
Thank You +Learn Google Spreadsheets for this series!
Thank you so much for the video. Its so useful to us.
so clear so net . thank you for this post
Thank you so much... your videos are very helpful to me.. I learnt alot..
Thank you so much for the lesson
GREAT DESCRIPTIONS!
Thank you so much great video!
Thank you.
19:29 I relly need this
Thanks. Learn a lot.
Is it possible to create hyperlinks to the query results that takes me back to the original location of certain data.
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?
Thank you soooooo much, life saver :)
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🤗
You are the best!!!
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 :)
This is a very helpful video indeed. Is there a limit with how many times I can use the command "AND"?
finally gets started one minute into video
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
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.
Really good video
very helpful thanks for the tuts
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)
Thank you so much!
Helps alot, thanx
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?
Good query explanation!
very helpful tutorials
Is there a way to label columns that are aggregated using "Label" in the query statement?
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!
Крутое видео! Cool tutorial
Excellente! Tks
Thanks man!
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.
Excellent!
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...
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.
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 could you use the QUERY function to return on this data only the top sales reps for each state?
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
Wow thank you very much
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.
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..
If i make a color to particular cell, i need that color has to change/update on compiled sheet. Please guide
How to use query when the data is alphanumeric. I tried using query and importrange but would not recognize alphanumeric character
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.
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.
Hi thanks for your video.
How i can use WHERE function to select multiple criteria to display the whole row data. Eg/ T32 or M33 is my primary key.
Please assist and thanks
How do you LIMIT according to date If I want to return only those that happened after such date and year?
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.
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.
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
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.
ONE QUESTION
Along with =, >, LIKE, can 'not equal to' also be used?
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.
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
Cool video
Excellent Thanks a lot
👍
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.