Pivot Table allows you to filter by text wildcard characters and make your filtering dynamic. You can use the row label filter and enter the text with wildcards to get the desired result.
Wildcards can take place of any character and the two wildcards that you can use while filtering text in Pivot Table is:
- Asterisk (*) – This returns any series of characters before or after the asterisk
- Question Mark (?) – This returns text that contains one variable
You can check out the multiple scenarios here:
- When you add * after Glo, Excel will filter cells that contain text starting with Glo like Globex Corporation, Globo Gym American Corp, etc.
- When you add * before tech, Excel will filter cells that contain text ending with tech like Initech, Primatech, etc.
- When you add ? in between a and c, Excel will filter data and containers any one character in between a and c. For example – ABC Telecom, Monarch Playing Card Co, etc.
Download this Excel Workbook and follow the step-by-step tutorial below:
This is our current Pivot Table setup. Let us see how these filter by text wildcards work!
Example 1: Use Asterisk (*)
STEP 1: Click on the Row Label filter button in the Pivot Table.
STEP 2: Select Label Filters.
You will see that we have a lot of filtering options. Let us try out Begins With
STEP 3: Type in Glo*
This will give us all the channel partners that start with Glo, what comes after that doesn’t matter.
Click OK
And you have the values starting with Glo!
Example 2: Use Question Mark (?)
STEP 1: Click on the Row Label filter button in the Pivot Table.
STEP 2: Select Label Filters > Contains
STEP 3: Type in a?c in the Label Filter dialog box.
This will give us all the channel partners that have a<any character>c inside.
Click OK
You can see it matched “abc”, “arc” and “a c”
This is how you can make filtering in Pivot Table handy by using filter by text wildcard!
Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:
