To add a custom filter to an Excel PivotTable, open the drop-down beside a row or column field, choose Label Filters or Value Filters, select a condition, and enter the criteria. Use a label filter to match the text or date labels in a field; use a value filter to include categories based on their calculated totals or other summarized values.
What a custom PivotTable filter does
A PivotTable filter narrows the categories shown in a summary without deleting the source data. A custom filter lets you define a rule, such as showing product names that contain a word, regions whose sales exceed a threshold, or dates within a chosen period. The PivotTable recalculates its displayed summary around the records that meet the filter.
The key distinction is what the rule evaluates. A label filter examines the items listed in a field, such as the text names in Product or the date labels in Order Date. A value filter examines a summarized result associated with each item, such as Sum of Sales by Region. These can produce very different outcomes: filtering for a product name containing “Pro” is not the same as keeping products whose sales total is greater than $10,000.
Filtering a PivotTable does not normally remove or change rows in the original data range or Excel Table. It changes what the report displays. That makes PivotTable filters useful for analysis, but it also means the source data still needs to be managed separately if records should actually be excluded from a dataset.
Choose the right filter type
Start by asking whether the rule concerns a field’s item labels or the numbers calculated for those items. In Excel for Microsoft 365 on Windows, open the arrow for the relevant PivotTable row or column field to see the available filter choices. The menu wording can vary slightly with the field type and report layout.
- Label Filters: Match text or date labels, for example Product begins with a word or Customer does not equal a particular name.
- Value Filters: Keep or exclude items according to a summarized measure, for example regions with Sum of Sales greater than a specified amount.
- Date Filters: For date fields, use date-based choices such as a range or a relative period when those options appear. These filter the date items, not a sales total.
- Manual selection: Check or clear individual items in the field list when you already know which categories to show. This is selection, not a rule that automatically matches new items.
- Report Filter: Place a field in the Filters area to choose one or more field items for the whole report. This is useful for selecting a department or year, but it is not the same as a custom label or value condition.
In a compact PivotTable layout, the field drop-down may be labeled Row Labels or Column Labels rather than showing the field name. If the PivotTable has several fields in the same area, identify which field’s items you want to filter before applying a rule.
Apply a label filter
Label filters are useful for text categories, codes, and date labels. For example, a report might list hundreds of product names, and you want to see only products containing the word “wireless.” The filter works on the product names, regardless of how much each product sold.
- Click the drop-down arrow for the relevant row or column field in the PivotTable.
- Point to Label Filters, then choose a condition such as Contains, Begins With, Equals, or Does Not Equal.
- Enter the text to match in the filter dialog. For the example, choose Contains and enter wireless.
- Choose OK. The PivotTable keeps the matching labels and updates its displayed summary.
Label filters are generally not case-sensitive, so a search for wireless will ordinarily match Wireless as well. Be precise about punctuation and spaces: a trailing space in a source label can make it look like an otherwise identical item while preventing an exact match. If a label filter seems to miss expected items, inspect the source values and refresh the PivotTable after correcting them.
Apply a value filter to summarized results
Use a value filter when the decision depends on a calculation in the PivotTable. Suppose the rows are Region, the Values area contains Sum of Sales, and you want only regions with sales above $10,000. Open the Region field’s drop-down, choose Value Filters, then select Greater Than. In the dialog, choose Sum of Sales as the value field and enter 10000.
The threshold applies to each region’s summarized sales, not to each individual transaction in the source data. If a region has many sales rows, Excel evaluates the aggregate for that region. This explains a common surprise: an individual sale below $10,000 can still belong to a region that passes a greater-than-$10,000 value filter because the region’s total exceeds the threshold.
Value Filter dialogs may offer conditions such as Equals, Greater Than, Less Than, Between, and Top 10, depending on the field and filter menu. Check the value field named in the dialog before confirming. A PivotTable can contain several measures, such as Sum of Sales and Count of Orders; choosing the wrong one changes which items qualify.
Use two conditions with And or Or
Some custom-filter dialogs allow two conditions. For instance, a label filter could keep names that begin with North and end with Branch. A value filter could keep totals above one limit and below another. Enter the first condition, select the appropriate logical choice, then set the second condition.
And means an item must meet both conditions. It is appropriate for a range, such as a value greater than 1,000 and less than 5,000. Or means an item can meet either condition. It is useful for including alternatives, such as labels equal to East or West. If the results look unexpectedly broad or narrow, check the And/Or selection before changing the source data.
Not every filter menu provides the same two-condition dialog. The choices depend on the type of field and the condition selected. If the required rule cannot be expressed in that dialog, consider filtering the source data before building the PivotTable or adding a helper column that classifies records into categories the PivotTable can summarize.
Dates, Top 10, and report filters
Date fields can offer date-specific filters, including choices for periods or date ranges. The available options depend on how Excel recognizes and groups the source dates. If dates are stored as text rather than real Excel dates, date filtering and grouping may not work as expected. Correct the source values, refresh the PivotTable, and check whether the field now behaves as a date field.
A Top 10 filter is a value-based way to focus on leading or trailing items. Its dialog can let you choose Top or Bottom, a number of items or percentage, and the value field used for ranking. For example, selecting the top five products by Sum of Sales ranks products by their summarized sales, not by the order in which their names appear. If several products tie near the cutoff, the visible count or result may require closer inspection rather than assuming the filter is a simple alphabetical selection.
A report filter works at a different level. Drag a field such as Year into the PivotTable Fields pane’s Filters area, then use the filter above the PivotTable to select the year or years to display. This is often a clearer control for readers who need to switch the whole report between departments or periods. It does not replace a custom rule such as “show years after 2022”; use a date or label filter when the condition itself matters.
Combine filters and understand refresh behavior
You can filter more than one PivotTable field at a time. For example, a report can use a label filter to show product names containing wireless and a value filter to keep regions whose sales exceed a threshold. The PivotTable then displays the intersection of the active restrictions: an item must survive the relevant filters to appear in the report.
When the underlying source data changes, use PivotTable Analyze > Refresh to update the PivotTable. If the source range has grown beyond the original range, a refresh alone may not include the new rows; check the PivotTable’s data source and expand it, or use an Excel Table as the source so added table rows can be included when the PivotTable is refreshed. A filter cannot show a new category that is absent from the PivotTable’s current source.
Filters may also affect other users’ interpretation of a report. A hidden category is not necessarily zero, missing, or absent from the source; it may simply be filtered out. Before sharing a PivotTable, make the active filter state visible or explain it in a nearby note. Clearing a filter restores the available items, though manually cleared checkboxes and custom criteria may need to be reset in the field menu.
Custom filters compared with slicers
Custom filters are compact and precise: they are well suited to criteria such as contains, greater than, or between. A slicer is a visual control made of clickable buttons for a field’s items. In Excel for Microsoft 365 on Windows, select the PivotTable and use PivotTable Analyze > Insert Slicer, then select the field. Slicers are useful when report readers need to switch among a manageable set of categories without opening drop-down menus.
A slicer generally selects listed items; it does not replace every custom-filter condition. If the goal is to show names containing a changing text pattern or totals above a numeric threshold, a custom filter is usually more direct. A slicer is often better for repeatedly choosing values such as Region, Year, or Channel. A Timeline is a separate visual control for filtering a date field when available. Excel for Mac and Excel for the web may present PivotTable commands differently, and some capabilities can vary by build; use the PivotTable field menu if the ribbon layout differs.
Common problems and fixes
- The filter option is missing: Confirm you opened the arrow for a row or column field and that the field type supports the condition. A Values area heading is not necessarily the field whose item labels you intend to filter.
- The filter returns no items: Check spelling, spaces, the selected condition, and whether And was chosen where Or was intended. For a value filter, verify the summarized measure and the threshold.
- The totals seem wrong: Remember that a value filter evaluates aggregated PivotTable values. Also check whether another field, report filter, or slicer is already restricting the data.
- New labels do not appear: Refresh the PivotTable and check whether the source range includes the new records. A refresh cannot add data outside the selected source.
- Dates act like text: Inspect the source column for text-formatted dates, blanks, or inconsistent values. Correct the source and refresh before relying on date filters.
For troubleshooting, clear one filter at a time rather than resetting the entire report immediately. That helps identify which condition is responsible and preserves other useful selections. If a field’s filter menu shows a funnel or other active-filter indicator, use it as a reminder that the displayed categories may be incomplete.
Frequently Asked Questions
How do I filter a PivotTable by text? Open the drop-down for the text field, choose Label Filters, select a condition such as Contains or Begins With, enter the text, and confirm. Use manual checkboxes instead when you want to select specific known items.
How do I filter a PivotTable by total or sum? Open the drop-down for the category field whose items you want to keep, choose Value Filters, select a condition, and choose the summarized measure, such as Sum of Sales. The condition is evaluated for each category’s PivotTable result.
Can I use two conditions in a PivotTable filter? Some custom-filter dialogs support two criteria combined with And or Or. And requires both conditions to be true; Or accepts either. The available choices depend on the field and filter type.
Does a PivotTable filter delete rows from my data? No. It changes which items the PivotTable displays; it does not delete matching or nonmatching source records. Edit the source data separately if records must actually be removed.
Why is my PivotTable filter not showing new data? Refresh the PivotTable and confirm that its data source includes the new rows. If the source is a fixed range, extend it or change the source; using an Excel Table can make it easier for added rows to be included when refreshed.
Can I filter a PivotTable by multiple fields? Yes. Apply filters to separate row, column, or report fields as needed. The displayed result reflects all active restrictions, so check each field’s filter when categories appear to be missing.
Related
- Excel Next Tab Shortcut: Move to the Next Worksheet
- Excel Keyboard Shortcut to Highlight a Cell with Color
- Excel Keyboard Shortcuts Mouse Pad: Guide to Choosing
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table Fields in Filters and Rows ExplainedIn Excel for Microsoft 365 on Windows, a PivotTable field normally cannot occupy the Filters and Rows areas…
- Excel Pivot Table: Filter Dates by YearTo filter an Excel PivotTable by year, group its date field: right-click any date in the PivotTable, choose…
- How to Make a Dashboard in ExcelTo make a dashboard in Excel, organize your data in a clean Excel Table, summarize it with PivotTables,…
- Excel Dashboard Course: What to Learn and How to ChooseAn Excel dashboard course teaches you to turn spreadsheet data into a clear, interactive summary that helps people…
- How to Make a Waterfall Chart in ExcelTo make a waterfall chart in Excel, arrange your categories and positive or negative changes in two columns,…
- Excel Budget Chart: Build and Read ItAn Excel budget chart compares planned spending with actual spending so you can see where money is on…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.