Pivot Table Issues

How to Fix PivotTable Filters Showing Items Without Data in Excel

Muhammad Talha Sep 25, 2026 869 views

Question details

The user wants to know why PivotTable filter dropdowns continue to display items that have no associated data after other filters have been applied, and how to resolve this behavior.

How to Fix PivotTable Filters Showing Items Without Data

Product: Spreadsheet
Device & OS: not provided
Scenario: Filtering data within a PivotTable using multiple criteria.
Observed behavior: Filter dropdowns list items that no longer contain data due to previously applied filters, failing to behave like standard AutoFilter dropdowns.

Before you start

Keep in mind that this behavior is generally a default PivotTable design limitation rather than a data error, but it can be bypassed using interactive filtering tools.

Solution 1Recommended

Use Slicers for Dynamic PivotTable Filtering

Slicers provide a visual filtering method that dynamically updates to show which items have data based on your current selections.

Since standard PivotTable filter lists do not cascade or update based on other applied filters by design, slicers offer a much clearer and interactive alternative. They visually separate items with data from those without.

Select the PivotTable

Click any cell inside your existing PivotTable to activate the PivotTable tools.

Insert a Slicer

Go to the 'PivotTable Analyze' (or 'Options') tab on the top ribbon and click on 'Insert Slicer'.

Choose Filter Fields

Check the boxes for the fields you want to filter and click 'OK' to insert the slicer panels onto your worksheet.

Filter Dynamically

Use the newly inserted slicer panels to filter your data. Items with no data will be visually grayed out or sorted to the end.

Use Slicers for Dynamic PivotTable Filtering

Slicer Settings Tip: You can right-click a slicer, select 'Slicer Settings', and check 'Hide items with no data' for an even cleaner view.

Solution 2

Adjust PivotTable Options to Drop Deleted Items

If your dropdowns are showing old items that have been permanently deleted from the source data, you can adjust settings to clear the PivotTable cache.

Advanced Spreadsheet Features

Manage PivotTables and Slicers Easily with WPS Office

WPS Spreadsheet provides powerful PivotTable functionalities, including intuitive Slicers that help you filter data dynamically without the clutter of empty items.

1. Open Your Data: Launch WPS Spreadsheet and open your dataset or existing report.
2. Create a PivotTable: Navigate to the 'Insert' tab and click 'PivotTable' to generate your data summary.
3. Insert a Slicer: Select the PivotTable, go to the 'PivotTable Analyze' tab, and click 'Insert Slicer'.
4. Filter Data Effectively: Click on the slicer buttons to instantly filter out data items without values, keeping your reports clean.

Create and manage PivotTables with an intuitive, user-friendly interfaceInsert and customize Slicers for highly visual and dynamic data filteringFully compatible with Microsoft Excel (.xlsx) formats and PivotTable configurationsLightweight software with fast performance for analyzing large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Can I make PivotTable dropdown filters work like standard Excel AutoFilters?

By default, standard PivotTable filters do not cascade (hide items based on other applied filters) like standard AutoFilters do. To achieve cascading filters where empty items are visually managed, it is highly recommended to use Slicers.

Why do deleted source items still appear in my PivotTable filter list?

PivotTables store a cache of your data to improve performance, which means they can remember items even after they are removed from the source data. You can fix this by changing 'Number of items to retain per field' to 'None' in PivotTable Options and then refreshing.

Are Slicers available in WPS Office Spreadsheet?

Yes, WPS Spreadsheet fully supports inserting, customizing, and operating Slicers for PivotTables, ensuring seamless compatibility and a highly dynamic data filtering experience.

Excel & Data Problems