How to Filter a Google Sheets Pivot Table with a Custom Formula

Do you want to filter a pivot table in Google Sheets using a custom formula? Pivot tables are one of the best data presentation tools available in any spreadsheet application. They allow you to summarize and analyze large amounts of data quickly and efficiently. In this article, we will explore how you can use custom formulas to filter pivot tables in Google Sheets.

Filtering with a Custom Formula in a Google Sheets Pivot Table

Let’s start with an example. We have created the following dataset:

And its corresponding pivot table, showing scores by region:

By default, Google Sheets provides a filtering option for pivot tables. You can find this option near the bottom of the pivot table editor. However, when it comes to filtering specific data with conditions, the default option has some limitations.

Limitations of the Regular Pivot Table Filter

Let’s consider a scenario where we want to find all regional scores recorded after February 1, 2022, in the pivot table. If we use the default date filter, we will get something like this:

All individual date entries are listed. That means every time we add a new date, a new option will be saved. This makes the regular pivot table filter quite ineffective when dealing with conditional data such as “greater than” or “less than”.

But fortunately, we can use custom formulas to filter pivot tables in Google Sheets.

Simple Custom Formula for Filtering in a Pivot Table (Date Filtering)

Let’s go back to the scenario of filtering regional scores after February 1, 2022, in a pivot table. This time, we will use a custom formula to apply the condition.

Step 1: Add the “Date Recorded” field in the “Filter” section of the “Pivot Table Editor”.
Step 2: Click the dropdown menu under the “Status” field. This will display all available condition options. Select the “Filter by condition” option.

Step 3: Scroll down to find and select the “Custom formula is” option.

Step 4: Enter the following custom formula:
'Date Recorded' > DATE(2022,2,1)

Step 5: Click “OK” to apply the custom formula.

Here’s a detailed breakdown of the formula:

• 'Date Recorded': The header name from which we get the date values. This header reference needs to be enclosed in single quotes (”). Note that all header names from the source dataset are listed on the right side of the pivot table editor.
• >: The “greater than” condition. We can also use ">=" to include the starting date.
• DATE(2022,2,1): The reference date for the starting date. We used the DATE function to avoid any formatting errors in the entered date.

Benefits of Using a Custom Formula as a Filter in a Pivot Table

Using a custom formula to filter data in a Google Sheets pivot table is not only easy but also incredibly powerful. Here are some advantages:

1. Users can apply any condition they want.
2. An unlimited number of conditions can be applied.
3. Users can use formulas with cell references, taking advantage of locked rows or columns.
4. Formulas are fully customizable, thanks to the ability to use regular expressions.

Using a Custom Formula with Regular Expressions to Filter a Pivot Table

Regular expressions are perhaps the best way to customize all conditions for formulas in Google Sheets. Let’s take an example to show you what we mean.

Suppose we want to filter the pivot table to display data from all regions containing “East”. This would include regions like Northeast or Southeast. Essentially, any region data that contains the text “East”.

But before we move to the pivot table, let’s see what happens when we search for “East” using a formula without regular expressions. The formula is as follows:
=A1="East"

All cell values that don’t have an exact text match are shown as “FALSE”. Unless we include other values that contain “East”, the general formula won’t work for our condition.

Now, let’s do the same thing with a regex function: REGEXMATCH. It works similar to the previous IF function to search the range and find the given value. The formula is:
=REGEXMATCH(A1,"East")

The REGEXMATCH function works similarly to IF to find the correct match. But this time, the function also works for partial matches (it is still case-sensitive). This is one of the biggest advantages of using regular expressions.

Now, let’s apply a similar formula to the custom formula filter of the pivot table in Google Sheets:

Step 1: In the pivot table editor, select “Region” as the filter. As before, select the “Custom formula is” option.

Step 2: Enter the following custom formula:
=REGEXMATCH(A1,"East")

Note that instead of a range of cells, we used the column header name (Region) for the text/range argument of the REGEXMATCH function. This is what you need to do in pivot tables. It’s simple and easy to apply.

Step 3: Click “OK” to apply the filter.

Filtering with Multiple Conditions in a Pivot Table

Another advantage of using regular expressions is that you can add multiple conditions of the same type in the same formula.

For example, let’s say we also want to include “West” as a condition. With a slight update, the formula would look like this:
=REGEXMATCH(A1,"East|West")

The “|” symbol represents “OR” in regular expressions. This sets the condition to include either “East” or “West” in the filter.

The result:

In Conclusion

Using a custom formula to filter data in a Google Sheets pivot table is not only easy but also incredibly useful. With the customization of formulas in Google Sheets, there is virtually no limit to the filtering conditions that a user can apply.

If you have any questions or tips, feel free to leave them in the comments section below.

Check out Crawlan.com for more helpful Google Sheets tips and tricks!

Related Article to Read:

• How to Sort a Pivot Table in Google Sheets (Easy Guide)
• Formatting a Pivot Table in Google Sheets (3 Easy Methods)