Hide Blank Slicer Items and Fix PivotTable Display Issues in WPS Spreadsheets
Question details
The user needs to remove the Blank entry from a slicer, prevent a PivotTable from resizing after refresh, and hide PivotTable filter controls when the EnableItemSelection property triggers VBA error 1004.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with PivotTables and slicers created from source data that contains blank cells, and needs to control layout and filter display behavior.
- Observed behavior
- The slicer shows blank items, the PivotTable shrinks or changes size after refresh, and filter controls remain visible or VBA error 1004 occurs when using EnableItemSelection.
Open the workbook in WPS Spreadsheets and locate the source range used by the PivotTable. Check for empty cells, especially in date columns, because blank source values create the Blank slicer item and trigger display problems.
Remove Blank slicer items by cleaning the source data
Use this when a slicer still displays Blank even after you remove visible rows; empty cells in the source column are the cause.
The Blank item is not a slicer filter option you can delete directly. It appears when the source column contains empty cells, and it is especially problematic in date fields because blank values interfere with spreadsheet date grouping and timeline calculations.
Select the source column that feeds the slicer, press Ctrl+G, click Special, choose Blanks, and click OK.
Type a valid replacement value or date, press Ctrl+Enter to fill only the selected blanks, or right-click and delete the blank rows if those records are not needed.
Click the PivotTable, go to PivotTable Analyze, click Refresh, then select the slicer and confirm that the Blank item is gone.
Prevent a PivotTable from shrinking or resizing after refresh
Use this when column widths or PivotTable size change every time you refresh the report.
Hide PivotTable filter controls and avoid EnableItemSelection error 1004
Use this when field headers or filter arrows remain visible, or a VBA macro fails with error 1004 after setting EnableItemSelection.
Fix PivotTable Slicer, Layout, and Filter Display Issues in WPS Spreadsheets
WPS Spreadsheets gives you direct tools to clean slicer source data, lock PivotTable layout options, and hide field headers, so you can fix Blank slicer items, refresh resizing, and filter display problems without depending on Microsoft Excel.
- 1. Open the Excel workbook in WPS Spreadsheets: Launch WPS Spreadsheets and open the .xlsx workbook that contains the PivotTable and slicer.
- 2. Clean slicer source blanks: Select the source column, press Ctrl+G and choose Special > Blanks, then replace or remove empty values before refreshing.
- 3. Lock PivotTable layout: Right-click the PivotTable, choose PivotTable Options, and clear Autofit column widths on update.
- 4. Hide filter controls: Click the PivotTable, go to PivotTable Analyze, and turn off Field Headers.
- 5. Save as an Excel file: Click Menu > Save As and choose .xlsx to keep the cleaned PivotTable compatible with Microsoft Excel.
Frequently Asked Questions
Why can't I remove the Blank entry directly from a slicer?
Because Blank is generated from empty cells in the source data, not from the slicer itself. Select the source column with Ctrl+G > Special > Blanks, replace or delete the blanks, and refresh the PivotTable.
Where do I stop PivotTable columns from resizing after refresh?
Right-click the PivotTable, choose PivotTable Options, open the Layout & Format tab, and clear Autofit column widths on update. Click OK and refresh to confirm.
How do I hide the filter buttons on a WPS PivotTable?
Click the PivotTable, open the PivotTable Analyze tab, and click Field Headers in the Show group. This hides field names and filter arrows.
Why does EnableItemSelection return VBA error 1004?
EnableItemSelection is not supported for every PivotField. Check the field type before setting the property or use an error handler, and hide filter icons through Field Headers for a non-VBA solution.
Does cleaning blanks also fix date slicers?
Yes. Blank cells in date columns are the main cause of Blank in date slicers, so replace or remove them before using date filters or timelines.
