Google Sheets Filter Custom Formula: How It Works

Yes. In Google Sheets, choose Data > Create a filter, open a column’s filter menu, then select Filter by condition > Custom formula is and enter a formula that returns TRUE for rows you want to keep. For example, with headers in row 1 and sales in column C, =$C2>=500 keeps rows whose sales value is at least 500.

What a custom formula filter does

A custom formula filter lets you define a row-selection rule using a spreadsheet formula rather than choosing a built-in condition such as “Text contains” or “Date is after.” Sheets evaluates the rule against the rows in the filtered range and hides rows that do not meet it. The underlying data is not deleted or rearranged permanently; clearing the filter makes the hidden rows visible again.

The formula should evaluate to a logical result: TRUE means the row passes, and FALSE means it is excluded. You can test one value, combine several requirements, or refer to another column. That last option is especially useful when the column whose filter menu you opened is not the column that determines which records qualify.

For instance, a list of orders might need to show only orders from a particular region that also meet a sales threshold. A regular condition can check one column at a time, while a custom formula can test both conditions together. The rule applies to the rows in the filter range, so include the relevant columns in that range even if the formula checks only one of them.

How to add the custom formula

In Google Sheets in a web browser, select the data range, including its header row, and choose Data > Create a filter. Filter icons appear in the header cells. Open the icon on a column, choose Filter by condition, select Custom formula is from the condition list, enter the formula, and click OK.

Make sure the range contains a single header row and the records you want to filter. Avoid including unrelated notes or totals below the table.
Choose the column’s filter icon. The formula can refer to other columns in the same filtered range.
In the custom formula box, enter a formula beginning with an equals sign. Use the row number of the first data row, not the header row.
Apply the condition. To remove it, reopen the column menu and clear the condition, or use the filter controls to turn off the filter.

If your data begins on a different row, adjust the formula’s row reference. For example, a table with headers in row 4 and its first record in row 5 needs references such as =$C5>=500, not =$C2>=500. The formula’s starting row is a frequent source of filters that appear to return the wrong records.

How references work across rows

In a custom filter formula, write the reference as if you are testing the first data row. Sheets applies the rule down the rows in the filtered range. A relative row reference such as C2 can therefore become C3, C4, and so on as the condition is evaluated for later records.

The dollar sign controls what stays fixed. In =$C2>=500, the column C is locked while the row number can change. That is a reliable pattern when every record should be tested against the same sales column. In =C2>=500, neither the column nor the row is locked; that may be appropriate in some layouts, but it can be confusing if the condition is associated with a particular column. A reference such as =$C$2>=500 locks both, so every row would test the same cell rather than its own sales value.

Custom formula filters are not the same as writing a formula in a worksheet cell to display a result for every row. You enter the rule in the filter condition box, and Sheets uses it to decide which rows to show. It does not add a TRUE/FALSE helper column to the table.

Examples using multiple columns

Suppose A1:C6 contains a header row followed by these records: Ana, West, 650; Ben, East, 720; Chen, West, 420; Dia, West, 900; and Eli, East, 350. Assume the columns are Name, Region, and Sales. Apply any of the following conditions from a filter menu in that range:

With the first data row in row 2 and sales in C, use =$C2>=500 to keep Ana, Ben, and Dia. Chen and Eli are hidden because their sales are below 500.
With Region in B and Sales in C, use =AND($B2="West",$C2>=500) to keep Ana and Dia. Both tests must be true for a row to pass.
With the same layout, use =OR($B2="West",$C2>=800) to keep Ana, Ben, Chen, and Dia. A record passes if it is in the West or has sales of at least 800; Eli meets neither test.

For a condition that excludes empty names as well as low sales, use =AND($A2<>"",$C2>=500), assuming names are in A and sales are in C with the first record in row 2. This is useful when blank rows have slipped into a range. The <> operator means “not equal to,” and the empty quoted text represents a blank value.

For a date cutoff, with dates in D and the first data row in row 2, use =$D2>=DATE(2026,1,1) to keep dates on or after January 1, 2026. This expects actual date values in the cells, not text that merely looks like a date. Change the year, month, and day in DATE to set the cutoff you need.

Building rules for text, blanks, and duplicates

AND and OR are useful for combining criteria: AND requires every listed condition to be true, while OR requires at least one. You can add more tests inside either function, separating arguments with commas. For example, a sales threshold and a nonblank customer check can be combined without creating helper columns.

Text comparisons are suitable for exact category checks, such as a region name. For partial text matching, Sheets also provides functions such as SEARCH and REGEXMATCH, but choose them carefully: a regular expression has its own syntax, and REGEXMATCH is case-sensitive unless the expression is configured otherwise. If you only need a simple contains, starts with, or ends with rule, the built-in filter conditions may be easier to understand and maintain.

