Filter Excel Using a PivotTable: Practical Steps and Tips

Formulas & functions · Updated November 20, 2026

To filter Excel data with a PivotTable, click inside the PivotTable, open the PivotTable Fields pane, and drag the field you want to filter into the Filters area. Use the drop-down that appears above the PivotTable to choose which items to show. You can also filter row or column labels directly, or add slicers for clickable, visual filtering.

How PivotTable filtering works

A PivotTable summarizes source data into categories and calculated values. Filtering changes which source records are included in that summary; it does not delete or edit the underlying records. For example, a sales PivotTable might show revenue by salesperson and month. Filtering the Region field to West makes the totals reflect only records assigned to that region.

There are several places to apply a filter, and they suit different questions:

  • Report filters apply a selection to the whole PivotTable and appear above it.
  • Row and column filters show or hide categories in the PivotTable’s row or column labels.
  • Value filters keep categories according to their summarized values, such as the ten products with the highest sales.
  • Slicers and timelines provide visible buttons for filtering fields and dates.

These controls affect the PivotTable view, not the source worksheet. If you need to permanently remove records, edit or filter the source data separately. If you need a filtered copy of rows rather than a summary, Excel’s regular table filters or the FILTER function may be more appropriate.

Prepare the source data first

Good source data makes PivotTable filters more predictable. Arrange the records in a simple rectangle: one header row, one record per row, and one kind of information per column. Avoid blank header cells, merged cells, subtotals inside the data, and completely blank rows or columns in the middle of the range.

For example, a sales list could have columns named Date, Region, Salesperson, Product, and Amount. Each row should represent one sale. If some dates are stored as text while others are actual Excel dates, date grouping and timeline filtering may not behave as expected. Likewise, inconsistent entries such as North, N., and Northern Region are treated as different Region items.

For a source range that will grow, convert it to an Excel table before creating the PivotTable. Select a cell in the data and press Ctrl+T on Windows, check that the range is correct, and confirm that the table has headers. On Mac, use Command+T. A table usually expands when you add records beneath it, making it easier to include new rows when you refresh the PivotTable.

Create a PivotTable and add a filter

  1. Select a cell in the source data or Excel table.
  2. Choose Insert > PivotTable. Check the selected table or range, choose where to place the PivotTable, and select OK. In Excel for Microsoft 365 on Windows, the dialog also offers options such as creating the PivotTable on a new or existing worksheet.
  3. In the PivotTable Fields pane, drag fields into Rows, Columns, Values, and Filters as needed. For a simple example, place Salesperson in Rows and Amount in Values.
  4. Drag Region into Filters. A Region drop-down appears above the PivotTable.
  5. Open that drop-down, clear Select All if necessary, select West, and choose OK. The summary now shows the selected region.

If the field list is not visible, click the PivotTable and choose PivotTable Analyze > Field List on Windows. The contextual PivotTable Analyze tab appears when a PivotTable is selected; its exact placement can differ between Windows, Mac, and Excel for the web. If your ribbon is simplified, look for the PivotTable tab or expand the ribbon to find the field-list control.

A report filter is useful when the whole report should focus on one or a few items, such as a single region, department, or year. It is less convenient when readers need to compare many selections repeatedly, because the filter is located in a drop-down rather than displayed as a set of buttons.

Filter row and column labels

When a field is in the Rows or Columns area, its label drop-down lets you filter the categories displayed in that part of the PivotTable. Open the arrow beside a row or column label, then select or clear individual items. For instance, if Product is in Rows, you can show just the products in a chosen category—provided the PivotTable includes a field that identifies that category, or the source data already groups products appropriately.

Label Filters help when a list is long. Depending on the field and Excel version, the menu can include conditions such as Begins With, Contains, or Equals. To show product names containing the word cable, open the Product label drop-down, choose Label Filters > Contains, enter cable, and confirm. This filters category names, not the sales totals.

To filter based on summarized numbers instead, use Value Filters from the row or column field menu. For example, a report with Product in Rows and Sum of Amount in Values can use a value filter to keep products whose sum of Amount is greater than a threshold. The comparison is against the aggregated value for each product, not each individual sale. That distinction matters: a product may have many small transactions whose total passes the threshold even though no single sale does.

Use slicers and timelines

A slicer turns a field’s filter into a panel of buttons. Click inside the PivotTable, choose PivotTable Analyze > Insert Slicer, select fields such as Region or Product, and choose OK. Click a button to filter; use the slicer’s clear-filter control to remove that selection. Hold Ctrl on Windows while clicking to select multiple nonadjacent items. On Mac, use Command for multiple selections where supported by the current Excel interface.

