Filter by Text wildcards | MyExcelOnline

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.

Filter by Text wildcards | MyExcelOnline

 

Download this Excel Workbook and follow the step-by-step tutorial below:

DOWNLOAD EXCEL WORKBOOK


This is our current Pivot Table setup. Let us see how these filter by text wildcards work!

Filter by Text wildcards | MyExcelOnline

 

Example 1: Use Asterisk (*)

STEP 1: Click on the Row Label filter button in the Pivot Table.

Filter by Text wildcards | MyExcelOnline

STEP 2: Select Label Filters.

You will see that we have a lot of filtering options. Let us try out Begins With

Filter by Text wildcards | MyExcelOnline

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

Filter by Text wildcards | MyExcelOnline

And you have the values starting with Glo!

Filter by Text wildcards | MyExcelOnline

 

Example 2: Use Question Mark (?)

STEP 1: Click on the Row Label filter button in the Pivot Table.

Filter by Text wildcards | MyExcelOnline

STEP 2: Select Label Filters > Contains

Filter by Text wildcards | MyExcelOnline

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

filter by text wildcard

You can see it matched “abc”, “arc” and “a c”

Filter by Text wildcards | MyExcelOnline

This is how you can make filtering in Pivot Table handy by using filter by text wildcard!

 

HELPFUL RESOURCE:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn