Excel Pivot Table: How to Exclude Specific Values

Charts & pivot tables · Updated November 18, 2026

To exclude values from an Excel PivotTable, open the drop-down for the field containing them, clear the checkboxes beside the items you want to hide, and select OK. For a rule-based exclusion, use the field menu’s Label Filters or Value Filters; these filters change what the PivotTable displays, not the underlying source data.

The quickest way to hide selected items

In Excel for Microsoft 365 on Windows, click the drop-down arrow beside the row or column field in the PivotTable. In a compact layout, the menu may be labelled Row Labels or Column Labels rather than showing the field name. Clear the checkboxes for the items to exclude, then click OK. If the list is long, use the search box in the menu to find an item before clearing it.

  1. Click a cell in the PivotTable so its field menus are available.
  2. Open the drop-down for the field whose items you want to filter.
  3. Clear Select All if you want to start from an empty selection, or leave it selected and clear only the unwanted items.
  4. Click OK and check that the totals and displayed categories match your intention.

For example, a sales report might show East, West, North, and South. Clear the checkbox for South to exclude that region from the report. The PivotTable recalculates its displayed totals using the remaining regions. The source rows for South are still in the worksheet and can be shown again by reopening the menu and selecting South.

To reverse a filter, open the same menu and select all items, or use the field’s clear-filter command if it is available. A filter icon on the field button indicates that the field is filtered. If you cannot find the arrow, click inside the PivotTable and check that field headers are displayed; the PivotTable Analyze ribbon includes options for showing field headers.

Choose the right kind of filter

Excel offers several ways to exclude information, and the best choice depends on what “value” means in your report. A category such as a product name is a label. A number such as a sales total may be a value. A date may be grouped into years or months, and a field placed in the Filters area works differently from one in Rows or Columns.

  • Manual item selection: clear individual categories, such as one region or one product. This is the quickest choice for a short, known list.
  • Label Filters: include or exclude text items using a condition, such as items that do not equal a label or begin with particular characters.
  • Value Filters: include or exclude categories based on their summarized numbers, such as products with total sales equal to zero.
  • Report filters and slicers: provide a visible control for choosing which items appear, useful when someone will interact with the report.

These are PivotTable display filters. They are not the same as filtering or hiding rows in the source worksheet. A worksheet’s AutoFilter or hidden rows generally do not tell a standard PivotTable to ignore those records. Decide whether you only need a cleaner report or whether the records themselves must be left out of the analysis.

Exclude text or category labels

Manual selection is suitable when you know the exact category to omit. To create a rule instead, open the field drop-down and choose Label Filters. Select an available condition, such as Does Not Equal, Contains, or Begins With, enter the criterion, and confirm it. The exact choices depend on the type of field. A label rule is useful when the source contains many names that follow a consistent pattern, such as test products whose names begin with “Sample”.

Label filters apply to the items in that PivotTable field. They do not search every cell in the source workbook or remove matching records from other reports. If you exclude a customer label, for instance, the customer’s source rows remain in the data. Other PivotTables based on the same source may still include that customer unless you filter those reports separately.

When using manual selection, remember that the list represents field items, not individual source rows. If the label “East” appears on hundreds of records, clearing East excludes all of those records from this PivotTable’s calculations. If you need to remove only one transaction from East, filtering the region is too broad; use a transaction-level field or correct the source record instead.

Exclude categories based on their numbers

Use a Value Filter when the condition concerns the summary result for each category. Open the drop-down for the row field you want to filter, choose Value Filters, select a comparison such as Does Not Equal or Greater Than, and set the value field and threshold in the dialog. For instance, a product report could exclude products whose summed Units Sold equals zero.

The key distinction is that Excel compares the aggregated result for each item, not necessarily each underlying row. If the Value Filter is applied to Product and set to Sum of Sales greater than 1,000, Excel tests each product’s total sales in the current PivotTable context. It does not simply delete every source transaction worth 1,000 or less. Other active filters can change the totals being evaluated, so a product may qualify in one view and not another.

Check the chosen summary calculation before relying on the condition. A field may be summarized by Sum, Count, Average, or another available calculation. A filter on Sum of Sales is not equivalent to one on Count of Sales. If the comparison appears wrong, verify both the selected value field and its calculation in the dialog, then inspect the PivotTable’s Values area.

Use a report filter or slicer for interactive reports

If readers need to switch between categories, place the relevant field in the PivotTable Fields pane’s Filters area. The resulting report filter appears above the PivotTable and lets a reader choose an item or, where available, select multiple items. This keeps the report compact, but the control can be easy to overlook, so label the report clearly and make the current selection visible.

