To filter a PivotTable to several values in Excel for Microsoft 365 on Windows, open the drop-down for the field, clear Select All, check each item you want, and select OK. For a field in the report-filter area, first turn on Select Multiple Items in its drop-down; a slicer is another convenient way to choose several items.
What selecting multiple values means
A PivotTable filter limits which source records contribute to the summary. When you select several items in one field—such as East and West in a Region field—the PivotTable includes records matching either selected item. In that sense, the choices within one field work like an OR condition: Region is East or West.
Filters on different fields work together. If you choose East and West for Region and also choose January for Month, the result includes records that are in either selected region and in January. Understanding that difference helps explain why a PivotTable can show fewer records than expected: each field’s filter narrows the result further.
Filtering does not delete or alter the source data. It changes what the PivotTable displays and summarizes. You can remove or change the filter later, and the underlying rows remain intact.
Filter a row or column field
Use this method when the field appears in the PivotTable’s Rows or Columns area. It works well for selecting a short list of categories, regions, products, or dates.
- Select the arrow beside the field name in the PivotTable. Depending on the report layout, the button may appear beside a specific field heading or beside Row Labels or Column Labels.
- In the item list, clear Select All. This removes the current checkmarks so you can start with a clean selection.
- Check every item you want included. For example, check East, Central, and West to include those three regions.
- Select OK to apply the filter.
If you want to exclude just one or two items from a short list, it can be quicker to leave Select All checked and clear only those unwanted items. For a long list where most items should be excluded, clearing all and selecting the smaller group is usually easier. The list may include a search box, which can help locate an item in a large field.
To restore the full list, reopen the field menu and select Clear Filter From followed by the field name, if that command is available in the menu. You can also select all items again and apply the selection.
Select several items in a report filter
A report filter sits above the PivotTable and usually displays a single field value at a time. It can still show records for multiple items, but its control behaves differently from a row- or column-field menu.
- Open the drop-down beside the report-filter field at the top of the PivotTable.
- Check Select Multiple Items in the menu.
- Clear Select All, check the values to include, and select OK.
For example, a report filter for Department can be set to show Sales and Support together. The filter control may display a multiple-selection indicator rather than one department name, so check the selected items in the drop-down if you need to confirm the exact selection.
If you do not see Select Multiple Items, confirm that you opened the drop-down for a report-filter field rather than a row or column field. Those other field menus already use checkboxes for multiple choices and do not need the same option.
Use a slicer to select multiple values
A slicer presents field items as clickable buttons. It is useful when a report will be used repeatedly, when readers need to see the active choices, or when dropdown lists are cumbersome. For example, a Region slicer makes East, Central, and West visible as buttons beside the PivotTable.
In Excel for Microsoft 365 on Windows, select a cell in the PivotTable, then choose PivotTable Analyze > Insert Slicer. Choose the field or fields to use and select OK. Click a slicer button to filter by that item. To keep more than one item selected, use the slicer’s Multi-Select control, or use the selection behavior available in the slicer; the control lets you add several buttons without replacing the existing choice. The clear-filter icon in the slicer header removes that slicer’s selection.
Slicers can make the current filter easier to understand, but they take up worksheet space. They also filter only the PivotTables connected to them. If several PivotTables share the same PivotTable cache or compatible data connection, select the slicer and use Slicer > Report Connections (called PivotTable Connections in some Excel builds) to connect it to other reports. The list contains only PivotTables the slicer can control.
On Excel for Mac, slicer creation and ribbon labels can vary somewhat by release; select the PivotTable and look for the slicer command on the PivotTable ribbon. Slicers are not the same as ordinary worksheet filters, and a slicer connected to a PivotTable does not automatically filter a separate, unrelated table.
Combine manual selections with label and value filters
Checking several items is a manual item filter: you choose specific entries from the field’s list. Excel also offers rules that identify items by their text or by the values summarized in the PivotTable. Open a row or column field menu and look for Label Filters or Value Filters.
- Label Filters select items based on text or dates, such as labels that begin with a particular letter or dates within a period. The available choices depend on the field’s data type.
- Value Filters select items based on summarized numbers, such as products with total sales greater than a chosen amount. Choose the value field to evaluate if the PivotTable summarizes more than one measure.
- Manual selection is best when you already know the particular items you want, regardless of their label pattern or totals.
These approaches answer different questions. To show three named products, check those products manually. To show every product whose sales exceed a threshold, use a value filter instead of selecting names one by one. A filter on a second field can narrow the resulting report further; for example, a Region selection combined with a product value filter restricts the summary to the selected regions and products that meet the rule.
Choose the right filtering method
For a one-time report with a handful of categories, the field drop-down is usually fastest. Use a report filter when the field belongs above the PivotTable and you want to keep the layout compact. Use a slicer when people need a visible, interactive control or when the report will be revisited.
Use rule-based filters when the selection should follow a condition rather than a fixed list. A manual selection of current products does not express the same intent as a rule such as “show products over $10,000 in sales.” If the data changes, a rule can include items that meet its condition, while a manually chosen list may need review.
For filtering by dates, consider whether a date field is grouped into years, quarters, or months. A grouped date hierarchy may have separate fields or levels, so filter the level that matches the question. A timeline can also provide a visual date-range control when the PivotTable has a suitable date field; it is not a general-purpose selector for text categories.
Common problems and how to fix them
- Only one report-filter value can be chosen. Reopen its menu and enable Select Multiple Items before checking several entries.
- The PivotTable shows no results. Check whether every item was cleared, whether a second field has a restrictive filter, and whether the chosen combinations exist in the source data. Clear filters one at a time to identify the cause.
- A new source item is missing. Refresh the PivotTable and confirm that its source range or table includes the new row. A manually restricted item list may also need updating after the source gains new categories.
- The list contains unexpected blanks or duplicates. Inspect the source field for blank cells, extra spaces, inconsistent spelling, or values stored differently. A PivotTable treats distinct source labels as separate items, so “North” and “North ” may not group together.
- The filter seems to affect another report. Check whether the PivotTable shares a slicer or report connection with other PivotTables. A connected slicer is designed to control every compatible report selected in its connections.
When source data changes, choose Data > Refresh All or right-click the PivotTable and choose Refresh. Refresh updates the PivotTable’s view of its source; it does not correct an incorrectly defined source range or standardize inconsistent labels. If a filter remains confusing, temporarily clear the relevant field’s filter and verify the available items before rebuilding the selection.
Frequently Asked Questions
How do I select all values except one?
Open the field drop-down, leave Select All checked, and clear the checkbox beside the value to exclude. Select OK. This is efficient when excluding only a few items; if excluding most of a long list, clear all and select the smaller group you want.
Can I filter multiple columns in a PivotTable?
Yes. Apply a filter to each relevant field. Selections within one field include any of the checked items, while filters on separate fields narrow the report together. A Region filter and a Month filter, for example, show records that match a selected region and a selected month.
Can I use a slicer and a dropdown filter at the same time?
Yes, but both selections apply to the same PivotTable. Their combined effect can narrow the results more than either filter alone. If items seem to disappear, clear one control temporarily to see which filter is restricting the list.
Why does my PivotTable filter show Select Multiple Items?
That option appears for a report-filter field so you can choose more than one item in the filter above the PivotTable. Turn it on before checking several values. Row and column field menus use item checkboxes directly.
Do PivotTable filters change the original data?
No. A PivotTable filter controls which source records appear in the summary; it does not remove rows from the source worksheet. To change the source itself, edit the source data separately and refresh the PivotTable.
Related
- Excel Number Format for Leading Zeros: A Practical Guide
- Excel Number Format: How to Show Zero as Blank Cells
- Excel Number Format: Red Negative Numbers in Brackets
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table: Group Dates by MonthTo group dates by month in an Excel PivotTable in Microsoft 365 for Windows, put the date field…
- Combo Chart Icon in Excel: Where to Find and Use ItIn Excel for Microsoft 365 on Windows, find the combo chart icon on the Insert tab, in the…
- Excel Payment Chart: Build a Loan Schedule and GraphAn Excel payment chart is usually a loan amortization schedule paired with a graph showing how payments are…
- Access Power Pivot Add-In: Use Access Data in ExcelYou cannot add Power Pivot to Microsoft Access: Power Pivot is an Excel add-in, not an Access feature.…
- Excel Pivot Table Tabular View: Setup and FormattingTo display an Excel PivotTable in tabular view, click inside the PivotTable and choose PivotTable Design > Report…
- Excel Pie Chart: Show Values, Percentages, or BothYes. In Excel for Microsoft 365 on Windows, select the pie chart, then choose Chart Design > Add…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.