I have recently used the awesome Query feature to auto populate information based on a drop down selection. If you check that you can see how simply we can extract necessary information from a lengthy database using QUERY. It’s pretty easy to use Query function as an alternative to Filter function in Google Sheets.
You can use Google Sheets Query function to filter data in seconds! I definitely see two advantages when I use Query function over Filter Function.
Advantages of Using Query Function as an Alternative to Filter Function
When you use Query function to filter, you can omit the columns that you don’t want to appear in your result.
You can perform calculations with the filtered value using functions like SUM, AVG, MIN, MAX, COUNT etc. So you can summarise data. Yep! You can summarise data using Query function!
Steps to Use Query Function as an Alternative to Filter Function in Google Sheets
First of all let me clear one thing. It’s not required to learn the Google Sheet FILTER function first, to follow the below G-Query tutorial. If you learn the FILTER function, obviously you can understand the difference.
I will give you the link to learn the Google Sheet FILTER function at the end of this post, as it’s not relevant at least for now.
Steps to Use Query Function Filter Feature:
First and foremost thing is a sample data. Just type the below table in a new Google sheet and name the tab as “MasterFile“.
You should start typing from the very first cell of your spreadsheet that means cell A1 should contain the filed name “name” and must complete in cell F16. So the data range will be A1:F16.
You should name this range in order to make the formula simple. Naming ranges is the best way to make a formula smarter. To do that go to Data menu Named Ranges. Give the range name “sourcemaster” and add the range as below.
You may also like: How to view, modify, delete Named Ranges
Text Field as Criteria in Query Function
You can see there are different age groups under the filed age_group. Using the query function I’m filtering the field “age_group” 11-17 in a new sheet.
In a new sheet apply the formula as below.
=query(sourcemaster,“select A,B,C,D,E,F where B=’11-17′”)
In the above query formula “sourcemaster” is the named range which you’ve just created in the sheet “MasterFile” and A, B, C, D, E, F are the column headings in the data.
Below is the Query function syntax we used. There are different syntaxes available in Query.
=query(dataList,”select A,B where F=‘Sold’”)
I already told you that you can use Query function as an alternative to filter function in Google Sheets. You can get the above query result by applying the below Filter function.
=filter(‘Master Folder’!A1:D19,‘Master Folder’!B1:B19=“5-10”)
If you want to learn the Filter function switch to our below tutorial.
Must read: Filter data to separate sheets in Google Sheets.
Date Field as Criteria in Query Function
In the above Google Sheet QUERY formula, we used text field as criteria, i.e. “5-10”. When you want to use date as criteria in QUERY, do as below. The date should be in “yyyy-mm-dd” format.
=query(sourcemaster,“select A,B,C,D,E,F where F = date ‘2010-08-22′”)
I used the QUERY function to filter the “dob” field in the data on sheet “MasterFile“.
Numeric Field as Criteria in Query Function
Finally if you want to use number as criteria in QUERY follow the below example. In the same above data we can apply the function. Let’s filter the data based on the “age” field.
=query(sourcemaster,”select A,B,C,D,E,F where E = 7″)
This filter the “age” field where the age of members are 7.
All the above QUERY function can be replaced by equivalent FILTER function. I’ve already given one example above.
Hope you understand how to use Query function as an alternative to filter function in Google Sheets. The above is so simple, if you create the data base and follow the tips.