To highlight repeated identifiers through a filter, set up a rule that counts occurrences in the identifier column. With IDs in A2:A and the first record on row 2, use =COUNTIF($A$2:$A,$A2)>1. This keeps rows whose ID appears more than once in that column. If the table includes a header, start the counting range below it as shown; otherwise, a header value could affect the count.

Custom formula filter versus the FILTER function

The custom formula condition is an interactive view of the existing table: rows that fail are hidden in place. Google Sheets also has a worksheet function named FILTER, which returns matching records into a separate output area. Use the filter menu when you want to inspect or temporarily narrow the original list. Use FILTER when you need a live formula result elsewhere, such as a report or a second tab.

For example, with headers in A1:C1, data in A2:C6, regions in B, and sales in C, enter this in an empty area to return West orders with sales of at least 500:

With the stated data layout, =FILTER(A2:C6,B2:B6="West",C2:C6>=500) returns the rows for Ana and Dia, including their names, regions, and sales. The source data remains visible, while the result is shown where the formula was entered.

FILTER requires each condition range to have the same number of rows as the range being returned. If no records match, Google Sheets returns a #N/A error; that means there were no matching rows, not necessarily that the formula is malformed. You can wrap the result in IFNA if you prefer a message or blank output for that case. Unlike a menu filter, a FILTER result occupies cells and may be blocked if those cells are not empty.

Filter views and shared spreadsheets

A standard filter changes what is visible in the shared sheet for people using that sheet. If several people need different filtering arrangements, a filter view can be more convenient. In Google Sheets, choose Data > Filter views > Create new filter view, then configure the filter conditions within that view. Filter views let users work with their own saved or temporary view without changing everyone else’s view in the same way a standard filter can.

Custom formula conditions can be useful in either workflow, but check that the filter range is correct before sharing the sheet. A rule can look broken if new rows sit outside the range, or if someone inserts a title row above the table and the formula still refers to the old first data row. For recurring reports, keep headers consistent and make sure new records are included in the filter range.

Common problems and fixes

The wrong rows appear: Check the row number in the formula against the first data row. A formula referencing row 3 when the first record is in row 2 can shift the test by one row.
Every row passes or none pass: Check for a fully locked reference such as $C$2, which tests one fixed cell for every record. Use a locked column with a relative row, such as $C2, when each row needs its own value checked.
The formula is rejected: Confirm that it begins with =, has balanced parentheses, and uses the right separator for the function. In Google Sheets, function arguments are normally separated by commas; spreadsheet locale settings can affect separators and date formats.
Dates do not filter as expected: The cells may contain text rather than date values. Check the cell format and, if needed, convert the source data to dates before using a date comparison.
New records are missing: The filter may cover only the original range. Expand the range or recreate the filter over the complete table, and verify that the formula row reference matches its first record.
Values seem to disappear: A filter hides nonmatching rows; it does not erase them. Clear the condition or turn off the filter to show the full list again.

A useful debugging technique is to temporarily put the same logical test in a spare column for a few rows. If the helper cells do not return the TRUE/FALSE results you expect, the issue is in the logic or references. Once the test is correct, remove the helper column if it is no longer needed. This separates formula mistakes from range and filter-menu mistakes.

Frequently Asked Questions

Does a custom formula filter delete rows?
No. It hides rows that fail the condition. Clear the filter or turn it off to reveal them. Deleting rows requires a separate action.

Can one custom formula check more than one column?
Yes. Refer to the needed columns in the same formula, commonly by combining tests with AND or OR. For example, a rule can require both a particular region and a minimum sales value.

Does the formula go in a worksheet cell?
No. Enter it in the filter menu under Filter by condition > Custom formula is. A formula entered in a regular cell instead calculates a value in the sheet.

Why does my custom formula start with row 2?
Most tables have headers in row 1 and the first record in row 2. The formula should reference the first record so Sheets can evaluate later rows correctly. Use the actual first data-row number if your table starts elsewhere.

Should I use a filter condition or FILTER?
Use a filter condition to hide nonmatching source rows while inspecting the table. Use the FILTER function to create a live matching-results list in another range or sheet.

Choosing the right approach

Use a custom formula filter when the built-in conditions cannot express your rule, especially when it depends on multiple columns, a combination of tests, or a calculated check. Start with the first data row, use relative row references, and test the result on a small range before relying on it for a large shared table. For a quick, simple condition, the built-in options are usually easier; for a separate automatically updating result, use the FILTER function instead.

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.