Do you want to learn how to filter multiple values in the Pivot table “Filter by condition” in Google Sheets? Look no further! In this article, we will explore two methods to achieve this.
Method 1: Filtering Source Data
Experts recommend filtering the source data using Query or Filter before creating the Pivot table. By using Query or Filter, you can extract the required values and use them as the Pivot table source. This method provides better control over filtering multiple values in the Pivot output.
Method 2: Custom Formula in the Pivot Table Editor
If filtering the source data is not feasible, you can still filter multiple values in the Pivot table by using a custom formula within the Pivot editor panel in Google Sheets.
Filter Pivot Table Data (Part of Pivot Customization)
Filtering data is a powerful customization feature in the Pivot table. Using the “Filters > Filter by condition” option in the Pivot table panel, you can apply various built-in filter commands such as Text contains, Text does not contain, Is equal to, Is not equal to, Text starts with, Text ends with, and more.
However, the built-in filter commands, except for the custom formula, do not accept multiple values. For example, you cannot use multiple values in “Text contains” or “Text does not contain” filters.
To overcome this limitation, we will explore the custom formula option in the Pivot table filter.
Sample Data and Pivot Table to Filter by Text Does Not Contain
Let’s consider an example to illustrate how to filter multiple values in the Pivot table. In the sample data below, the source data is in the B2:D10 range, and the Pivot table (Insert menu > Pivot table) is created in cell F2 (range F2:G7) of the same sheet.
Suppose we want to exclude the rows that contain “2nd” or “3rd” in the “Attempt” field. To achieve this, we can use the built-in “Text does not contain” command in the Pivot table Filters. However, this command only allows excluding rows with a single value.
To exclude multiple values, we need to rely on the custom formula field in the Pivot table Filters.
Filter Multiple Values in Pivot Table Using Regexmatch in Google Sheets
To exclude rows containing multiple values, such as “2nd” or “3rd,” we can use the Regexmatch formula in the Pivot table Filters custom formula field.
=REGEXMATCH(Attempt, "2nd|3rd")=FALSEIn this formula, we use REGEXMATCH to match “2nd” or “3rd” and set the condition to FALSE since we want to exclude these values.
If you prefer, you can replace the field name “Attempt” with the range reference C3:C10.
=REGEXMATCH(C3:C10, "2nd|3rd")=FALSETo understand how this Regexmatch formula works within the Pivot table filter, refer to the image below.
When using Regexmatch outside the Pivot table editor, remember to include the ArrayFormula if the reference is an array (e.g., C3:C10) and avoid using field names outside the Pivot table editor.
Do you want to exclude more values? You can follow the syntax =REGEXMATCH(C3:C10, "2nd|3rd|4th|5th")=FALSE.
Including Multiple Values in the Text Contains
To include specific values in the Pivot table, use the Regexmatch formula as shown below. Note that this formula is similar to the multiple values “Does Not Contain” formula, but instead of =FALSE, we use =TRUE.
=REGEXMATCH(Attempt, "2nd|3rd")=TRUEI hope you found these Pivot table customization tips helpful and that you now feel confident in filtering multiple values in Google Sheets. For more exciting tips and tricks, visit Crawlan.com!