Slicers make the current filter easier to see and are useful in dashboards shared with colleagues. They also show which items are selected, rather than hiding the selection inside a menu. You can move and resize a slicer, and use its slicer tab to change its appearance or arrange its buttons into columns. A slicer can control more than one PivotTable when those PivotTables share a compatible data source or PivotTable cache: select the slicer, open Slicer > Report Connections (the command may be labeled PivotTable Connections), and select the PivotTables to connect.

For a PivotTable containing dates, a timeline can provide a visual date-range control. Select the PivotTable and choose PivotTable Analyze > Insert Timeline, then select a date field. Use the timeline’s controls to switch its displayed unit, such as months or years, and drag across the period to filter. The timeline requires a date field Excel recognizes as dates; text-formatted dates may need to be corrected in the source data first.

Combine filters and clear selections

You can apply more than one filter at a time. For example, use a report filter to choose West, a Product label filter to show names containing cable, and a value filter to retain products with sufficient total sales. The resulting summary must satisfy all active filters. If the outcome seems unexpectedly small, check every report filter, row and column filter, slicer, and timeline—not only the control you last changed.

To remove a selection, open its filter menu and choose the option to clear the filter, or use the clear-filter icon on a slicer or timeline. To clear filters from the PivotTable, click inside it and choose PivotTable Analyze > Clear > Clear Filters on Windows. Clearing filters does not remove the PivotTable layout or its fields; it restores the items hidden by filters. The exact ribbon labels can vary slightly across platforms and updates.

Refresh after changing the source

A PivotTable does not always update immediately when you edit its source records. After correcting values or adding new data, click inside the PivotTable and choose PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh. To refresh PivotTables in the workbook together, use Data > Refresh All. Mac and web layouts differ, but the Refresh or Refresh All command is available in current Excel versions.

If you added rows outside the original source range and those rows do not appear after refreshing, check the PivotTable’s source. Choose PivotTable Analyze > Change Data Source and adjust the range, or use an Excel table as the source so new table rows are included when refreshed. Refreshing updates the summary from its source; it does not necessarily repair inconsistent labels or convert text into dates. Fix those issues in the source and refresh again.

Common filtering problems

  • A field is missing from the filter menu: Confirm that it is in the PivotTable layout and that the source header is present and unique. Refresh after changing the source structure.
  • New items do not appear: Check that the source range includes the new records, then refresh. A table-based source can help with expanding data.
  • Similar items appear separately: Look for spelling differences, extra spaces, or inconsistent data types in the source, such as numbers stored as text.
  • Date filters or timelines are unavailable: Check that the source field contains real Excel dates rather than text or mixed values, then refresh the PivotTable.
  • A filter gives a surprising total: Review all active filters and confirm whether you used a label condition or a value condition. Label filters test names; value filters test summarized results.
  • The filter arrow is not visible: Click inside the PivotTable and inspect its field buttons or layout. If a field is in the Filters area, its report-filter control is above the PivotTable, not beside each row label.

PivotTable filters versus other Excel filters

Use a PivotTable when you want to summarize records and interactively compare categories or totals. Its filters change which records contribute to a summary, and its field layout lets you rearrange the analysis. Use ordinary table filters when you want to hide rows in a detailed list while keeping the original records visible. Those filters are applied from the header arrows of an Excel table or range.

For a formula-driven list of matching records, Excel for Microsoft 365 and Excel 2021 also support the FILTER function. It returns matching rows into worksheet cells rather than creating a PivotTable summary. That can be useful for a live extract, but it does not provide the same drag-and-drop arrangement of rows, columns, and summarized values. Choose based on the result you need: a grouped summary, a filtered view of the original list, or a formula-generated set of matching rows.

Frequently Asked Questions

Can I filter a PivotTable by more than one item?
Yes. Open the relevant filter menu, clear Select All if needed, and select multiple items. Slicers also support multiple selections; use their multi-select control or the platform’s modifier key when selecting separate buttons.

Can I filter a PivotTable by a number greater than a value?
Yes, use a Value Filter on a row or column field. Choose a condition such as Greater Than and enter the threshold. The test applies to the field’s summarized value, such as total sales per product, rather than to each underlying transaction.

Why does my PivotTable filter not include newly added data?
The new records may be outside the PivotTable’s source range, or the PivotTable may not have been refreshed. Check Change Data Source, include the added rows, and refresh. Using an Excel table as the source helps it expand as records are added.

Can a slicer filter two PivotTables at once?
Often, yes, if both PivotTables use a compatible source or cache. Select the slicer and use Report Connections or PivotTable Connections to choose which reports it controls. If a PivotTable is not listed, its source or cache may not be compatible.

Does filtering a PivotTable delete hidden records?
No. A PivotTable filter hides items from the summary; it does not delete source rows. To remove records permanently, edit the source data itself and keep a backup if the changes matter.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.