When working with a medium-sized dataset, it’s easier to use a pivot table to group and analyze the data. However, one limitation of pivot tables is that you have limited options when it comes to filtering the data.
There are many instances where the value of the column you want to filter data by is made up of a series of strings. Take categories, for example; they usually appear in the form of strings chained together like “Crypto, Design, Dev, Tech”.
What if you only want to create a pivot table that displays zones that are under the Crypto and Tech categories?
The most straightforward solution is to combine the CUSTOM FORMULA filter option and REGEXMATCH to select the data you need.
If you’re like me, you probably try to match the column letters in the formula and are unable to get the result you want to achieve.
Instead of using =REGEXMATCH(B:B,"Crypto|Tech"), which assumes that the formula can recognize the column, you must match the column name like this: =REGEXMATCH(Categories,"Crypto|Tech"). This is because the pivot table recognizes the header name instead of the column letter.