FILTER vs QUERY: Sheets' two ways to slice data
In Google Sheets these overlap heavily, so people ask which to learn. The portability answer is decisive: FILTER now exists in Excel and LibreOffice too (verified by execution), while QUERY is Google Sheets-only — a sheet built on QUERY cannot be exported anywhere.
The differences at a glance
| FILTER | QUERY | |
|---|---|---|
| Keep rows matching conditions | Yes — its whole job | Yes (WHERE clause) |
| Select/reorder specific columns | No — returns whole rows of the range (combine with CHOOSECOLS) | Yes (SELECT B, D) |
| Sort, group, aggregate, pivot | No — compose with SORT/UNIQUE/etc. | Yes — ORDER BY, GROUP BY, PIVOT, SUM()... |
| Syntax style | Spreadsheet formulas | Mini-SQL in a text string |
| Google Sheets | Yes | Yes |
| Excel | Yes (365/2021) | No — #NAME? |
| LibreOffice (executed result) | Yes since 24.8 (quirks noted on its page) | No — not implemented |
Which should you use?
- FILTER — Anything that might ever leave Google Sheets — it's the only one of the two with a future in Excel/LibreOffice. Compose with SORT, UNIQUE, and CHOOSECOLS for most QUERY-like results.
- QUERY — Sheets-only workbooks where you genuinely need SQL-style grouping, aggregation, or pivoting in one formula — it has no equal for that, but accept the lock-in.
Compatibility (from executed tests)
We executed FILTER in real LibreOffice: #NAME? in 24.2, working (with edge-case quirks — see its function page) from 24.8. QUERY is documented only by Google; in Excel and LibreOffice it does not exist at all. Exporting a QUERY-based sheet to .xlsx silently breaks every QUERY cell.
Example formulas
| FILTER (portable) | =FILTER(A2:C10,C2:C10>100) |
| QUERY (Sheets only) | =QUERY(A1:C10,"SELECT A, SUM(C) WHERE C > 100 GROUP BY A",1) |
Full per-version details on each function page: FILTER · QUERY.