Pivot Table Issues

How to Show Missing Zero-Count Numbers in an Excel PivotTable

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs an Excel PivotTable to display all consecutive numbers in a sequence, including numbers that have a count of zero records, to ensure accurate charting without missing values.

Product: Excel
Device & OS: not provided
Scenario: Creating a PivotTable and chart where a complete sequence of whole numbers (such as months after graduation) is required, even if filters are applied.
Observed behavior: Numbers with zero records (e.g., 5, 7, and 8) are completely omitted from the PivotTable results, causing gaps in the sequence.

Before you start

Check your source data to ensure the sequence field is formatted correctly as a number, and verify whether you are using a standard PivotTable or one built from the Data Model.

Solution 1Recommended

Enable 'Show items with no data' for Regular PivotTables

Adjust the Field Settings to force Excel to display all categories even if they have no corresponding data records.

Select the target field

Click any cell inside the target field (e.g., the 'Months' column) within your existing PivotTable.

Open Field Settings

Right-click the selected cell and choose 'Field Settings' from the context menu.

Navigate to Layout & Print

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

Check the display option

Check the box next to 'Show items with no data' and click 'OK' to apply the changes. The missing numbers will now appear.

Solution 2

Configure PivotTable Options for the Data Model

If your PivotTable is built from the Data Model, you need to enable zero-count rows in the PivotTable Options instead of Field Settings.

Solution 3

Include Missing Values in the Source Data

Add dummy rows to your dataset or use a complete lookup table so that every number exists in the data, preventing items from disappearing when filters are applied.

Manage Data Effectively with WPS Spreadsheet

Easily Manage PivotTable Data with WPS Office

WPS Spreadsheet offers powerful PivotTable features fully compatible with Excel, allowing you to easily format, filter, and display your data without omitting important zero-count records.

1. Insert a PivotTable: Open your workbook in WPS Spreadsheet, select your dataset, go to the 'Insert' tab, and click 'PivotTable'.
2. Access Field Settings: Right-click the row label field in your generated PivotTable and select 'Field Settings'.
3. Modify Layout Options: Navigate to the 'Layout & Print' tab within the Field Settings window.
4. Display missing items: Check the box for 'Show items with no data' and click 'OK' to instantly show zero-count entries.

100% compatible with Microsoft Excel (.xlsx) formatsIntuitive PivotTable settings and advanced data analysis toolsEasily toggle 'Show items with no data' options with a familiar interfaceFree and lightweight alternative for seamless data management

Frequently Asked Questions

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

This option may be disabled if the field is grouped or if it is a calculated field. Ungrouping the data or modifying the calculated field settings will typically re-enable the checkbox.

Can I replace empty cells in a PivotTable with a zero?

Yes. Right-click anywhere in the PivotTable, select 'PivotTable Options', navigate to the 'Layout & Format' tab, check the box for 'For empty cells show:', and enter '0' in the adjacent text box.

Why do my zero-count items disappear when I apply a filter?

Standard filters might hide items that don't meet specific criteria, even if 'Show items with no data' is checked. To keep them visible during filtering, it is best to use a separate lookup table containing the complete list of items linked to your main data in the Data Model.

Excel & Data Problems