How to Limit Values Shown in an Excel Slicer
Question details
The user needs to hide or exclude specific unique values, such as specific years or categories, from appearing as selectable options in an Excel slicer.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating an interactive data dashboard or PivotTable where certain source items should not be visible or selectable in the slicer menu.
- Observed behavior
- Excel slicers automatically display all unique values present in the selected source column, lacking a direct built-in option to hide specific active values without removing them from the dataset.
Before attempting to limit slicer values, verify which specific data points you want to exclude and ensure your source data is properly formatted as an Excel Table to prevent calculation errors.
Use Power Query to Pre-Filter the Slicer Source Data
Since slicers pull all unique values directly from the source column, filtering out unwanted data using Power Query before generating the PivotTable is the most reliable workaround.
Power Query allows you to create a filtered connection to your original dataset. By removing the unwanted items at the query level, the resulting PivotTable and its slicer will never see or display those values.
Select any cell in your source data table, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.
In the Power Query Editor, locate the column you intend to use for your slicer. Click the filter drop-down arrow in the column header and uncheck the specific values you want to hide.
Click 'Close & Load' from the Home tab, and choose 'Close & Load To...'. Select 'PivotTable Report' to load the filtered data directly into a new PivotTable.
Click inside your new PivotTable, go to the 'PivotTable Analyze' tab, and click 'Insert Slicer'. Choose your column. The excluded values will no longer appear.
Create a Helper Column to Filter Out Unwanted Values
By adding an extra column to your dataset, you can tag rows containing unwanted values and filter them out of the PivotTable before adding a slicer.
Simplify Data Visualization with WPS Office
If you are frustrated by complex workarounds for basic spreadsheet tasks, consider switching to WPS Office. It provides a highly compatible, lightweight, and completely free alternative for managing data, creating PivotTables, and designing dashboards.
- 1. Download and Install: Visit the official WPS website and download the free WPS Office suite for your operating system.
- 2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx file. Your PivotTables and Slicers will load perfectly.
- 3. Manage Data Easily: Use WPS's intuitive Data tab and PivotTable tools to filter, analyze, and visualize your datasets with ease.
Frequently Asked Questions
Why do deleted source data items still show up in my slicer?
Slicers pull data from the PivotTable cache, which retains old items by default even after source data is deleted. To fix this, right-click the PivotTable, choose 'PivotTable Options', go to the 'Data' tab, and change 'Number of items to retain per field' to 'None'. Then, refresh the PivotTable.
Can I simply hide items in a slicer that have no data?
Yes. Right-click the slicer and select 'Slicer Settings'. Check the box that says 'Hide items with no data' and click OK. This automatically hides empty categories rather than just graying them out.
Is it possible to use VBA to hide slicer items?
Yes, advanced users can write a VBA macro to loop through SlicerItems and set their 'Selected' property to False for specific values. However, the item will still appear in the list unselected unless you also filter out the underlying PivotTable data.
