Filter Data Using Search Box
Use the Search Box
- Click on the drop-down arrow beside the field you want to filter in the Rows or Columns area of the pivot table.
- Look for the “Search” or “Search Box” option. This option might appear as a text box with a small magnifying glass icon.
- Type the keyword or search term you want to filter for in this search box.
- As you type, the pivot table will dynamically filter the items to display only those that match your search term.
- The pivot table will update in real time, showing only the items that contain the search term you entered.
Clear or Modify the Search in search box
- To clear the search and view all items again, either delete the text from the search box or click on the “x” icon within the search box.
- To modify the search, you can edit the search term directly in the search box.
WildCard Characters in the Search box
You can also use wildcard characters in the search box like asterisks (*) and question marks (?), to search for specific patterns within the data.
- An asterisk (*) represents any number of characters.
- A question mark (?) represents a single character.
For example, if you want to get the name of all the customers that start with the alphabet M, use the search string as M* (M followed by an asterisk).
Since an asterisk represents any number of characters, this means that the name can contain any number of characters after M.
Similarly, if you want to get the list of all the customers that end with the alphabet M, use the search term as *M (asterisk followed by M).
Another Example is that, if you want to get the name of all the customers whose first name is 4 characters long and then space and then middle or last name. Hence we will use ???? * (4 times question sign <spce> Astric sign) to get the desired result.
Also you can search many patterns with using these wildcard characters.
Therefore, Using the search box in a pivot table is a convenient way to filter data when you have a large dataset and need to quickly locate specific items based on keywords or terms.
There are a few important things to know about the search bar
- If you filter onc by one criteria/condition and then filter by other criteria, the first criteria/condition is discarded and you get a list as per the second criteria. Filtering is not additive.
- You can’t exclude specific text or terms results. For example, if you want to exclude only customers whose 1st name is Aaron, there is no way to do that using the search box. However, this can be done using a label filter using the ‘does not contain’ criteria.
- One advantage of using the search box is that you can manually deselect certain results. For example, if you have a huge list of financial companies and want to filter out only banks, you can search for the word ‘bank’. But if some companies come that are not banks, you can simply uncheck it and keep it out.