Pivot Table Issues

How to Hide Zeros in an Excel PivotChart

Muhammad Talha Sep 27, 2026 868 views

Question details

The user wants to format an Excel PivotChart and PivotTable so that zero values are visually hidden from the display.

How to Hide Zeros in an Excel PivotChart

Product: Excel
Device & OS: not provided
Scenario: Cleaning up a PivotTable or PivotChart presentation by preventing zero values from cluttering the data visualization.
Observed behavior: Zero values are currently visible in the data fields of the PivotTable and PivotChart, making the chart look messy or difficult to read.

Before you start

Ensure your PivotChart is properly linked to its source PivotTable, as formatting changes must be applied directly to the PivotTable's value field settings to reflect on the chart.

Solution 1Recommended

Apply a Custom Number Format to the Value Field

By modifying the number format settings of the PivotTable's value field to a specific custom code, you can display positive and negative numbers while completely hiding zeros.

Excel uses a four-section format code for custom number formatting: positive values, negative values, zero values, and text. By leaving the third section (zero values) blank in the code '0;-0;;@', Excel is instructed to display nothing when the value equals zero.

Select the Associated PivotTable

Locate the PivotTable that is generating your PivotChart. The formatting must be applied here, not directly on the chart.

Open Value Field Settings

Right-click on any numeric cell within the value field of the PivotTable and select 'Value Field Settings' from the drop-down menu.

Access Number Formatting

In the Value Field Settings dialog box, click the 'Number Format' button located at the bottom left corner.

Enter the Custom Code

In the Format Cells window, choose 'Custom' from the Category list. In the 'Type' input field, clear the existing text, type '0;-0;;@', and click 'OK' on both dialog boxes to apply the changes.

Apply a Custom Number Format to the Value Field

Automatic Chart Update: Once you apply this custom format to the PivotTable, the linked PivotChart will automatically update to hide all zero data labels and markers.

Data Analysis with WPS Spreadsheet

Hide Zeros in PivotCharts Easily with WPS Office

WPS Spreadsheet offers powerful, user-friendly PivotTable and PivotChart functionalities. You can effortlessly clean up your data presentation and hide zero values using the exact same custom number formatting techniques.

1. Insert a PivotTable: Open your dataset in WPS Spreadsheet, click the 'Insert' tab, and select 'PivotTable'.
2. Generate a PivotChart: Select any cell in your new PivotTable, go to the 'Insert' tab, and click 'Chart' to create a linked PivotChart.
3. Open Field Settings: Right-click the data field inside your PivotTable and choose 'Value Field Settings'.
4. Apply Custom Format: Click 'Number Format', select 'Custom', input '0;-0;;@' in the Type box, and confirm to hide zeros instantly.

Fully compatible with Microsoft Excel (.xlsx) files and native PivotTables.Easily hide zero values using the intuitive custom number format menu.Lightweight, fast-loading alternative for robust data analysis.Completely free to use with familiar menus and charting tools.

microsoft office alternative - wps office

Frequently Asked Questions

Will hiding zeros with a custom format affect my PivotTable totals?

No. The custom number format '0;-0;;@' only alters how the data is visually displayed. The zero values remain in the dataset and are still calculated normally in your grand totals and subtotals.

Why didn't the formatting work when I applied it directly to the PivotChart?

PivotCharts inherit their structural and numerical formatting from their source PivotTable. To change how values appear in the chart, you must modify the 'Value Field Settings' in the associated PivotTable directly.

Can I filter out zeros instead of just hiding them?

Yes. If you want to entirely remove the categories containing zero values from your chart, you can click the drop-down arrow on your PivotTable's Row or Column Labels, select 'Value Filters', and set the rule to show items that are 'Not equal to' zero.

What does the custom format code '0;-0;;@' actually mean?

The format is divided into four sections separated by semicolons: positive numbers (0), negative numbers (-0), zero values (blank), and text (@). By leaving the section between the second and third semicolon empty, you instruct the software to display nothing for zero values.

Excel & Data Problems