How to Fix Show Items with No Data Disabled in a WPS Spreadsheets PivotTable
Question details
The user needs to understand why the Show items with no data option is unavailable in a PivotTable and how to enable it for row or column fields.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is working with a PivotTable and wants to display row or column items that currently have no data.
- Observed behavior
- The Show items with no data option is disabled or greyed out in PivotTable Field Settings.
Select the PivotTable and confirm whether it was created from a standard worksheet range or from a Data Model or OLAP source.
Enable Show Items with No Data from PivotTable Field Settings in WPS Spreadsheets
Use this when the PivotTable uses a standard range and the field is placed in the Rows or Columns area.
The option is available only for row and column fields in a standard PivotTable. It does not apply to fields in the Values area or report filter.
Click any cell inside the PivotTable, then right-click the row or column field in the PivotTable Fields pane and choose Field Settings.
In the Field Settings dialog box, click the Layout & Print tab to view the layout options.
Check the 'Show items with no data' option under the layout options, then click OK to apply the setting.
Right-click the PivotTable and choose Refresh, or go to the PivotTable Analyze tab and click Refresh, to update the displayed items.
Check the PivotTable Source and Field Placement
Use this if the option remains disabled because the PivotTable uses a Data Model or OLAP source, or the field is in an unsupported area.
Enable Show Items with No Data in WPS Spreadsheets PivotTables
Use WPS Office Spreadsheets to open your workbook, create or edit a standard PivotTable, and turn on 'Show items with no data' for row or column fields from Field Settings. WPS keeps the familiar pivot layout and Excel-compatible workflow so you can fix the view without extra tools.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the .xlsx or .xls workbook that contains the PivotTable source data.
- 2. Create a standard PivotTable: Select the source range, click Insert > PivotTable, choose a new worksheet, and place the relevant field in the Rows or Columns area.
- 3. Open Field Settings: Right-click the row or column field in the PivotTable Fields pane and select Field Settings.
- 4. Enable the layout option: On the Layout & Print tab, check 'Show items with no data' and click OK.
- 5. Refresh and verify: Right-click the PivotTable, click Refresh, and confirm that items with no data now appear.
Frequently Asked Questions
Why is Show items with no data greyed out in my WPS PivotTable?
It usually means the field is not placed in the Rows or Columns area, or the PivotTable uses a Data Model or OLAP source. For a standard range, move the field to Rows or Columns, open Field Settings > Layout & Print, and enable the option.
Does Show items with no data work for value fields or report filters?
No, the option applies only to row and column fields. Move the field to the Rows or Columns area before checking Field Settings.
Will enabling this option display items that do not exist in the source data?
No, it only displays items that exist in the source data but do not currently have data in the PivotTable layout.
Do I need to refresh after changing the setting?
Yes, right-click the PivotTable and choose Refresh, or use the Refresh command on the PivotTable Analyze tab, to update the displayed items.
Can WPS Spreadsheets open Excel pivot files and change this setting?
Yes, WPS Spreadsheets supports .xlsx and .xls PivotTables and provides Field Settings layout options for row and column fields.
