Google Sheets Filter vs Query: Which Should You Use?

Use a regular filter or filter view to temporarily hide rows while you inspect a sheet; use the FILTER function to return matching rows in a formula-driven results area; use QUERY when you need to select, sort, group, or summarize data with a query statement. In short, filters are interactive, while the two functions create calculated outputs.

What “filter” means in Google Sheets

“Filter” can refer to two different things in Google Sheets: the controls applied to a table, or the FILTER formula. Both narrow a set of records, but they behave differently. A table filter hides rows in the original range without deleting them. The FILTER function instead displays matching values in another location, leaving the source rows visible and unchanged.

QUERY is a separate formula, not a more advanced menu filter. It reads a range and returns a calculated result based on a query-language instruction. That makes it capable of filtering records, but also of choosing columns, ordering results, grouping rows, and calculating summaries.

Menu filter or filter view: best for a person exploring or reviewing the original table.
FILTER: best for a formula-based list of rows that meet conditions.
QUERY: best for a shaped or summarized output, especially when several operations belong together.

How menu filters and filter views work

To apply a basic filter in Google Sheets in a web browser, select the data range or a cell in the table, then choose Data > Create a filter. Filter icons appear in the header row. Use an icon to filter by values, condition, or color, or to sort the range. Rows that do not meet the chosen criteria are hidden, not removed. Clear the filter to show them again.

A basic filter changes the view of the shared sheet, so other people looking at that sheet may see its effect. If people need separate ways to inspect the same shared data, use a filter view instead: choose Data > Filter views > Create new filter view, then configure and name the view. Filter views let users work with their own filtered view without changing what other collaborators are currently seeing. They are useful for a team that shares one source table but needs different regions, owners, or date ranges.

These controls are convenient for one-off questions, such as “Which orders are still open?” They do not create a separate table of results that another formula can reference as an independent output. They also require someone to interact with the sheet to change the criteria. For a report that should refresh as data changes, a formula is usually more suitable.

How the FILTER function works

The FILTER function returns rows or columns from a range when corresponding conditions are true. Its usual form is FILTER(range, condition1, [condition2, ...]). Each condition must line up with the range along the dimension being tested: for a row-by-row filter, a condition covering rows 2 through 100 should have the same number of rows as the data range. Multiple conditions are combined, so the returned records must satisfy each one.

For a concrete example, assume a table has headers in row 1 and records in A2:D6: salesperson in A, region in B, product in C, and sales amount in D. Suppose the rows are Ana, West, Mug, 120; Ben, East, Pen, 80; Ana, West, Notebook, 200; Cara, West, Pen, 90; and Ben, West, Mug, 150. With that layout, this formula returns the three West-region records with sales of at least 100, in their original row order:

=FILTER(A2:D6,B2:B6="West",D2:D6>=100)

The output is Ana’s Mug sale of 120, Ana’s Notebook sale of 200, and Ben’s Mug sale of 150. It appears in cells below or beside the formula, so leave enough empty space for the results to expand. If matching data changes, the displayed results update with recalculation. If no row matches, FILTER returns #N/A; that is a no-match result, not evidence that a record was deleted. A formula can be wrapped in an error-handling function if a blank or custom message is preferable.

How QUERY works

QUERY takes a data range, a query string, and an optional number of header rows: QUERY(data, query, [headers]). The query string uses Google Visualization API Query Language syntax. Common clauses include select to choose columns, where to filter, order by to sort, and group by to summarize records by a field. It can also use operations such as sum and count for aggregations. This is a query language inside a Sheets function, not SQL with every SQL feature.

Using the same A1:D6 layout, the following formula selects each salesperson and their total sales in the West region, groups the records by salesperson, and sorts totals from largest to smallest. The final argument tells Sheets that the first row contains headers:

=QUERY(A1:D6,"select A, sum(D) where B = 'West' group by A order by sum(D) desc label sum(D) 'West sales'",1)

For the example records, the output is Ana with 320, Ben with 150, and Cara with 90. Ana’s total is 120 + 200 = 320; the other totals are their single matching sales. The label clause changes the displayed heading for the calculated column. For an ordinary range, query expressions commonly refer to columns by letters; when the input is an array expression, column identifiers such as Col1 may be needed. The optional header argument helps Sheets interpret the range’s header rows; it is not a replacement for checking that the range and its data types are consistent.

