Using the Query function in Google Sheets
Reporting & Analysis
Query() is a powerful Google Sheet tool. But what if you don't want to learn coding?
If you have ever wanted to pull data from multiple spreadsheets, you would have come across the query() function. The formula looks something like this:
QUERY(data, query, [headers])
If you know your query languages, you would write something like this:
QUERY(A2:E6,"select avg(A) pivot B")
But if you're like me, you have to do a Google search every time you're creating a complex query to make sure the syntax is legit. Here are a few common ones I use often:
Import selected columns from another Google Sheet
I often have to import a few columns from large datasheets that have 10s of columns. In those cases, I find the query() function faster than the traditional indexmatch. Usually, they look something like this:
QUERY(MASTER!A:X,"select A, B, C, Q, R")
Import selected columns from another Google Sheet, based on criteria
Once in a while, I import data from another sheet which meet a certain criteria. Again, far easier than complex indexmatch's:
QUERY(MASTER!A:X,"select A, B WHERE F='FALSE'")
This is all good and fine if you are familiar with SQL querying. But if you are a business user, you likely don't know querying languages. Or, at least, don't have them at the tip of your fingers.