Pivot Table Issues

How to Enable Show Items with No Data in an Excel PivotTable

Huda Qurayshi Oct 22, 2026 868 views

Question details

The user needs to display years with no filtered data in an Excel PivotTable, but the 'Show Items with No Data' option is disabled.

How to Enable Show Items with No Data in an Excel PivotTable

Product: Microsoft Excel 2021
Device & OS: not provided
Scenario: Filtering data in a PivotTable where certain years or categories have zero values and are automatically hidden.
Observed behavior: The PivotTable omits the years with zero values entirely, and the setting to show items with no data cannot be checked because it is greyed out.

Before you start

Ensure your original dataset actually contains the missing years or categories in the source column, even if the corresponding value columns are blank or zero.

Solution 1Recommended

Enable Show Items with No Data via PivotTable Options

The standard method to force a PivotTable to display empty categories on rows or columns.

By default, Excel PivotTables hide items that do not have data for the current filter context. You can change this behavior in the Field Settings.

Access Field Settings

Right-click on one of the items in the row or column field (e.g., a specific Year) inside your PivotTable and select Field Settings from the context menu.

Navigate to Layout & Print

In the Field Settings dialog box, click on the Layout & Print tab.

Check the Option

Check the box next to 'Show items with no data' and click OK to apply the changes.

Enable Show Items with No Data via PivotTable Options

Solution 2

Workaround for Greyed-Out Option (Data Model)

Use this method if the 'Show items with no data' option is disabled, which typically happens when the PivotTable is connected to the Excel Data Model.

Manage PivotTables Easily with WPS Office

Create and Customize PivotTables in WPS Spreadsheet

WPS Spreadsheet provides robust, easy-to-use PivotTable features that are fully compatible with Microsoft Excel formats. You can effortlessly manage empty data fields and customize your reports without dealing with complex Data Model restrictions.

1. Open Your File: Launch WPS Spreadsheet and open your existing .xlsx workbook.
2. Access Field Settings: Right-click the specific row or column label in your PivotTable and select Field Settings.
3. Enable Empty Items: Navigate to the Layout & Print tab, check the 'Show items with no data' box, and click OK.
4. Format Blank Cells: To display zeros instead of blanks, right-click the PivotTable, select PivotTable Options, and check 'For empty cells show', then type '0'.

100% compatible with Microsoft Excel (.xlsx) formats and PivotTable structures.Intuitive PivotTable options for handling blank cells and displaying empty data items.Lightweight software that processes large datasets quickly and smoothly.

microsoft office alternative - wps office

Frequently Asked Questions

Why is 'Show items with no data' greyed out in my PivotTable?

This option is disabled if your PivotTable is based on the Excel Data Model (Power Pivot) or an external OLAP database. Standard PivotTables created directly from a worksheet range support this feature without issues.

How do I show zero instead of blanks in a PivotTable?

Right-click anywhere inside the PivotTable, select 'PivotTable Options', go to the 'Layout & Format' tab, and check 'For empty cells show:'. Enter '0' in the adjacent text box and click OK.

Does checking 'Show items with no data' affect performance?

Yes, if your source data has a massive number of unique items across different columns, enabling this feature can significantly increase the size of the PivotTable and slow down calculation times as Excel attempts to render every possible combination.

Excel & Data Problems