How to Filter Custom Formula in Google Sheets (3 Easy Examples)

Sometimes the available filter options of the spreadsheet just do not cut it. While there are many, the user may require a customized approach to filter their data. Thankfully, users can apply their version of a filter using a custom formula in Google Sheets.

Let’s see how it’s done.

Where Do You Input Custom Formulas for a Filter in Google Sheets?

Once you have applied a filter to a dataset in Google Sheets, you can access the filter menu by clicking on one of the filter icons beside the column headers:

the filter menu - filter custom formula in google sheets

Here, the main filter options are split into two:

  • Filter by Value (Default)
  • Filter by Condition

The Filter by condition section is where users may find the most common yet customized filter options. By clicking on the drop-down we can see all of these:

filter by condition menu

While these conditions are enough to satisfy the requirements of most users, if by chance these are not enough, you can always scroll down to the bottom of the menu to find the “Custom formula is” option.

custom formula is option in the filter menu of google sheets

It is in this “Custom formula is” section a user can apply their custom formula to filter data in Google Sheets.

the custom formula field for filter in google sheets

Examples of Using Custom Formula in a Filter of Google Sheets

With custom formulas in our hands, there’s virtually no limit to the criteria of filters we can apply in Google Sheets.

So, let’s have a look at some examples of the uses of custom formulas in a Google Sheets spreadsheet.

1. Match Text with Cell Reference

For our first example, we will use a custom formula to filter all matching text from the following dataset. In this case, the name Rita.

match criterion is in a separate cell

While we could’ve used the “Text contains” option from the Filter by condition section, using a custom formula enables us to use a cell reference to point to a criterion, making the filter more dynamic.

Step 1: Set the filter of the Name column to take custom formulas.

Filter > Filter by condition > Custom formula is

opening the custom formula is option in the filter menu

Step 2: Apply the following formula:


inputting the custom formula

The formula looks up the value in cell E2 and matches it down the name column. So, the filter shows only the matched values.

We’ve used absolute cell reference ($) for cell E2 to lock the cell reference for the criterion.

Step 3: Click OK to apply.

apply custom formula to match text and filter in google sheets


  1. This simple formula cannot match partial text values. (See the method with regular expressions later in this article)
  2. You have to apply the filter condition again when the criterion changes.

2. Filter Multiple Text Criteria in Google Sheets

Unlike our previous example, most text matches are usually done with regular expressions.

Regular expressions allow us to look for specific text in the cell, much like the existing text conditions in the filter.

existing text conditions for filter

The advantage of using regular expressions is that we can use other functions in tandem with it to enrich the custom formula.

For example, to find a partial text match we only need to use the REGEXMATCH function.

We will use the REGEXMATCH function to filter the reviews that start with the text “Great” from the following dataset:

a sample dataset with filter applied

The formula:


custom formula using regular expression match

Click OK to apply and see the filter result.

filter with custom formula in google sheets

What if we want to filter by multiple text criteria in Google Sheets?

As we have mentioned before, the biggest advantage of formulas is their customizability. So, filtering by multiple conditions with a custom formula is 100% possible in Google Sheets.

Continuing from where we left off, let’s say we want to now add the “Good” criteria to filter Review alongside “Great”.

Since we have two criteria and either is accepted, we can use the OR function to bring together the formula:


custom formula to match two text criteria in google sheets filter

The result:

two text match filter result

3. Filter if Cell Value Does Not Contain Text

This is virtually the opposite of what we did for the first example. But this time, we will use regular expressions to filter everything except the matched text.

For example, let’s say we want to omit all instances of “Great” reviews from the dataset.

Step 1: Set the Review column filter to take a custom formula.

Step 2: The formula with REGEXMATCH in the custom formula field will be:


custom formula to filter if cell doesn't contain text in google sheets

Step 3: Click OK to see the result.

filter all values that doesn't contain value in google sheets

Final Words

That concludes our simple guide on how we can apply and use a custom formula in a filter of Google Sheets.

The examples we discussed here are just the tip of the iceberg of what is possible to achieve using custom formulas in filters. But that is not to say that these aren’t important, as they can be applied to any feature that can take advantage of custom formulas in Google Sheets.

Feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply