Mehrab Imtiaz
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:
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:
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.
It is in this “Custom formula is” section a user can apply their custom formula to filter data 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.
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
Step 2: Apply the following formula:
=A2=$E$1
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.
Disadvantages:
- This simple formula cannot match partial text values. (See the method with regular expressions later in this article)
- 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.
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:
The formula:
=REGEXMATCH(C2:C11,"Great")
Click OK to apply and see the filter result.
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:
=OR(REGEXMATCH(C2:C11,"Great"),REGEXMATCH(C2:C11,"Good"))
The 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:
Step 3: Click OK to see the result.
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.
