Advanced Filter in Google Sheets: Methods and Examples

Google Sheets does not have a single command named “Advanced Filter” like Microsoft Excel. To filter a table interactively, use Data > Create a filter or a saved filter view; for more complex criteria or a separate, automatically updating result, use a custom filter formula, the FILTER function, or QUERY.

What “advanced filter” means in Google Sheets

The phrase can mean two different things. Some people want to apply several conditions to a table, such as showing open orders from one region. Others are looking for Excel’s Advanced Filter tool, which can filter a list using a criteria range and, in some cases, copy matching or unique records elsewhere. Google Sheets has no direct, one-for-one Advanced Filter dialog. Its filtering tools are distributed across menus, formulas, and functions instead.

For a quick view of the original table, use the built-in filter controls. For a reusable view that should not change what collaborators see, use a filter view. If you need the matching records displayed in another area and refreshed as the source changes, use a formula such as FILTER or QUERY. Choosing the right approach first helps avoid building a formula when a menu filter would do, or expecting an interactive filter to create a separate output table.

Apply an interactive filter to a table

In Google Sheets in a web browser, select a cell in a well-formed data range, then choose Data > Create a filter. Sheets adds filter icons to the header row. Open the icon for a column to select or clear particular values, sort the data, or filter by a condition. The filter acts on the existing rows; it hides rows that do not meet the chosen criteria rather than deleting them.

Make sure the data has one header row and that each column has a descriptive heading.
Click a cell in the data, then choose Data > Create a filter.
Open a column’s filter menu and choose values or select Filter by condition.
Choose a condition, enter the value or rule, and apply it. Set another column’s filter to narrow the visible rows further.

For example, a sales table might have Region, Rep, Status, and Sales columns. Filtering Region to North and Status to Open shows records satisfying both column filters. Conditions on different columns combine as an AND: a row must pass each active filter. Within a list of selected values in one column, selecting North or South includes either selected value. This is often enough for everyday multi-condition searches.

Keep the header row distinct and avoid blank rows or merged cells inside the data. A filter needs to identify the intended range and its headings; an irregular layout can cause it to include too little, too much, or interpret a row incorrectly. If the wrong range is filtered, remove the filter and select the complete table before creating it again.

Use filter views for saved or personal filters

A standard filter changes the visible state of the sheet, which can be inconvenient when several people are working in the same file. A filter view gives you a separate way to sort and filter the data without imposing that view on everyone else. In the browser, choose Data > Filter views > Create new filter view. Apply conditions as you would with a regular filter, then use the filter-view controls to name and close the view. Saved views can be reopened from the same menu.

Filter views are useful for recurring tasks: one view for late orders, another for each sales team, or a view showing records that need review. They do not create a copy of the underlying data; they are saved ways to inspect the same range. A person who needs a static snapshot should copy and paste the resulting rows elsewhere, understanding that the copy will not automatically stay in sync.

Filter views are especially helpful in shared spreadsheets, but they are not a permission or privacy feature. Hiding rows with a filter does not protect sensitive information. Anyone with suitable access to the file may be able to change the view or inspect the data, so control access to the spreadsheet itself when confidentiality matters.

Build more specific criteria with a custom formula

When a built-in condition is not expressive enough, a filter condition can use a custom formula. This is useful for rules that combine columns, such as “Region is North and Status is Open,” or “Region is North or Sales is at least 300.” Create a filter, open the relevant column’s filter menu, choose Filter by condition, then select Custom formula is. Enter a formula that evaluates to TRUE for rows to keep.

Suppose A1:D5 contains the headers Region, Rep, Status, and Sales, followed by these four records: North, Ana, Open, 240; South, Bo, Closed, 180; North, Cam, Open, 320; West, Dee, Open, 90. With the custom formula applied to the data rows beginning at row 2, enter =AND($A2="North",$C2="Open"). It keeps Ana’s and Cam’s records, because both have North in column A and Open in column C. The dollar signs lock the column references while the row number adjusts for each record.

For an OR rule on the same data, use =OR($A2="North",$D2>=300). This includes a row if its region is North or its sales value is at least 300. Ana and Cam match because they are in North; neither Bo nor Dee meets either condition. These examples illustrate a key detail: the formula’s row reference should correspond to the first data row, not the header. A reference starting at row 1 can make the results appear shifted or incorrect.

Custom formulas can also use functions such as AND, OR, REGEXMATCH, and date comparisons, as long as the formula returns a TRUE or FALSE result for each row. Keep the rule understandable: a complicated formula can be difficult for another editor to maintain. For a repeatable report or a result table, a formula in a separate range may be clearer than embedding a long rule in the filter menu.

Create a live results table with FILTER

The FILTER function returns matching rows in a destination area rather than hiding nonmatching rows in the source. This distinction matters: it can make a report, while the built-in filter changes which source rows are visible. In an empty cell outside the source table, enter a formula whose output area has room to expand. Do not place notes or other data where the results need to spill.

With the same headers in A1:D1 and records in A2:D5, enter =FILTER(A2:D5,C2:C5="Open") in an empty cell. It returns the three open records—Ana, Cam, and Dee—with all four columns. The condition range C2:C5 has the same number of rows as the data range A2:D5, which is required for the rows to line up. Add the headings separately if you want them above the results.