A slicer gives the same kind of filtering a more prominent button-based interface. Click the PivotTable, then choose PivotTable Analyze > Insert Slicer, select the field, and click OK. Click one slicer button to filter; use the slicer’s clear-filter button to restore all items. Slicers are especially useful on dashboards because the active selection is visible without opening a small drop-down menu.

If one slicer should control more than one PivotTable, select the slicer and use the Report Connections command, sometimes shown as PivotTable Connections, to choose compatible PivotTables. The PivotTables generally need to share the same underlying data source or data model. Confirm the connections rather than assuming a slicer affects every report on the sheet.

When the source records must be excluded

A PivotTable filter hides items from that particular summary; it does not erase, edit, or permanently remove source records. This is normally the safer choice for reporting because you can restore the items later. It is not sufficient, however, when excluded records must not be part of the analysis at all, or when the same exclusion must apply consistently to several outputs.

For a small, one-time cleanup, remove or correct the unwanted source rows, then refresh the PivotTable. Before deleting data, keep a copy of the original workbook or source table. For a repeatable process, add a clear status field such as Include in Report to the source table, mark eligible records, and filter the PivotTable to the included status. That makes the rule visible and easier to review than silently deleting records.

For recurring imports or more involved criteria, Power Query can create a cleaned dataset before the PivotTable is built. In Excel for Windows, use Data > From Table/Range to open the query editor, apply a filter to the relevant column, and load the resulting data for reporting. A query-based approach is helpful when the same exclusion must be reapplied each time new source data arrives. Keep the original source intact if you need an audit trail or may later change the rule.

Refresh behavior and common problems

After changing source data, refresh the PivotTable so its results reflect the updated records. Click inside it and choose PivotTable Analyze > Refresh, or use Data > Refresh All to update workbook connections and PivotTables. A filter can remain in place after a refresh, but check the item selection and totals whenever new categories or records have been added. A newly appearing item may not behave as you expect under an existing manual selection.

  • The excluded item returns: check whether a refresh introduced a new item or whether the filter was cleared. Reopen the field menu and confirm the selection.
  • The total still includes a hidden-looking record: confirm that you filtered the PivotTable field itself. Hiding a worksheet row or applying a sheet filter is not a reliable substitute for a PivotTable filter.
  • The wrong categories disappear: check which field’s drop-down you used. Similar fields, such as Region and Sales Region, can be easy to confuse.
  • A value condition gives an unexpected result: verify the aggregation, the threshold, and any other active filters that affect the category totals.
  • There is no drop-down arrow: click the PivotTable and check its field-header display options, or inspect the field placement in the PivotTable Fields pane.

In Excel for Mac, the same core options are available in current desktop versions, though ribbon labels and menu layout can differ slightly by release. Click inside the PivotTable and look for the PivotTable Analyze tab and the field’s drop-down. Excel for the web also supports common PivotTable filtering, but some advanced controls and connection features can differ from the Windows desktop app; use the desktop version if a command is unavailable.

Frequently Asked Questions

Does excluding a PivotTable value delete it from the source data?
No. It only filters the PivotTable display and calculation. The source record remains in the worksheet, and you can usually restore the item by changing or clearing the filter.

How do I exclude multiple items at once?
Open the field drop-down and clear each unwanted checkbox. For a large list, search for items in the menu. You can also clear Select All first and then select only the items you want to keep.

Can I exclude blank values from a PivotTable?
Yes. Open the relevant field’s drop-down and clear the checkbox for (blank), then click OK. If blanks keep returning, check the source column for empty cells or formulas that return an empty string, and refresh the PivotTable after correcting the data.

Can I exclude values below a certain sales amount?
Use a Value Filter on the category field and choose a comparison such as Greater Than or Greater Than Or Equal To, selecting the appropriate sales summary. This tests each category’s aggregated sales, not each individual transaction.

Why does my PivotTable still count rows hidden in the source sheet?
Hiding rows or filtering a worksheet table does not ordinarily remove those records from a standard PivotTable’s source. Filter the PivotTable field, change the source dataset, or create a filtered dataset with a repeatable method such as Power Query.

Can I exclude an item from every PivotTable in the workbook?
A regular field filter applies to the PivotTable where you set it. A slicer can control multiple compatible PivotTables when they are connected through Report Connections, but it does not automatically change unrelated PivotTables. For a workbook-wide rule, apply the exclusion in the shared source or query data.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.