Pinterest Pixel

Filter by Text wildcards

Pivot Table allows you to filter by text wildcard characters and make your filtering dynamic. You can... read more

Download Excel Workbook
Bryan
Posted on

Overview

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.
See also  6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

Pivot Filter by Values - Top or Bottom %

 

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

download excel workbookFilter-by-Text-wildcards.xlsx


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

Pivot Filter by Values - Top or Bottom %

 

Example 1: Use Asterisk (*)

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

Filter by Text wildcards

STEP 2: Select Label Filters.

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

Pivot Filter by Values - Top or Bottom %

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

Pivot Filter by Values - Top or Bottom %

And you have the values starting with Glo!

Pivot Filter by Values - Top or Bottom %

 

Example 2: Use Question Mark (?)

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

Filter by Text wildcards

STEP 2: Select Label Filters > Contains

Pivot Filter by Values - Top or Bottom %

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.

See also  Sort an Excel Pivot Table Manually

Click OK

Pivot Filter by Values - Top or Bottom %

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

Pivot Filter by Values - Top or Bottom %

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:

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Filter by Text wildcards | MyExcelOnline

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!