Multiple conditions can be combined by multiplying TRUE/FALSE tests for AND behavior. For the same data, =FILTER(A2:D5,(A2:A5="North")*(C2:C5="Open")) returns Ana and Cam. A plus sign between condition tests can provide OR behavior: a row is included if either test is true. If no records match, FILTER returns an error rather than a blank result; you can handle that deliberately with IFNA, for example =IFNA(FILTER(A2:D5,C2:C5="Urgent"),"No matches"). In this example the result is the text “No matches,” since none of the four records has Urgent status.

Because the results are formula output, do not type into cells that the returned array needs to occupy. Also check whether the source range should expand as new records are added. A fixed range such as A2:D5 will not include row 6 automatically; use a suitably open-ended range when appropriate, while avoiding unnecessarily large ranges that may slow a complex sheet.

Use QUERY for selection, sorting, and summaries

QUERY is another option when you want a formula-generated report and need to express several operations together. It uses Google Visualization API query language, which resembles SQL but is not full SQL. It can select columns, filter rows, sort results, group data, and perform some aggregations. That makes it useful when a report needs more than a simple match.

With the headers in A1:D1 and the four records in A2:D5, enter =QUERY(A1:D5,"select A, B, D where C = 'Open' and D >= 200",1) in an empty area. The final argument, 1, tells Sheets that the source range contains one header row. The query returns the rep and sales columns for open records with sales of at least 200: Ana with 240 and Cam with 320. The query string uses single quotes around the text value Open; those quotes are part of the query language inside the formula.

Use FILTER when the main goal is to return rows that meet straightforward conditions and you want ordinary spreadsheet references. Consider QUERY when selecting particular columns, sorting, grouping, or summarizing in one formula is valuable. Neither function is the same as a menu filter: both generate results elsewhere, and their output changes when their source data or criteria change.

Choose the method that fits the job

Use Create a filter for quick, temporary sorting and filtering directly in the source table.
Use a filter view when you want a named view or want to work with filters without changing the shared view for collaborators.
Use a custom formula condition when the interactive filter needs a rule combining columns or applying logic not covered by a standard condition.
Use FILTER to display matching rows in a separate, updating output range with relatively direct criteria.
Use QUERY when the output should select, sort, group, or summarize data in a more report-like way.

Think about whether the result needs to be interactive, shareable, or reusable before choosing. A filter view is easiest for people who want to inspect the source themselves. A formula result is easier to place in a dashboard or another worksheet, but it requires room for the output and some understanding of formulas. If you need a fixed handoff, copy the results and paste values into a separate area; that snapshot will no longer update automatically.

Common problems and how to fix them

If a filter hides records you expected to see, check every active column filter. Multiple conditions on separate columns narrow the result together, so an old Status or date filter can silently exclude rows. Also check for inconsistent entries such as “Open ” with a trailing space, text-formatted numbers, or dates stored as text. Values that look identical on screen may not compare identically.

Headers are included in the results: confirm the selected range and the header setting. For QUERY, the final header-count argument should reflect the number of header rows.
A custom formula selects the wrong rows: reference the first data row and lock columns where needed, such as $A2. Avoid locking the row number if the test needs to adjust for each record.
FILTER shows an error: make sure each condition range has the same height as the data range, that the destination cells are empty, and that you have accounted for a no-match result.
QUERY returns unexpected results: check the column letters, header count, data types, and quotation marks in the query string. Numeric values and dates should be stored consistently rather than mixed with text.
New records do not appear: extend a fixed source range or choose a range that includes future rows, and verify that the output area has room to expand.

When results matter, test a rule against a few records whose expected outcome is obvious. This is particularly helpful with dates, OR conditions, and formulas that combine several columns. A carefully named filter view or a short note beside a report can also help the next editor understand what the criteria are meant to do.

Frequently Asked Questions

Does Google Sheets have an Advanced Filter button?
No. Sheets does not provide a single command with the same name and behavior as Excel’s Advanced Filter. Use filter views or custom filter conditions for interactive work, and FILTER or QUERY when you need a separate formula-driven result.

Can I filter by more than one condition?
Yes. You can apply filters on multiple columns, and a row must satisfy each active column filter. For more flexible logic, use a custom formula condition, or combine conditions in a FILTER or QUERY formula.

Can a filter copy matching rows to another location?
A standard menu filter hides rows in the original range; it does not create a separate live copy. Use FILTER or QUERY to generate an updating result elsewhere, or copy and paste rows when you only need a static snapshot.

What is the difference between a filter and a filter view?
A filter changes the visible state of the range, which can affect what other editors see. A filter view is a saved, separate view for sorting and filtering the shared data, so it is generally more suitable when collaborators need different ways to inspect the same table.

Why does FILTER return an error when there are no matches?
That is the function’s normal no-match result. If you prefer a message or another fallback, wrap the function in IFNA, for example =IFNA(FILTER(A2:D5,C2:C5="Urgent"),"No matches"). Make sure the fallback text is enclosed in quotation marks.

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.