Excel Pivot Table: Show Only Filtered Data in Your Report?

Charts & pivot tables · Updated November 18, 2026

Yes. In Excel for Microsoft 365 on Windows, filter a PivotTable field using its dropdown, or add a field to the Filters area to show only records that meet your chosen criteria. One important catch: filtering the original worksheet rows does not, by itself, make a PivotTable include only those visible rows.

What a PivotTable filter actually changes

A PivotTable summarizes its source data. When you filter a field in the PivotTable, Excel changes which field items are included in the displayed summary. For example, a sales PivotTable can show only the East region, or only orders marked Completed. The source records remain in the worksheet; filtering the PivotTable does not delete or rewrite them.

There are two different operations that people often mean by “show only filtered data”:

  • Filter the PivotTable: Choose which categories or values appear in the summary. This is usually the quickest option.
  • Build the PivotTable from a restricted set of source rows: Exclude records before they reach the PivotTable. This is necessary when you need the underlying summary itself to be based only on a preselected subset.

Understanding that distinction prevents a common surprise: a worksheet may display only a few rows after an AutoFilter is applied, while a PivotTable built from that range still summarizes all of its source records.

Filter a field in the PivotTable

For a quick filter, click the dropdown beside a row or column field in the PivotTable. Clear Select All, select the items to keep, and click OK. For instance, if Region is in the Rows area, select East and West to hide the other regions from the summary.

  1. Click a cell inside the PivotTable.
  2. Open the dropdown beside the field heading you want to filter.
  3. Use the search box or the checkboxes to find and select the items to display.
  4. Click OK to apply the selection.

The exact heading depends on the PivotTable layout. A field placed in Rows may appear under a heading such as Row Labels; a field in Columns has a column heading. If the dropdowns are not visible, click inside the PivotTable and look for the field buttons or filter arrows. The options available can vary with the report layout and Excel build.

To restore all items, open the same dropdown and select Select All. You can also clear a field’s filter from its dropdown when that command is available. Clearing a PivotTable filter does not remove filters on the source worksheet, and clearing a worksheet filter does not necessarily clear the PivotTable filter.

Use the Filters area for a report-level choice

A report filter appears above the PivotTable and lets a reader choose which item to summarize without changing the row-and-column structure. For example, put Region in Filters, Product in Rows, and Sales in Values. The report filter can then switch the summary between regions while the product list stays in place.

To add one, click the PivotTable and open the PivotTable Fields pane if it is not already visible. Drag the desired field into the Filters area. In Excel for Microsoft 365 on Windows, you can show the pane from PivotTable Analyze > Field List. Click the report-filter dropdown above the PivotTable and choose the item to display. Depending on the field and filter settings, you may be able to select more than one item.

A report filter is useful when someone needs a simple control at the top of a report. It is less convenient when users need to compare many selections at once, because they may have to change the selection repeatedly. A slicer is often easier for interactive reports.

Choose label, value, or date filters

Not every filter works the same way. A label filter keeps or excludes items based on their names, while a value filter evaluates the summarized values. If Product is in Rows, a label filter can keep names beginning with “A.” A value filter can instead keep products whose summed sales exceed a threshold. The latter is based on the PivotTable’s calculation, so check which value field and summary calculation Excel is using.

For dates, a PivotTable can offer date-oriented filtering options, and dates may also be grouped into periods such as months or quarters. A Timeline provides a more visual way to filter date fields. To add one, click the PivotTable and use PivotTable Analyze > Insert Timeline, then choose a date field. The command is available when the PivotTable has a suitable date field; it will not turn ordinary text that merely looks like a date into genuine date values.

Use a label filter when the category name is the criterion, a value filter when the result of the aggregation is the criterion, and a date filter or Timeline when the reporting period is the criterion. These choices answer different questions, so a value filter should not be used as a substitute for filtering the underlying records when the requirement is to exclude individual transactions.

Add slicers for visible, interactive filtering

A slicer is a panel of clickable buttons for a PivotTable field. It makes the current selection easy to see and is usually more approachable than a dropdown, especially in a dashboard. Click inside the PivotTable, choose PivotTable Analyze > Insert Slicer, select one or more fields, and click OK. Then click a slicer button to filter the report. Use the slicer’s clear-filter button to return to all items.

A slicer can control more than one PivotTable when those PivotTables share a compatible data source or PivotCache. Select the slicer and use Slicer > Report Connections (the tab or command wording can vary slightly by build) to choose connected reports. If another PivotTable is missing from the list, it may not share the same cache or data model; recreating it from the same source may be needed. Excel for Mac has PivotTable and slicer features too, though ribbon placement and available commands can differ by version.

Why filtering the source worksheet may not work

An AutoFilter on the source range controls which worksheet rows are visible. A regular PivotTable, however, reads its source range or table and maintains a PivotCache, which can contain records that are currently hidden by the worksheet filter. Consequently, hiding rows in the source and then refreshing does not generally mean “summarize only the visible rows.” Refreshing updates the PivotTable from its source; it does not convert a worksheet’s temporary visibility state into a source-data rule.

If the PivotTable must include only qualifying records, make the condition part of the data supplied to it. Practical approaches include:

  • Add an inclusion field: Add a column to the source table such as Include, with values like Yes and No. Add that field to the PivotTable’s Filters area or a slicer, then select Yes. This keeps the decision visible and easy to change.
  • Filter during data preparation: Use Power Query to filter the records and load the resulting table, then create a PivotTable from that output. When the source changes, refresh the query and PivotTable as appropriate.
  • Create a separate source range: Copy or otherwise prepare only the qualifying records in a clearly identified table, and build the PivotTable from that table. Maintain the process when new records arrive.

Choose the inclusion-field approach when users may need to switch between included and excluded records. Choose a prepared, filtered source when the PivotTable should never contain records that fail the rule. In either case, document the rule so someone reviewing the workbook can understand why certain transactions are absent.

Refresh behavior and common filter problems

PivotTable filters can appear to stop working when the source changes, a new category is added, or a report is refreshed. Click in the PivotTable and choose PivotTable Analyze > Refresh. To refresh workbook connections as well, use Data > Refresh All. Refreshing updates the PivotTable from its defined source, but it does not always expand a fixed source range to include new rows. Converting a source list to an Excel Table before building the PivotTable helps the source range expand as rows are added.

Other common causes are a filter left on another field, selecting a category that has no records under the current combination of filters, or using a value filter that is evaluated against a different value field than expected. Check each field’s dropdown and review the PivotTable Fields areas. If the filter list does not show a newly added category, confirm that the row is inside the source table or range, then refresh.

Also distinguish a filtered-out item from a blank or zero result. A category can remain visible with an empty-looking value if the source contains blanks or if the report’s layout displays items without data. Conversely, an item may disappear because another field’s filter leaves it with no matching records. Review the filter selections and the source data before concluding that records have been deleted.

Pick the right method for the report

For a one-time question, a field dropdown is usually fastest. For a report that readers will reuse, a report filter provides a compact control, while slicers make available choices more visible. For periods, a Timeline can make date selection clearer. If the rule determines which source records are allowed into the summary, filter or flag the source records as part of data preparation instead.

For example, imagine a transaction table with Date, Region, Product, Status, and Amount. To summarize completed orders by product for the East region, filter Status to Completed and Region to East in the PivotTable, then place Product in Rows and Amount in Values. If the requirement instead says the PivotTable’s source must contain no pending orders, create a source that excludes them or use an inclusion field and filter it to Completed. The first setup changes what the report displays; the second makes the inclusion rule explicit in the data feeding the report.

Frequently Asked Questions

Can an Excel PivotTable show only visible rows from a filtered source?
A regular PivotTable generally summarizes its defined source records, including rows hidden by a worksheet AutoFilter. Filter a field inside the PivotTable or prepare a source that excludes the unwanted records.

How do I show only one category in a PivotTable?
Open the dropdown for the category field, clear Select All, select the category to keep, and click OK. You can also put that field in the Filters area and choose the category above the report.

Does filtering a PivotTable delete the hidden data?
No. A PivotTable filter changes the summary’s displayed items, not the underlying worksheet records. The hidden categories can be shown again by changing or clearing the filter.

Why does my PivotTable still include rows hidden on the source sheet?
Worksheet AutoFilter controls row visibility, while the PivotTable uses its defined source and cache. To exclude those records, apply a PivotTable filter or create a filtered source for the PivotTable.

How do I make a PivotTable filter apply to multiple reports?
Use a slicer and check its Report Connections for compatible PivotTables. Reports that do not share a compatible source or PivotCache may not be available to connect.

Will refreshing clear my PivotTable filters?
Refresh updates the report from its source, and existing filter selections often remain, but new items or changed source structure can affect what is available. Check the selections after refreshing, especially when the source categories have changed.

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.