In today’s technology-driven society, if you’re older than 7 years of age and you aren’t intimately familiar with basic spreadsheet functions like SUM(), AVERAGE(), MAX(), MIN(), and maybe even the occasional COUNTIF(), you were probably born into an Amish community and won’t be reading this anyway. If you consider yourself an expert of the VLOOKUP() with nested INDEX MATCH functions, prepare to have your mind blown.
Like the title says, this a function specific to Google Sheets. Sorry, Excel-lovers…you’re out of luck.
The function we’re talking about today is QUERY(). This function allows the user to create pseudo-natural language look-ups within the spreadsheet. The inputs given within the brackets when using the QUERY() function are similar in structure to the database language called SQL.
If you want to learn by doing, I’ve created a Google Sheet that you can use to practice and follow along. Jerod’s Awesome Query Google Sheet (Full Disclosure: I copied the sample data from this page and modified it very slightly just to give us something to work with.)
As you can see, we have a quick & easy sales table with all the pertinent details. We’ll be performing our magic on this data. Let’s dive in with a relatively basic query. In cell J3 enter the following:
=query(A3:G46,"select * where D='Pencil'", 1)
This can be dissected as:
- A3:G46 — our data table including headers
- “select * where D=’Pencil'” — We must include the quotes (“) here to delineate this section of the command. We’re telling the query, “We want to see everything (that’s the select * part) as long as it says “Pencil” in column D (the where D=’Pencil’ part).”
- 1 — The “1” at the end simply tells the query command that we included a header row in our table. If you had two header rows this would be a “2,” three header rows would be a “3,” etc. If you do not include header rows you can set this to zero, “-1,” or “FALSE.”
The output should look like this (in the area J3:P16):
Well now, that’s pretty damn useful…but as they say in infomercials…WAIT! THERE’S MORE!
Let’s say that we want to know the total sales of pencils during this entire time period. We simply modify our function to read:
=query(A3:G46,"select sum(G) where D='Pencil'", 1)
When we execute this query, the spreadsheet returns sum Total 2135.14. All we did was replace our asterisk (the “everything” selector) with the normal spreadsheet function we wanted to execute on any line that had to do with pencils. At this point, if you’re not seeing the immense power and utility of the QUERY() function then you need to dream a little bigger. Here we go with bigger…
=query(A3:G46,"select C where (B<>'Central' and G>1000) or (E<10)", 1)
This query should give us the names of any sales reps who do not work in the central region (B<>’Central’) and whose line item sales are greater than $1,000 (G>1000) or any sales rep who sold less than 10 units of any particular item (E<10). I have no idea why you would want to get this particular information…but you can…and it works!
If you want to get the average units sold per salesperson, simply create a pivot:
=query(A3:G46,"select AVG(E) pivot C", 1)
…and you get these results…
Other options available include group by and order by for various kinds of grouping and sorting. In fact, with just a very few number of commands, the QUERY() function can provide astounding results. Here’s a link to the simple Google Docs Support Page and a link to the Google Developers Page–both for your light reading and query-function-learning pleasure.
The QUERY() function can even be nested within itself to run exceedingly complex lookups. Spoiler alert! You have to use ARRAYFORMULA() and some other fanciness, but it can be done. I won’t pretend I’m good enough to walk you through one of those examples but there are plenty of them online.
This is a quite simple function to deploy at its most basic level but it retains the ability to provide immensely powerful results when used in a more advanced manner.
If you make something awesome with the QUERY() function, let me know about it. I’d love to see what others are doing with this powerful tool!
Leave a Reply