To filter an Excel PivotTable for labels containing particular text, open the dropdown for the row or column field, choose Label Filters > Contains, enter the text, and select OK. Excel keeps the items whose labels include that text anywhere; it does not search every cell in the source data or filter numeric values by their displayed contents.
What a Contains filter does
A PivotTable contains a list of distinct items for each field used in its Rows or Columns area. A label filter limits which of those items appear. For example, if a row field lists product names, a Contains filter for desk can retain names such as Desk Lamp and Standing Desk while hiding other product names.
The filter applies to the selected PivotTable field, not automatically to every field or every source column. If you apply it to Product, it looks at the product labels. It will not find the word in a separate Notes field unless you apply a filter to Notes instead. A matching label can still summarize many underlying records: filtering a product name does not remove those records from the source worksheet.
Contains is useful when you know part of a label but not its full wording, or when many items share a word or fragment. It is different from selecting one or more items with checkboxes, which requires you to identify each item individually.
How to apply the filter in Excel for Microsoft 365
- Click a cell in the PivotTable so its contextual tabs appear.
- Find the dropdown beside the field you want to filter. In the default Compact Form layout, row fields may appear under a combined Row Labels heading. Use the dropdown beside that heading for a row field, or beside Column Labels for a column field. In layouts that show individual field headings, use the dropdown beside the relevant heading.
- Point to Label Filters, then select Contains. If Label Filters is unavailable, check that the field is in the Rows or Columns area and that you opened that field’s menu.
- Type the text fragment in the filter dialog and select OK.
Excel updates the PivotTable to show items matching the condition. The dropdown usually shows a filter indicator so you can tell that the field is restricted. To remove the condition, open the same field menu and choose Clear Filter From followed by the field name, or use the available clear-filter command in that menu.
On Mac, PivotTable field menus and label-filter options are also available, though their placement and appearance can differ by Excel version and layout. In Excel for the web, menu options can vary as well; if the Contains command is missing, open the workbook in the desktop Excel app.
What to enter and what matches
Enter the characters that should appear in the item label. A search for north, for example, can match North Region and Warehouse North. The fragment can occur at the beginning, middle, or end of a label; this is why Contains differs from a Begins With or Ends With condition.
Text filters in Excel are generally not case-sensitive, so a search for north ordinarily matches labels written as North or NORTH too. Leading or trailing spaces in source labels can nevertheless make data look inconsistent, and spelling differences still matter: a filter for color will not match a label spelled colour.
Contains searches the labels displayed for the selected PivotTable field. It does not mean contains a number in a calculation, and it is not a full-text search of the workbook. If an item label is abbreviated, calculated, or assembled from multiple source columns, the filter works with the item the PivotTable actually displays.
Example: find products by part of a name
Suppose a sales PivotTable has Product in Rows and Sum of Sales in Values. Its product labels include Desk Lamp, Standing Desk, Floor Lamp, and Office Chair. Applying Label Filters > Contains with desk leaves Desk Lamp and Standing Desk. The sales figures shown are still the PivotTable’s summaries for those product items; the filter has not changed the source sales records or rewritten the sales amounts.
This is handy for reporting on a naming convention, such as products containing a model code, locations containing a region name, or departments containing a shared word. It can also help with imperfectly standardized labels, but it does not merge differently named items. If the same product appears as Desk Lamp and Desk-Lamp, both may match the fragment, yet Excel will continue to treat them as separate PivotTable items unless you standardize the source data.
Label filters versus value filters
Choose Label Filters when the condition concerns the text or date label of a row or column item. Choose Value Filters when the condition concerns the summarized result, such as showing products with total sales greater than a threshold. These filters answer different questions: Contains asks whether an item’s label includes text; a value filter asks whether its calculated measure meets a numeric condition.
For example, if Product is in Rows and Sum of Sales is in Values, a label filter can find product names containing desk. A value filter can instead show products whose summed sales exceed a chosen amount. The value-filter command is associated with the row or column field whose items are being compared, and the PivotTable needs an appropriate value field to evaluate.
If the text you want to search is in the source data but is not a row or column label, first consider adding that source field to Rows or Columns. If that is not appropriate for the report, filter the source table before creating or refreshing the PivotTable, or add a helper field that categorizes the records. A text field placed in Values is commonly summarized as a count; that does not turn its contents into searchable labels there.
Filtering with more than one condition
The Label Filter dialog can offer a second condition, joined with And or Or. Use And when an item must satisfy both conditions, such as containing desk and also containing wood. Use Or when either match is acceptable, such as containing desk or table. The exact controls depend on the filter dialog and Excel build.
Be careful when combining a label condition with manual checkbox selections. A field can have more than one kind of filtering restriction, so the visible items may be limited by both the text condition and the selected-item list. If expected items are missing, reopen the field dropdown, inspect its search and checkbox selections, and clear any unintended restriction before applying the text condition again.
Refreshing data and troubleshooting results
A PivotTable works from its source range, table, or data connection and its stored PivotTable data. If source labels change or new records are added, refresh the PivotTable to update its items. In Excel for Microsoft 365 on Windows, click inside the PivotTable and select PivotTable Analyze > Refresh, or use Data > Refresh All when you need to update workbook connections too. A refresh updates the PivotTable from its source; it does not repair misspellings or make distinct labels identical.
- No items appear: Check the spelling and fragment, remove accidental spaces from the search box, and confirm that you opened the correct field’s menu. Also check other active filters that may exclude the matching items.
- A new label is missing: Refresh the PivotTable and confirm that the source range includes the new rows. If the source is a fixed cell range, newly added records may fall outside it; an Excel Table used as the source generally expands as rows are added.
- The filter seems to search the wrong thing: Verify the field heading. A label filter examines that field’s item names, not the numbers in the Values area or the contents of an unrelated source field.
- Similar entries remain separate: Check the source for inconsistent spelling, punctuation, or spaces. Clean or standardize the source labels if those items should be treated as one category, then refresh.
If labels are created by formulas in the source, make sure those formulas have recalculated and that the PivotTable is refreshed afterward. If the PivotTable is based on an external connection or the Data Model, refresh behavior can depend on that connection and its access settings.
When a different filtering method is better
Use the dropdown search box when you simply need to locate and select known items. It is a convenient way to check individual labels, but selecting search results is not always the same as setting a reusable Contains condition. Use Label Filters > Contains when the rule itself—matching a fragment anywhere in the label—is what you want to apply.
A report filter, placed in the PivotTable’s Filters area, lets a reader choose an item from a field to display a particular slice of the report. A slicer provides clickable buttons for selecting items. Neither is the same as a contains rule: they are useful for interactive selection, while a label filter expresses a text condition on a row or column field.
For complex text cleanup or matching across several source columns, prepare a helper column in the source data. For example, a category field could mark records whose description meets a business rule; the PivotTable can then summarize that category. This approach is often clearer than trying to make a PivotTable field filter perform data preparation, especially when the same rule must be reused in multiple reports.
Frequently Asked Questions
Can I filter a PivotTable for text that contains a word?
Yes. Open the dropdown for the relevant row or column field, choose Label Filters > Contains, enter the word, and select OK. The matching text must be part of an item label for that field.
Does Contains search every column in the source data?
No. It filters labels in the selected PivotTable field only. To search text held in another source column, add that field to the PivotTable’s Rows or Columns area, or prepare and filter the source data another way.
Can I use Contains on a number or sales total?
Contains is a label filter, not a numeric test. Use a Value Filter for a condition on a summarized number, such as sales greater than a threshold. If a number is actually stored and displayed as a text label in a row or column field, a label filter may apply to that label instead.
Why does my Contains filter return no results?
Confirm the selected field and the fragment’s spelling, then inspect other active filters and checkbox selections. Refresh if the source changed, and verify that the new records fall inside the PivotTable’s source range.
Will the filter combine similar product names?
No. It shows each matching item but does not consolidate labels. Standardize inconsistent names in the source data if they should represent the same category, then refresh the PivotTable.
Related
- Excel List Template Free? Where to Find and Use One
- Excel Long Number Format: Display More Digits Correctly
- Excel List of Weekday Dates: 10 Ways to Build a List
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Power Pivot vs PivotTable: Differences, Uses, and ChoiceExcel PivotTables summarize and explore data; Power Pivot adds a data-modeling layer for connecting tables, defining relationships, and…
- Excel Dashboard Format: Layout, Design, and SetupAn Excel dashboard is usually a formatted worksheet that brings key measures, charts, and filters together so people…
- Excel Pivot Table: Percentage of Two ColumnsTo show one PivotTable column as a percentage of another in Excel for Microsoft 365 on Windows, add…
- Shade Area of a Chart in Excel: Methods and TipsTo shade the area beneath a line in Excel, select the chart, go to Chart Design > Change…
- Excel Stacked Column Chart With Two Data Sets ExplainedYes. In Excel for Microsoft 365 on Windows, place the two data sets in adjacent columns beside the…
- Add a Bar Chart in Excel: Steps and TipsTo add a bar chart in Excel for Microsoft 365 on Windows, select the category labels and numeric…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.