Google Spreadsheets has a powerful function -among many- which you will not find in any other spreadsheet offering. It’s the QUERY() function.
If you’re a Master of VLOOKUP() and other advanced functions that pull data from other sources, you’re going to love what QUERY() can do.
In this video, I explain how it works and introduce a few other functions that may interest you too.
Resources:
NEW! UPDATED AD-HOCK QUERY available.
- Uses the new Google Spreadsheets
- Start and end date range picker.
- Improved (shorter) query code!
After opening each sheet, choose File>Make a Copy… from the menu to make your own editable copy of each sheet. I demonstrate doing this in the beginning of the video.
In Google Spreadsheets, how can I select 2 or more rows/columns that are not adjacent to each other, at the same time?…
As of this date, command clicking is not possible in a Google Spreadsheet. This comes in handy when copying data and then pasting it together in another location. Though it isn’t possible at this time, you can select a column and drag it to another po…
So I’ve put my data into the ad hoc query report but when I reorder the query formula to take different cells, it doesn’t work. Any suggestions?
Could be a range issue. Make sure you are referencing the correct range of data in the import range function. The error message always helps me to pinpoint the issue.
Hi,
I have a table A and a Table B. In the Table A I like to run a query within in the Table_A and I like to mix some data from the Table_B with query.
e. G.
( The importrange name is not real )
=query(importrange(“18aH456hgd6767hggY”;”Table_B!A2:L73″);”Select Col4 where Col2 = ‘GT 11.01.2016’ “; 0)
The string ‘GT 11.01.2016’ should be not like that, it should a variable. Is t
Is this possible? How I will write in the query string, as a variable from the table_A (not as a string) ? Any hints?,
Thanks in advance.
My first guess is to do something like Col2=”””&H2&””” to allow you to grab a value from a cell on the sheet somewhere.
Thanks so much for the tutorial. As a long time Excel user and not a programmer, it was hard to get my head around the Google syntax, but your Query overview has helped me jump lightyears ahead. May the force be with you…
Glad to help. 🙂
Hi Cameron,
Like the previous comment, you really helped to propel my google docs usage to a new level. As the head of fundraising for my charity (with little budget for databases) the task of managing our data is in my lap.
I have limited expertise in database management, design, etc and I do not have time to invest in a university CSC program. Question is: can you guide me towards some ‘Coles Notes’ type learning materials that focuses on this subject matter as it relates to GoogleDocs?
Thanks again for your excellent training video and for your response.
Hello Tom, I have a background in business intelligence and I understand where you’re coming from. Wish there was more of a budget for the field because its important that you can get the data you need out of your information for quick decision making. The query function however isn’t easy to master without having had some training with SQL. I think you will be very happy to learn about pivot tables as an easier alternative to mining your data for information. THe built in pivot tables in google sheets can do some amazing stuff and you don’t need to have any SQL experience to understand how they work ore even write any formulas. They are controlled by a report editor. I’ll be releasing a video on how to create and use a pivot table soon. But here is the google video and reference on them: https://www.youtube.com/watch?v=giuD7KSmock https://support.google.com/drive/answer/1272900?hl=en
This is pretty neat. I really like the Daily Summary Query Pivot. I am trying to create A referral Database for my employer that would work on a monthly basis.
I’m finding the tutorials for the Query function jump from “this is how you cut and paste ” to what you showed.
You mentioned some documentation on this could you like that?
Hello,
Really great work, its helping me a lot.
Do you know how to convert this:
EX: 1min30s to 00:01:30 with query function?
Ty
I really appreciate your help!
your’re welcome.
Hey, just want to say thank you so much. My wife will really love this reporting w/o leaving her spreadsheet knowledge behind. She was hesitant when I told her to learn database instead
Hey, just want to say thank you so much. My wife will really love this reporting w/o leaving her spreadsheet knowledge behind. She was hesitant when I told her to learn database instead
Hey, just want to say thank you so much. My wife will really love this reporting w/o leaving her spreadsheet knowledge behind. She was hesitant when I told her to learn database instead
Great video! I knew there was so much more to Google Spreadsheets and your video clearly and concisely pointed me in the right direction. Thank you!
Thank you for this complete, easy to understand tutorial.
I was trying to come up with a simple way for instructors to obtain class lists from a dynamic spreadsheet database I created (dynamic = spreadsheet updated as student enroll). I’d created a Class List template, but I had been hiding columns, filtering (for each class), copying and pasting into the template.
I am now motivated to create the report you demonstrated, and have the instructor select the class and have the Class List template created. Or, since the database has the identical structure year to year, I can duplicate the query sheet, specifying the unique column for each class and have all of the Class Lists dynamically populated.
The only feature I’ll miss by working in Google, is the ability control the print area via a formula I found that dynamically sets the lower limit of the print.
Thank you for opening up this capability for me.
Great tutorial, thank you.
Do you know whether it is possible to use named ranges as part of the Select clause instead of using the “Col#” structure? I tried using Indirect() but does not seem to work.
I am trying to “move” the column that I pull data from using a validation cell as a reference.
Thanks!
Jesus Patino.
The Query Function will not take named ranges in a select statement. If you are querying in the same spreadsheet, you can use A, B, C etc instead of Col1, Col2, Col3 in your select statements.
You CAN reference a cell in your spreadsheet that contains the entire select statement.
So, in E1 you could have the text: select * <---grabs everything. Then your query function could look like QUERY(A2:C1000,E1,1) which will pull in the select statement you have in E1 This may allow you to do what you are trying to do if you experiment with it in the cell you are referencing. Give it a try. Also, not sure what you mean by "move". you can re-arrange your data by re-arranging your columns in your select statement. For instance: Select C, B, A will display the resulting data columns in the reverse order.
Thank you putting together is amazing tutorial!
I am using the filtered query option to filter the responses I receive via a form, however it is only populating the values in the various columns and not the text. Below is the query I am using; please let me know if I am doing something wrong.
=Query(importRange(“0Ao5W2dqH8MUddHhucU5vT0N6WlV3SE9OTHcxUm5iSVE”,”Sheet1!A1:k100000″), “select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10 where Col10 contains ‘Tuesday'”,1)
Thanks!
Thank you for putting together this amazing tutorial!
I am using the filtered query option to filter the responses I receive via a form, however it is only populating the values but not the text. It is also not populating if the number of values are more. Below is the query I am using; please let me know if I am doing something wrong.
=Query(importRange(“0Ao5W2dqH8MUddHhucU5vT0N6WlV3SE9OTHcxUm5iSVE”,”Sheet1!A1:k100000″), “select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10 where Col10 contains ‘Tuesday’”,1)
Thanks!
Can you share your spreadsheet with me so I can take a look? cameron@crestwebservices.com
do you know if it is possible to combine data from 2 spreadsheets into 1 for the following case. sheet 1 has columns containing ID, Name, age, address. sheet 2 has columns containing ID, height, weight, comments. i would like to know if there is a way to combine a row from 1 sheet with the a corresponding row from a 2nd sheet that have the same ID.
so, in 1 row i would get: ID, name, age, address, height, weight, comments.
i hope my example is clear enough and i hope there is a way to do that.
I would really appreciate the help.
thank you,
What’s the difference between these?
“””&D5&”””
“&D5&”
I’ve been using them inside strings, and sometimes “&D5&” works and sometimes it doesn’t. I’m confused.
I stick to 3 sets of quotes and I’ve never had an issue with the formula not working.
Thank you for showing this tutorial. I really like this video.
But I’m not as smart as you, so I have some problem.
Can you help me ?
I’m using this formula in cell B1 of my Sheet A :
=Query(importRange(“0AiNHChaxeoAVdGVnOF9HYlpmdHd5b2N1R2hrTDFuZnc”,”WAL-BT!A1:R500″), “select Col9 where Col3 contains “””&A1&””” “,1)
but the result only show me the header of column 9 of the sheet WAL-BT and the row B2 of my sheet A only show me “–“.
Can you help me fix this problem ?
thank you very much Sir.
Please share your spreadsheet with cameron@crestwebservices and contact me over email.
I have a field that can have the following values: Prototype 1, Prototype 2, or Prototype 3. I want to write a query to find all 3 of these values. Your demonstration only shows one specific value per query.
Eric
Can you elaborate on your situation a bit? Perhaps share a worksheet with your data and an example of what result you want?
Thanks for the great tutorial!
I used the query function to import data from the form responses to various tabs in the same spreadsheet but for some reason I keep getting an error and need to hit Ctrl + shft + E to populate the responses. Could you tell me why I get the error? and what to I need to do so that it auto populates?
Do you know of any way to do a
select a, countif(b = 1) group by a… or something to that effect?
Essentially I want to count all particular occurences within a group by.
In SQL … you can use select a, sum(case when b = 1 then 1 else 0 end) group by a
Appreciate any feedback!
Thanks for asking,
You could try =QUERY(A:B,”SELECT A, COUNT(A), WHERE B=1 GROUP BY A”,) That should give you a unique list of the names in A and a count of the number of times they had “1” in column B.
More examples here: http://productforums.google.com/forum/#!topic/docs/yjBTPR1uyIo
Hi Cameron
Can you please assist, my query does not want to continue into below rows and some of my data is imported incorrectly
Thanks! Great job! Learnt a lot!
Hi Sir Cameron Crest!
Good day!
Your Query video tutorial was the best among the rest!!!
Thanked GOD I found your video tutorial!
And now, I terribly needed your help regarding Ad Hoc Query Report.
I really, really hope you can help me Sir.
I did my best to follow the formulas but I got really lost in Ad Hoc Query Report.. HUHUHUHUUUU!!!
Please, I’m begging you, HELP me with the correct formula I needed.
I will send you email Sir, I really pray that you will reply to my demanding request. I’m so sorry.
Thank you so much in advance Sir!
~Nancy
Hi Sir Cameron Crest!
You’re such a great blessing to me!!!
Thank you very, very much from the bottom of my heart!
Thank you sooooo much for your precious time!!!
I can’t thank you enough.
You did a wonderful job!!!
It’s a tough one, though~
I cannot succeed in this project without your help.
Thank you very much.
May the LORD GOD continue to bless you more and more!~
Nancy~
Glad I could help. Thanks for the kind words Nancy.
Hey Cameron,
You are a legend! Awesome tutorial. We can now do A LOT of things for our clients.
Great work.
Cheers
Tony
Hello, I have created a class form that allows me to choose to give points or to take points away. I have create a QR Code that will allow any staff member to go to that form and give or take away points. With the form, I have to choose student from a list and then choose debit or credit. This places the responses in a spreadsheet. This will be given over a period of six weeks. Students can earn several points on a daily basis and also lose several points. The students will be all mixed up. I need a way to add and subtract the debits and credits from just that student and then do the same for the rest of my students. I hope this is making sense. Please help!
Hello Teressa, I’m emailing you. 🙂
Thank you so much for your help! Using a pivot table works perfectly!
Thank you so very much for this good straight forward explanation – it’s a great way to kick off working with Google Sheets.
Not much else that’s a real help so this is a Godsend.
All the Best
Mike
Can you help me out one more time?
[…] CameronCrest says: June 21, 2012 at 5:19 pm […]
Cameron,
I have been trying to get this query to work, but am more confused than anything thanks to some jugheads on the Google Contributers user forum….
I have several sheets that I need to get working, but once I have a better understanding I should be able to get them all to work in similar ways…
The biggest sheet has Columns A thru AU
A. Timestamp from form response
B. Name of the person
C thru AU being data that they entered
Need the following to happen
Sum for C, D, F, G, I, J, L, M, O, P, R, S, U, V, X, Y, AA, AB, AD, AE, AG, AH, AJ, AK, AM, AN, AP, AQ, AR, AS, AT, AU
Max for E, H, K, N, Q, T, W, Z, AC, AF, AI, AL, AO,
Grouping by B
Every time I try the query I get an error, either the Column(D) does not exist in the table or some other error
a couple of the contributers from qoogle forum gave me this
=query( arrayformula(‘Cleaned’!A2:AU); “select Col2” & arrayformula( concatenate(“, sum(Col” & column(C:BV) & “)” ) ) & “where Col2 ” group by Col2″; 1 )
the problem is that it puts the word sum in everything, and it sums everything which is something else that I do not want or need,; but all they keep saying is goto their webpages for query and figure it out…..i’m lost confused and really ticked of out those arrogant pukes up there….I am hardware engineer not a space astronaut or a freaking robot. I learn by example and explanations that make sense.
Can you please assist and explain some things so that I can understand them better….
Thanks
Rich
p.s. I
[…] video on query function in google spreadsheets is a missing manual for on query functions. His blog lists the spreadsheet employed in the […]
Great video. Very helpfull.
I have a need to combine query from multiple sheets to one report. You touched on this briefly in the beginning saying that was the method for populating your source spreadsheet. Can this be accomplished with stock functions? Or, do I need to get into scripting?
You can accomplish this with installing the custom vmerge function script created by Ahab. Google the VMERGE().
Hi Cameron i was wondering if u could help me with a project.
i have 9 charities the kids volunteer for. i have 9 sheets in 1 spreadsheets. columns are all the same
Firstname Lastname school date hours
i was wondering how i could make a query that i can type in a kids lastname and i pull “Firstname Lastname school date hours” from all my sheets
any help or recommendation would be really help full
Hi Cameron,
I came across a google forum that showed me how to make ImportRange work in the New Google Sheets! https://productforums.google.com/forum/#!starred/docs/wyxF6WvxoUo
______
Btw i just made it work!!!
First, try use normal import range from same file in a cell
=IMPORTRANGE(“0ApKcXS2222222dG9iNEtxY1B2S2U2Tk1UUzdIaTAzdVE”;”Sheet1!C2:C18″)
After the system will ask you if you want to access that file… you accept.
Try again with Query (importRange (….. it work here 😀
Thanks Allison!
I have a trouble to copy and paste the spreadsheet key
https://docs.google.com/a/ginosi.com/spreadsheets/d/1B9li1rl822Su6fdEMvZgAE-TtFzKfkJ8wjr_0PxdkpY/edit#gid=0
there is no key here, which part I should copy in Query formula?
Please help
Copy the part in bold.
https://docs.google.com/a/ginosi.com/spreadsheets/d/1B9li1rl822Su6fdEMvZgAE-TtFzKfkJ8wjr_0PxdkpY/edit#gid=0
Hi Cameron, this is really the best Video Tutorial about Google Spreadsheet query and other advanced functions. I really appreciate you taking the time creating and sharing it with us.
Thanks again,
Lewis@affordablewebdesign.com
Certified SEO Consultant
Thanks! Glad you like it. 🙂
Nice video… somehow the links for sheets are buffering for me!
I’m trying to do an ad hoc query with three drop-down options much like the last tab in your sample sheet. Only, I want to have an ALL option for all three lists. Is there any way to do that without having 8 different if statements?
Hello Cameron –
Thanks for publishing your spreadsheet. It helps a lot. I have 2 questions,
01. If i’ve to run a query for a date range, something like 11-01-2014 to 11-10-2014.
02. How can I incorporate this date range to your “Ad Hoc Query Report”.
Thanks, VJ
Hello VJ,
I have a new and improved Ad-hoc query report that has exactly what you need. You can maye your own copy by choosing File > Make A Copy.. at:
https://docs.google.com/spreadsheets/d/1b7sPFxuhx-a3pw54mLOK-3kcjKT8OENDSC7_YcFBsh4/edit?usp=sharing
Hi Cameron,
I am using this Function to query from a monthly payroll report.
=QUERY(HVST!A3:W835, “select P,D,F,Q,U,Sum(J), Sum(V)where B contains”””&D1&”””and C >= date “””&text(B1,”yyyy-MM-dd”)&””” and C <= date """&text(C1,"yyyy-MM-dd")&"""GROUP BY P,D,Q,F,U",1)
And this is the result.
Full Name Client Project Code Department Cost Rate sum Hours Rounded sum Cost Amount.
How do I get the rest of the report to populate?
Thank You for posting this video, it is one of the best for Google Sheets.
Pat
Hey Pat, It’s possible that you are getting the correct result. Make sure your choices in B1 (the date) match exactly what you have in your source data. If you are just typing in a date it may not work. I always make my dates a choice from a drop-down for this reason. The drop down list is generated elsewhere using the unique() function.
Can I make a select by row in the query? For example: get the cells in column A that correspond to a TRUE in another column that is selected according to a comparative parameter, which in this case would be selected by the row?
The simple answer is yes. The formula for doing this is hard to list here without some source data to work with.
Gracias por toda su ayuda!
De nada mi amigo.
Thank you so very much
You’re welcome!
Very good tutorial and examples! Thank you! Made my life a lot easier. Regards from Switzerland.
Glad to help!
Hi Cameron,
My knowledge in excel in not very advanced. I have spent a lot of time looking over your spreadsheets, and I understand what you have done; It was really helpful. However, I am having trouble understanding all the syntax; Is there a website you know of that explains this in detail? I have searched but am having trouble finding an explanation. For example when you are converting to year the formula is =Arrayformula(if(A5:A=””,””,YEAR(A5:A))) . Why do you need the =””,””, ? Is this because you are using an if function and saying that if A is blank then leave blank otherwise put the year? If that is the case why use an if function at all? Why not just use =Arrayformula(Year(A5:A)? or in the original query why do you need “””&B2&”””. It is my understanding that you use quotes when text is required by why do you use triple quotes and why do you use two &? If there is a website or even a book you can recommend that would help explain the proper use of syntax? Thank you
Sorry for the late reply Shandon.
if(A5:A=””,””, is there to display nothing if there is nothing in A1. In english: if A5 is nothing then show nothing.
Hj Cameron!
thanks for your share,
Could you help me,
How to use continue function is added automatically to download speard sheets to excel.
You may need to copy your sheet, copy all the data and paste it over itself as values. That will most likely handle the issue of continue() showing up in all the cells when you download it as an Excel Spreadsheet.
I am absolutely grateful for the info. I just noticed I spent the past 3 hours playing around with this query function thanks to your AdHoc sheet. Lots of fun. I really enjoyed the video.
Glad you enjoyed it. Sorry for the late reply. I have a new ad-hock query sheet with date ranges if you are interested:
https://docs.google.com/spreadsheets/d/1b7sPFxuhx-a3pw54mLOK-3kcjKT8OENDSC7_YcFBsh4/edit?usp=sharing
Thanks for your great video – very very helpful!! You’re very knowledgeable!!
Not sure if you are still taking questions, but here is mine…
During the “Daily Summary Query Pivot” demo you mentioned that you can “wrap the Col1 in a Month function”. I would love your help on how to do that. I am new to sheets, and what you set up in your Pivot demo is exactly what I need, plus, I need the Month function that you mention, added so that it totals by month instead of day (as you currently have the query). Can you help?
Here is your current query:
=Query(importRange(“0Am5Xsf1r3lGjdEdORUItWGo5MHZOcElGYnN1RThUaEE”,”Sheet1!A3:F100000″), “select Col6, Sum(Col2) GROUP BY Col6 pivot Col1 ORDER BY Col6 desc “,1)
An example of wrapping the col1 in a month function can be seen on my sheet: “Query Examples 3: Advanced Reporting” https://docs.google.com/spreadsheets/d/1b7sPFxuhx-a3pw54mLOK-3kcjKT8OENDSC7_YcFBsh4/edit?usp=sharing
Check out the supplemental date columns on the first sheet. They allow you to report on month week -or whatever time dimension you want to define.
Hey Cameron.
Your tutorial has helpted me with more than one project. Thanks fot that.
But now I’m looking for the correct query.
=QUERY(IMPORTRANGE(“spreadsheet”;”A:H”); “select Col1, Col2, Col3, Col4, Col5, Col6, Col8 where Col3 starts with ‘MORINEN’ or Col4 starts with ‘MORINEN'”;1)
How can I ad the condition that teh date in Col1, must me equal or greater than today ?
where col1 >= date “””&text(F2,”yyyy-MM-dd”)&””” then place this function in cell F2: =today()
Hi.
Thank you very much for the tutorial. It helped a lot.
You’re welcome. Glad to help.