Google Sheets | FILTER | QUERY | Function | Differences | Example | Spreadsheet | Tutorial

Google Sheets FILTER and QUERY functions are powerful tools to extract data. Of these two functions, the FILTER function is not case-sensitive. QUERY assists to extract all or specific columns of data easily. With FILTER, only contiguous columns can be extracted.
Use a function like CHOOSECOL with FILTER to get only specific columns.
Both the FILTER and QUERY functions allow to specify multiple conditions in the formula.
QUERY function is based on the Google Visualization API Query Language.
For more info on FILTER and QUERY, please review their exclusive tutorials whose links are given below.
*************************************
How to Use FILTER in Google Sheets?
FILTER is a powerful function to extract data based on or more conditions:
• Google Sheets Filter F...
*************************************
How to Use QUERY in Google Sheets?
QUERY can extract data from specific or all columns:
• Google Sheets | QUERY ...
*************************************
-------------------------------------
How to Use VLOOKUP in Google Sheets?
Use VLOOKUP to get a single value:
• VLOOKUP Google Sheets ...
-------------------------------------
How to Create a Pivot Table in Google Sheets?
Create a pivot table for calculation and in-depth data analysis:
• Google Sheets | Pivot ...
-------------------------------------
How to Use Convert to Table in Google Sheets?
Convert a data range into a table easily:
• Google Sheets | Conver...
-------------------------------------
How to Use REDUCE in Google Sheets?
Use REDUCE to get the final value of intermediate calculations:
• Google Sheets | REDUCE...
-------------------------------------
How to Use SCAN in Google Sheets?
It's easy to calculate running total, running count and the like with SCAN:
• Google Sheets | SCAN |...
-------------------------------------
How to Use FREQUENCY in Google Sheets?
FREQUENCY returns the frequency distribution of data:
• Google Sheets | FREQUE...
-------------------------------------
How to Use COUNTIFS in Google Sheets?
Count values that fulfill one or more criteria:
• How to use COUNTIFS in...
-------------------------------------
Syntax of FILTER Function Formula:
=FILTER(range, condition1, [condition2, …])
Example of FILTER Function Formula:
Assume that each cell in the range A2 to A25 has vendor codes, B2 to B25 has names of computer accessories like Portable hard disk, Keyboard, Mouse, Speakers, and RAM. C2 to C25 has inventory of each item, D2 to D25 has the unit price of each item, and E2 to E25 has the total price of each item.
To extract data in the range A2 to E25, pertaining to a vendor code CA304 and item Keyboard, the formula is:
=FILTER(A2:E25, A2:A25="ca304", B2:B25="keyboard")
The formula will extract all the data that fulfill the two conditions.
Syntax of QUERY Function Formula:
=QUERY(data, query, [headers])
Example of QUERY Function Formula:
Using the same description of the example above, the formula is:
=QUERY(A2:E25, "SELECT * WHERE A='R303' and B='Keyboard'", 1)
To extract data only in columns A and B, the formula is:
=QUERY(A2:E25, "SELECT A, B WHERE A='R303' and B='Keyboard'", 1)
To get more info on FILTER and QUERY, please review their exclusive tutorials whose links are given above.
Please review this video tutorial, which highlights the key differences between the Google Sheets FILTER and QUERY functions, and how to extract data using these functions, with examples.