Key differences at a glance

Where results appear: a menu filter hides rows in place; FILTER and QUERY spill results into the formula’s output area.
How criteria are changed: menu filters are adjusted through controls; formula criteria are changed by editing the formula or referencing cells that contain criteria.
What the result can do: FILTER is direct and readable for returning matching values. QUERY can also select columns, sort, group, and aggregate.
Best audience: an interactive filter helps someone browsing a table; a formula result can serve as a repeatable report or feed another calculation.
Impact on source data: none of these tools deletes matching or nonmatching source records. A menu filter hides rows; formulas generate separate results.

One practical distinction is that a formula output is not an editable copy of the source records. A user can inspect or use the returned values, but should make changes in the source table if those changes need to become part of the underlying data. Similarly, sorting a filtered table with the menu changes the row order in that table, whereas sorting a formula result changes only the order of the returned output.

Which option should you choose?

Choose the tool based on what you want to happen after the rows are narrowed down. For quick exploration, the menu filter is generally the least complicated choice. It avoids writing a formula and lets you switch conditions through the column controls. Use a filter view when collaborators should be able to look at different subsets without disrupting one another’s view.

Choose FILTER when you want a live subset in a separate area and the logic is straightforward. For example, a dashboard could show all support tickets assigned to one team, or a separate tab could list orders above a chosen amount. Referencing a cell for a criterion can make the report easier to adjust without editing the formula itself.

Choose QUERY when the result needs several transformations together. A monthly sales summary might select the salesperson and total, keep only a particular region, group by salesperson, and sort by total. A query can express those steps in one formula, though the query text can be less approachable to readers unfamiliar with its clauses. If you only need a simple matching list, using QUERY may add complexity without much benefit.

It is also reasonable to combine tools. Keep a filter or filter view on the source tab for manual inspection, and put a QUERY report on another tab for a stable summary. Avoid placing a formula’s expanding output inside a crowded range: existing values in the spill area can prevent results from appearing. Keep the source table organized with one header row and consistent columns, and make sure the formula’s input range includes the records you expect.

Common problems and misconceptions

A frequent mistake is treating a filter as if it removed data. A hidden row is still present and can reappear when the criteria are cleared. Another is using the word “filter” without specifying whether it means the menu feature or the function. When asking for help, say “filter view,” “menu filter,” or “FILTER formula” to make the intended tool clear.

With FILTER, check that each condition range aligns with the data range. For example, a range beginning at row 2 should not be paired with a condition beginning at row 1. Also check the output area for existing content if a result does not expand. With QUERY, verify the header count, column identifiers, spelling of text values, and data consistency. A column mixing numbers, text, and blanks can lead to confusing results because Sheets must interpret the column’s values for the query.

Finally, QUERY is not simply a menu filter written as a formula. It can filter, but its value is the ability to express a shaped result and summary. Conversely, a menu filter does not require you to learn query syntax, and it remains the better choice when the task is simply to inspect the original rows interactively.

Frequently Asked Questions

Is QUERY better than FILTER in Google Sheets?
Not universally. QUERY is more capable for selecting columns, sorting, grouping, and summarizing. FILTER is usually simpler when you only need rows that meet one or more conditions.

Does a Google Sheets filter delete rows?
No. A menu filter hides rows that do not match its criteria. The underlying records remain in the sheet and can be shown again by clearing or changing the filter.

Can I use FILTER and QUERY on the same data?
Yes. They can each read the same source range, and one formula’s output can sometimes be used as another formula’s input. For a maintainable sheet, avoid adding unnecessary layers when a single function can express the task clearly.

Why does FILTER show #N/A?
It commonly means no rows matched the conditions. Confirm that the criteria match the actual values and that the condition ranges align with the data range. If an empty result is expected, use suitable error handling to display a clearer alternative.

Can other people use my filter without changing my view?
Use a filter view for that situation. A basic filter affects the shared sheet’s current view, while filter views let collaborators work with separate saved views of the same data.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.