Sometimes, the available filtering options in Google Sheets just aren’t enough. Even though there are many options, users may need a customized approach to filtering their data. Luckily, users can apply their own version of a filter by using a custom formula in Google Sheets.
Let’s find out how to do it.
Where to Enter Custom Formulas for Filtering in Google Sheets?
Once you have applied a filter to a set of data in Google Sheets, you can access the filter menu by clicking on one of the filter icons next to the column headers:
Here, the main filtering options are divided into two sections:
- Filter by value (default)
- Filter by condition
The “Filter by condition” section is where users can find the most common custom filtering options. By clicking on the drop-down list, we can see all these options:
Although these conditions are sufficient to meet the needs of most users, if by chance they are not enough, you can always scroll down the menu to find the “Custom formula is” option.
It is in this “Custom formula is” section that a user can apply their custom formula to filter data in Google Sheets.
Examples of Using a Custom Formula in a Google Sheets Filter
With custom formulas in our hands, there is practically no limit to the filtering criteria we can apply in Google Sheets.
So, let’s take a look at a few examples of using custom formulas in a Google Sheets spreadsheet.
1. Matching Text with a Cell Reference
For our first example, we will use a custom formula to filter all texts that match a given criterion from the following dataset. In this case, the criterion is the name “Rita”.
While we could have used the “Text contains” option from the “Filter by condition” section, using a custom formula allows us to use a cell reference to point to a criterion, making the filter more dynamic.
Step 1: Set the filter for the “Name” column to consider custom formulas.
Filter > Filter by condition > Custom formula is
Step 2: Apply the following formula:
=$E$2=A2
The formula searches for the value in cell E2 and matches it in the “Name” column. As a result, the filter only shows matching values.
We used an absolute cell reference ($) for cell E2 to lock the cell reference for the criterion.
Step 3: Click OK to apply.
Drawbacks:
- This simple formula cannot match partial text values. (See the method with regular expressions later in this article)
- You have to reapply the filter condition when the criterion changes.
2. Filtering Multiple Text Criteria in Google Sheets
Unlike our previous example, most text matches are usually done using regular expressions.
Regular expressions allow us to search for specific text within the cell, just like the existing text conditions in the filter.
The advantage of using regular expressions is that we can use other functions in conjunction with them to enrich the custom formula.
For example, to find a partial text match, we can simply use the REGEXMATCH function.
We will use the REGEXMATCH function to filter reviews that start with the text “Super” from the following dataset:
=REGEXMATCH(A2, "^Great")
Click OK to apply and see the filter result.
And what if we want to filter based on multiple text criteria in Google Sheets?
As we mentioned earlier, the greatest advantage of formulas is their customization capability. So, filtering based on multiple conditions with a custom formula is absolutely possible in Google Sheets.
Let’s continue where we left off. Now, let’s say we want to add the criterion “Good” to filter “Reviews” along with “Super”.
Since we have two criteria and either one is accepted, we can use the OR function to combine the formulas:
=OR(REGEXMATCH(A2, "^Great"), REGEXMATCH(A2, "^Good"))The result:
3. Filtering If Cell Value Does Not Contain Text
This is practically the opposite of what we did for the first example. But this time, we will use regular expressions to filter out everything except the matching text.
For example, let’s say we want to exclude all reviews containing “Super” from the dataset.
Step 1: Set the filter for the “Reviews” column to consider a custom formula.
Step 2: The formula with REGEXMATCH in the custom formula field will be:
=NOT(REGEXMATCH(A2, "^Great"))Step 3: Click OK to see the result.
In Conclusion
So, we have concluded our simple guide on how we can apply and use a custom formula in a Google Sheets filter.
The examples we have covered here are just the tip of the iceberg of what is possible to achieve using custom formulas in filters. But that doesn’t mean these examples are not important, as they can be applied to any feature that can benefit from custom formulas in Google Sheets.
Feel free to leave your questions or suggestions in the comments section below.
