How to Filter a Long Excel Slicer with Another Slicer
Question details
The user wants to make a lengthy Excel slicer easier to navigate by using a secondary slicer to filter its contents or changing its display layout.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Filtering large datasets in a PivotTable where the default slicer generates a single, excessively long column of items, requiring tedious vertical scrolling.
- Observed behavior
- The generated slicer contains too many items in a single column making it difficult to find specific filter criteria efficiently.
Ensure your PivotTable is correctly set up and verify that the broader category field you intend to use as a primary filter exists in your source dataset.
Filter the Long Slicer Using a Category Slicer
Create a broader category slicer (like regions or alphabet letters) to narrow down the choices in your main, lengthy slicer automatically.
This method involves establishing a hierarchy. When you click an item in the parent category slicer, Excel automatically grays out or hides irrelevant items in the child slicer, making it much easier to find the exact item you need.
Click anywhere inside your PivotTable. Navigate to the 'PivotTable Analyze' tab on the ribbon, click 'Insert Slicer', and check the box for your broader category field (e.g., 'Region' or 'First Letter'). Click OK.
Repeat the previous step, but this time check the box for the field containing the long list of items. Click OK.
Right-click the secondary (long) slicer and select 'Slicer Settings'. Check the box that says 'Hide items with no data' and click OK. Now, when you select a category in the first slicer, the second slicer will shrink to show only relevant items.
Format Slicer Buttons into Multiple Columns
Change the layout of your slicer to display buttons across multiple columns, maximizing screen space and reducing the need to scroll vertically.
Manage PivotTables and Slicers Seamlessly in WPS Office
WPS Spreadsheet provides robust, easy-to-use data analysis tools. You can quickly insert, format, and connect multiple slicers to your PivotTables to filter massive datasets without lag.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
- 2. Insert PivotTable Slicers: Select your PivotTable, go to the 'Options' or 'Analyze' tab, and click 'Insert Slicer' to choose your data fields.
- 3. Customize and connect: Use the Slicer settings in the ribbon to increase columns, or right-click to connect your new slicers to multiple PivotTables instantly.
Frequently Asked Questions
How do I hide irrelevant items in my second slicer?
Right-click the second slicer, select 'Slicer Settings', and check 'Hide items with no data'. This ensures that when you filter using the first slicer, the second slicer removes the grayed-out buttons entirely.
Can I connect my new slicers to multiple PivotTables?
Yes. Right-click the slicer, choose 'Report Connections' (or 'PivotTable Connections'), and check the boxes next to all the PivotTables you want that specific slicer to control. Ensure all PivotTables share the same data source.
Why are some slicer buttons grayed out?
Buttons turn gray when they have been filtered out by another connected slicer, or when there is no underlying data for that item in the current PivotTable context.
Can I add a scroll bar to the worksheet instead of changing the slicer?
While it is possible to add Form Controls like a scroll bar via the Developer tab, it requires assigning macros or complex formulas to function as a data filter. Changing the slicer layout or using a category slicer is much faster and requires no coding.
