Excel PivotTables do not have a dedicated Invert Selection command for filters. To show everything except a few items, open the field’s filter list, select all items, and clear the checkboxes beside the items you want to exclude; if the list is already filtered, clear that field’s filter first.
What inverting a PivotTable filter means
A PivotTable filter controls which items from a source-data field appear in the summary. For example, if a Region field contains East, West, North, and South, selecting East and West includes those regions; an inverted selection would show North and South instead. Excel provides checkboxes for choosing items, but it does not offer a one-click command that automatically swaps every selected item with every unselected item.
This is different from reversing the sort order or changing a value calculation. Inverting a filter changes which categories or records the PivotTable displays. It does not alter the underlying source data, delete records, or reverse the numbers in the summary. The exact result depends on the field being filtered and on any other filters already applied.
The quickest method: select all, then exclude items
In Excel for Microsoft 365 on Windows, use the field’s dropdown in the PivotTable. The dropdown may appear beside a row or column field name, or beside Row Labels or Column Labels when the layout groups fields under those headings.
- Click the dropdown arrow for the field you want to filter.
- If the field has an existing filter, choose Clear Filter From [field name] so that the item list starts from a known state. This clears that field’s filter, not necessarily filters on other fields.
- In the item checklist, select Select All. All available items in that list should be checked.
- Clear the checkbox beside each item you want to leave out.
- Click OK to apply the selection.
For example, to display every product category except Accessories and Clearance, select all categories and uncheck those two. The PivotTable then summarizes the remaining categories. This is usually faster and less error-prone than checking each category you want to keep when most categories should be included.
If you need to clear every filter in the PivotTable rather than just one field, select a cell in the PivotTable and use PivotTable Analyze > Clear > Clear Filters. This removes the PivotTable’s filters, so use the field-specific clear option when other field selections must remain in place.
When the checklist is not the best option
The checklist approach works well when you need to exclude a small number of known items. For a single text value, Excel may offer a more direct rule: open the field dropdown, choose Label Filters, and select Does Not Equal. Enter the label to exclude and confirm. The PivotTable then displays labels that do not match that value.
Label Filters can also be useful for text patterns, such as labels that begin with a particular word. Available choices depend on the field and the PivotTable context. They are not the same as Value Filters: label filters test the item names, while value filters test summarized numbers. For instance, filtering out a region named West is a label operation; showing regions with total sales below a threshold is a value operation.
For several exclusions that follow a rule, consider adding a helper field to the source data. A column such as Status could mark each source row Include or Exclude, or identify a group such as Keep or Remove. Refresh the PivotTable, place Status in the Filters area or use it as a row field, and select the appropriate value. This makes the rule visible and easier to maintain than a long list of manually unchecked items.
How existing filters affect the result
PivotTable fields can affect one another. If another field is already filtered, Excel may show only the items available within that remaining data. As a result, selecting all in one field’s checklist may mean all currently available items, not every distinct item in the original source. Clear relevant filters before attempting a complete inversion, especially when fields are related or the PivotTable uses grouped or hierarchical data.
Also check whether the dropdown belongs to the field you intend to change. A PivotTable can contain separate fields in Rows, Columns, Filters, and Values. Filtering a field in the Filters area is not the same as filtering a similarly named field in Rows. If the layout uses Compact Form, several row fields may appear beneath a shared Row Labels heading; use the field dropdown for the correct level.
Another useful distinction is between filtering one item and filtering its underlying records. When a PivotTable hides a category, its source rows are still in the worksheet or data model. The filter changes the summary view. Removing the filter restores the category, provided it is still present in the source and the PivotTable has been refreshed when needed.
Using a slicer to select the items to keep
A slicer provides clickable buttons for filtering a PivotTable. To add one, select the PivotTable and choose PivotTable Analyze > Insert Slicer, then select the field and confirm. Click the slicer buttons for the items you want included. A slicer can make the current selection easier to see, particularly when a checklist is long or the report is used by other people.
Slicers do not add a special invert command. To start over, use the slicer’s Clear Filter button, then select the desired items. If you want to choose several items, use the slicer’s multi-select control when available, or use the applicable selection method shown in the slicer. Because an inverted selection can still involve many clicks, a helper field is often clearer when the exclusion rule changes regularly.
A slicer can also be connected to more than one PivotTable when the tables share a compatible data source. That can keep multiple summaries in sync, but check the slicer connections before relying on it: a slicer that is connected to only one PivotTable will not filter the others.
Common problems and practical checks
- The item list is shorter than expected: Clear filters on related fields and reopen the list. Other selections may limit which items are available.
- Select All does not produce the expected view: Confirm that you are filtering the intended field, and check for an active search in the dropdown. Clear the field filter and inspect the full list again.
- A category is missing after changing the source: Refresh the PivotTable. Select a PivotTable cell and choose PivotTable Analyze > Refresh, or use Data > Refresh All if you need to update multiple connections.
- New source rows do not appear: Check the PivotTable’s source range. Converting a growing source range to an Excel table can help it expand as rows are added, but the PivotTable still needs refreshing to show updated data.
- The selection seems to reset: Check whether you changed the source, refreshed the PivotTable, or cleared filters. Those actions can change the available items or the displayed selection.
In Excel for Mac, the core idea is the same: open the PivotTable field filter and choose the items to include. Ribbon labels and the placement of commands can differ slightly by Excel release. Excel for the web also supports PivotTable filtering, but some editing controls may be more limited than in the Windows desktop app; use desktop Excel if a specific filter option is not available in the browser.
Choosing the right approach
Use the checklist when the exclusions are few and you want a quick, one-time filter. Use Label Filters when the exclusion is a single value or a text rule. Use a helper field when the excluded items form a recurring business rule, when you need an auditable include/exclude flag, or when the list is too long to manage reliably by hand. Use a slicer when people need a visible, interactive control for exploring a report.
Before sharing the workbook, consider whether readers should see the filtered result or the full source-level context. A PivotTable filter can make a report look complete even though some categories are hidden. A descriptive field name, a visible slicer, or a note stating which items are excluded can prevent someone from mistaking a filtered total for an all-items total.
Frequently Asked Questions
Can I invert a PivotTable filter with one click?
No. Excel’s PivotTable filter checklist does not include a general Invert Selection command. Select all items and uncheck the ones to exclude, or use a label filter or helper field when that better matches the rule.
How do I exclude one item from a PivotTable?
Open that field’s dropdown and clear the checkbox beside the item. For a text field, Label Filters > Does Not Equal can also exclude a specific label.
Why does my PivotTable filter not show every item?
Another field’s filter may restrict the available items, or the source may not include those categories. Clear related filters and refresh the PivotTable after source-data changes.
Does filtering a PivotTable delete the hidden source rows?
No. It hides items from the PivotTable summary; it does not delete the original records. The source data remains unchanged unless you edit or remove it separately.
Can a slicer invert the selected items?
Not automatically. Clear the slicer filter and select the items you want to display. For a consistent set of exclusions, a helper field in the source data is often easier to manage.
Related
- Excel Number Format K: Show Values in Thousands
- Excel Number Format: Keep Leading Zeros in Your Cells
- Excel Number Format: Hide Zeros Without Deleting Them
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Sparklines: Meaning, Types, and How to Use ThemExcel sparklines are miniature charts placed inside individual worksheet cells to show a small set of data as…
- Clustered Stack Column Chart in Excel: How to Make OneExcel does not offer a chart type named Clustered Stacked Column, but you can create the same visual…
- Excel Bubble Chart Data Setup: Build a Clear ChartSet up an Excel bubble chart with three numeric columns for each data point: an X value, a…
- Line Balancing Chart in Excel: Build One Step by StepA line balancing chart in Excel compares the work assigned to each station with a target cycle time,…
- Filter in Power Pivot: Ways to Narrow PivotTable DataTo filter Power Pivot data, add the Data Model to a PivotTable and use its field drop-downs, report…
- Excel Stacked Column Chart for Multiple Groups ExplainedYes—Excel can show multiple groups in a stacked column chart by using one or more category columns for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.