Google Sheets FILTER와 QUERY: 무엇을 사용할까요?

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

• Google Sheets (web, Android, iOS): Both functions are available.
• Excel 365, 2021, 2024: FILTER exists but has a different syntax (see the Excel section). Excel has no QUERY function.
• LibreOffice Calc: Not covered. QUERY is a Google Sheets function.

Example data

All formulas use this table in A1:E9 on a sheet named Orders:

Decision table

9 tasks side by side

1. Rows for one region

=FILTER(A2:E9, B2:B9="East")

=QUERY(A1:E9, "select * where B = 'East'", 1)

Both return four rows. QUERY also returns the header because the third argument says the data has one header row.

2. Criterion from a cell

With the region in G1:

=FILTER(A2:E9, B2:B9=G1)

=QUERY(A1:E9, "select * where B = '"&G1&"'", 1)

This is where QUERY gets fiddly. Text needs single quotes inside the double-quoted string, numbers must not have them, and dates need a special format (task 8). If G1 contains an apostrophe, such as O’Brien, the single-quoted version breaks. The query language accepts string literals in single or double quotes, so wrap the value in doubled double quotes instead:

=QUERY(A1:E9, "select * where C = """&G1&"""", 1)

Most “Unable to parse query string” errors come from this step. See the QUERY parse error guide.

3. Several conditions (AND)

=FILTER(A2:E9, B2:B9="East", E2:E9>500)

=QUERY(A1:E9, "select * where B = 'East' and E > 500", 1)

FILTER treats each extra condition argument as AND. Result: three rows (600, 900, 1200).

4. Either condition (OR)

=FILTER(A2:E9, (B2:B9="East")+(B2:B9="West"))

=QUERY(A1:E9, "select * where B = 'East' or B = 'West'", 1)

In FILTER, adding two TRUE/FALSE arrays gives 1 or 2 where either is true and 0 where neither is, and FILTER keeps any non-zero row.

5. Text contains a word (case-insensitive)

=FILTER(A2:E9, ISNUMBER(SEARCH("widget", D2:D9)))

=QUERY(A1:E9, "select * where lower(D) contains 'widget'", 1)

Google’s query language reference states that contains, starts with, ends with, matches and like are case-sensitive, and recommends upper() or lower() to work around it. Without lower(D), “widget” wouldn’t match “Widget”. SEARCH ignores case. FIND is its case-sensitive twin.

6. Two columns, sorted, top 3

=ARRAY_CONSTRAIN(SORT(CHOOSECOLS(FILTER(A2:E9, B2:B9="East"), 3, 5), 2, FALSE), 3, 2)

=QUERY(A1:E9, "select C, E where B = 'East' order by E desc limit 3", 1)

Here QUERY is clearly more readable: one clause per step, in the order the query language requires (select, where, group by, pivot, order by, limit, offset, label, format).

7. Total per region (group by)

=QUERY(A1:E9, "select B, sum(E) group by B order by sum(E) desc label sum(E) 'Total'", 1)

FILTER can’t aggregate. The non-QUERY version takes two formulas: =UNIQUE(B2:B9) in G2, and next to it:

=ARRAYFORMULA(IF(G2:G="",,SUMIF(B2:B9, G2:G, E2:E9)))

If you need many aggregates (sum, count, average by region and month), QUERY wins easily.

8. Dates

=FILTER(A2:E9, A2:A9>=DATE(2026,4,1))

=QUERY(A1:E9, "select * where A >= date '2026-04-01'", 1)

QUERY needs the keyword date followed by a yyyy-MM-dd string. With the date in G1, build that string with TEXT:

=QUERY(A1:E9, "select * where A >= date '"&TEXT(G1,"yyyy-mm-dd")&"'", 1)

FILTER compares real date values directly, so there’s nothing to format.

9. Data from another file (IMPORTRANGE)

QUERY reads the imported array once. Columns are referred to as Col1, Col2… because there are no sheet column letters in an array:

=QUERY(IMPORTRANGE("SPREADSHEET_URL", "Orders!A1:E"), "select Col3, Col5 where Col2 = 'East'", 1)

With FILTER you’d have to repeat IMPORTRANGE for the data and for each condition. LET avoids that:

=LET(d, IMPORTRANGE("SPREADSHEET_URL", "Orders!A2:E"), FILTER(d, INDEX(d, 0, 2)="East"))

You have to connect the files once before either formula works. See fix “You need to connect these sheets”.

The four traps that should decide for you

Trap 1: QUERY drops values in mixed-type columns

Google’s QUERY documentation says that when a column has mixed data types, the majority type decides the column type and the minority values are treated as null. An Order ID column with 1001, 1002, 1003 and “A-17” can silently lose the “A-17” row in a where test, or show it as blank in the output. FILTER returns the cell as it is.

What to do: Keep columns consistent (convert IDs to all-text or all-numbers, see convert numbers stored as text), or use FILTER for that data.

Trap 2: Column letters inside the string don’t move

If you insert a column before B, a FILTER on B2:B9 updates itself to C2:C9. A QUERY that says where B = 'East' keeps pointing at whatever is now in column B. No error, just wrong results. For sheets that other people edit, that alone is a good reason to prefer FILTER, or to keep QUERY sources on a locked raw-data tab.

Trap 3: Empty results

Google’s FILTER documentation states that FILTER returns #N/A when no rows meet the conditions. Wrap it:

=IFNA(FILTER(A2:E9, B2:B9=G1), "No matching orders")

In the live test, QUERY retained its header with headers=1, but returned #N/A with headers=0 when nothing matched. If it’s an error, wrap it the same way with IFNA or IFERROR, but remember that IFERROR also hides real syntax errors.

Trap 4: Spill space

Both return arrays that need empty cells below and to the right. If anything is in the way you’ll see #REF! with “Array result was not expanded”. Fix that with this guide.

What about speed?

Google doesn’t publish performance comparisons for FILTER and QUERY, so we won’t claim one is faster. In practice, these habits matter more than the function you choose:

• Use bounded ranges (A2:E5000) rather than whole columns when the data size is predictable.
• Call IMPORTRANGE once and reuse the result (QUERY, or LET + FILTER) instead of several times in one formula.
• Don’t stack hundreds of copies of the same FILTER down a column. One formula that returns the whole result is lighter.

Using both together

Each one covers the other’s weak spot. Use FILTER for case-insensitive or regex conditions, then QUERY to aggregate the result. Because FILTER returns an array, QUERY must use Col notation:

=QUERY(FILTER(A2:E9, REGEXMATCH(D2:D9, "(?i)^(widget|gadget)$")), "select Col2, sum(Col5) group by Col2 label sum(Col5) 'Total'", 0)

The last argument is 0 because FILTER doesn’t pass a header row.

Excel users: FILTER is different and QUERY doesn’t exist

The Excel version of task 3:

=FILTER(A2:E9, (B2:B9="East")*(E2:E9>500), "No matches")

For QUERY-style grouping in Excel, use a PivotTable, or GROUPBY / PIVOTBY if your Excel 365 build has them.

Common mistakes

1. Quoting numbers in QUERY. where E > '500' compares text. Use where E > 500.
2. Using header names in QUERY. select Amount doesn’t work. Use the column letter (E), or Col5 when the data is an array. Google’s reference says to use column IDs, never labels.
3. Forgetting the headers argument. If QUERY guesses wrong, your header becomes a data row or your first data row becomes a header. Always pass 1 (or 0) explicitly.
4. Mismatched FILTER ranges. Google’s documentation requires each condition to be exactly the same length as the range. FILTER(A2:E9, B2:B10="East") returns an error.
5. Expecting = in FILTER to be case-sensitive. It isn’t. Use EXACT(C2:C9, "ann") as the condition when case matters.

FAQ

Is FILTER or QUERY faster in Google Sheets?

Google doesn’t publish a comparison. On normal-sized sheets the difference is rarely noticeable. Bounded ranges and calling IMPORTRANGE only once have a bigger effect than the choice of function.

Can FILTER sum or group data?

No. FILTER only returns matching rows. Use QUERY with group by, or combine UNIQUE with SUMIF or SUMIFS.

Why does my QUERY ignore some rows?

The column probably has mixed data types. QUERY uses the majority type for each column and treats the minority values as null. Make the column consistently numbers or consistently text.

How do I use a cell value in a QUERY where clause?

Concatenate it into the string: “select * where B = ‘“&G1&”’” for text, “where E > “&G1 for numbers, and “where A >= date ‘“&TEXT(G1,“yyyy-mm-dd”)&”’” for dates.

Does Excel have the QUERY function?

No. Excel has FILTER (with a different syntax), PivotTables and Power Query, but no QUERY function.

Related tutorials

• Google Sheets QUERY “Unable to parse query string” error
• Fix “You need to connect these sheets” (IMPORTRANGE)
• Fix “Array result was not expanded”
• ARRAYFORMULA vs MAP/LAMBDA vs fill down
• COUNTIF vs COUNTIFS vs SUMPRODUCT

Live-test findings

In live Sheets checks, an empty QUERY result with headers=1 retained the header; headers=0 returned #N/A, “Query completed with an empty output.” Mixed-type columns treated minority text values as null. GROUPBY and PIVOTBY returned #NAME? on the tested Mac Excel 16.114.4; use them only if your build provides them. IMPORTRANGE-dependent comparisons were not verified after authorization.

Sources

• Google Docs Editors Help: FILTER function (syntax, conditions must be the same length as the range, #N/A when nothing matches): https://support.google.com/docs/answer/3093197
• Google Docs Editors Help: QUERY function (headers argument, mixed data types: the majority type wins and the minority is null, Col notation): https://support.google.com/docs/answer/3093343
• Google for Developers: Query Language Reference v0.7 (clause order, case-sensitive string operators, lower()/upper(), string literals in single or double quotes, date literal format, column IDs vs labels): https://developers.google.com/chart/interactive/docs/querylanguage
• Google Docs Editors Help: LET function (names evaluated once and reused): https://support.google.com/docs/answer/13190535
• Microsoft Support: FILTER function (Excel syntax, if_empty, #CALC! for empty results, multiplying conditions): https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759