Pivot Table Issues

How to Filter PivotTable Fields Based on Another Filter

Amos Gikunda Oct 1, 2026 868 views

Question details

The user needs to configure a PivotTable so that selecting an item in one filter automatically narrows down the available options in a second filter.

How to Filter PivotTable Fields Based on Another Filter

Product: Spreadsheet
Device & OS: not provided
Scenario: Trying to create dependent filters in a PivotTable report, such as filtering a Staff Member list based on a previously selected Location.
Observed behavior: Standard PivotTable report filters continue to display all available items, ignoring selections made in other fields.

Before you start

Ensure your PivotTable is already set up and that your dataset contains hierarchical or related categories (like Location and Staff Member). Note that this method requires Slicers, which are best utilized on desktop spreadsheet applications.

Solution 1Recommended

Use Connected Slicers for Dependent Filtering

Replace standard report filters with connected Slicers to automatically filter available items based on your previous selections.

Standard PivotTable filters do not natively support dependent filtering. By using Slicers, you can visually connect fields so that picking an item in one Slicer restricts the options displayed in another.

Activate the PivotTable tools

Click anywhere inside your existing PivotTable to display the PivotTable Analyze tab on your top ribbon.

Insert Slicers

Click on 'Insert Slicer' in the Filter group. A dialog box will appear listing all your PivotTable fields.

Select the connected fields

Check the boxes for the fields you want to connect (e.g., Location and Staff Member) and click 'OK'.

Configure Slicer settings to hide empty items

Right-click the dependent Slicer (e.g., Staff Member), select 'Slicer Settings', and check the box for 'Hide items with no data'. Click 'OK'.

Test the dependent filter

Select an item in your primary Slicer (Location). The secondary Slicer (Staff Member) will now automatically update to show only the relevant staff members for that location.

Use Connected Slicers for Dependent Filtering

Mobile Device Limitation: Slicers may not be available or fully functional on mobile versions of spreadsheet apps. This solution should be performed on a desktop version.

Manage PivotTables Effortlessly

Easily Create and Filter PivotTables in WPS Spreadsheet

WPS Spreadsheet fully supports advanced PivotTable features, including inserting Slicers for dynamic and dependent data filtering. Enjoy a smooth data analysis experience with an interface you already know.

1. Insert a PivotTable: Open your dataset in WPS Spreadsheet, go to the 'Insert' tab, and click 'PivotTable'.
2. Add Slicers: Click your generated PivotTable, navigate to the 'Analyze' tab, and choose 'Insert Slicer'.
3. Apply Dependent Filters: Select your desired fields, right-click the slicers to hide items with no data, and click to filter your data dynamically.

Fully compatible with Microsoft Excel (.xlsx) PivotTables and Slicer formats.Intuitive interface for inserting and managing advanced data filters.Free, lightweight, and fast performance for handling large datasets.Seamless transition from other Office suites with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why do standard PivotTable filters show all items instead of filtering them?

Standard report filters in PivotTables operate independently by default. They are designed to filter the main data set rather than interacting with each other to hide unavailable sub-items.

Can I create dependent PivotTable filters on Excel mobile?

Usually, no. Connected Slicers, which are required for dependent filtering, are often unavailable or lack full functionality on mobile versions of spreadsheet applications. You will need to use a desktop client for this feature.

How do I hide items with no data in a PivotTable Slicer?

Right-click the Slicer and select 'Slicer Settings'. Look for the option labeled 'Hide items with no data' and check the box. This ensures only relevant choices appear based on your other filter selections.

Excel & Data Problems