How to Hide Zeros in an Excel Pivot Chart Properly

In Excel for Microsoft 365 on Windows, the right way to hide zeros in a PivotChart depends on what you mean: apply a custom number format to hide zero labels, or use a PivotTable value filter to remove zero-valued categories from the chart. Formatting hides the displayed zero but keeps the data; filtering changes which items the PivotChart shows.

Choose what you want to hide

A zero can appear in several places in a PivotChart, and the fixes are not interchangeable. It might be a data label printed on a bar, a tick label on the value axis, or a category whose value is zero. A zero-height column or a point at zero may also be present without displaying a printed “0.” Decide which of these is distracting before changing the PivotTable or chart.

Only hide printed zero labels: use a number format with an empty zero section.
Hide zero tick labels on the value axis: apply that kind of number format to the axis.
Remove categories whose summarized value is zero: apply a Value Filter to the relevant PivotTable field.
Keep the category but make a zero point visually unobtrusive: consider whether the chart already does this. A zero-height column, for example, has no visible bar, although its category can still appear.

These approaches do not erase values in the source data. A display format changes appearance; a PivotTable filter changes the visible summary and its linked PivotChart.

Hide zero data labels with a number format

If the problem is a “0” printed next to a point or inside a bar, format the data labels rather than filtering the PivotTable. In Excel for Microsoft 365 on Windows:

Select a data label in the PivotChart. Selecting one label in a series usually selects the labels for that series.
Right-click and choose Format Data Labels.
In the Format Data Labels pane, open Number. If Linked to source is selected, clear it so the label can use its own number format.
Choose Custom, enter 0;-0;;@ in the format code box, and select Add if that button is shown.

The four sections in that custom format apply, in order, to positive numbers, negative numbers, zero, and text. The third section is empty, so a value of zero is displayed as blank. Positive and negative values remain visible. For a chart showing values with one decimal place, use 0.0;-0.0;;@. For thousands separators and whole numbers, use #,##0;-#,##0;;@.

This changes the label’s appearance, not the value used in the PivotTable or chart. Calculations, totals, and filters still treat zero as zero. If you have several data series, check the labels in each series; label formatting may need to be applied to each one. A refresh or later chart-format change can also warrant a quick check that the custom format remains in place.

Hide zero categories with a PivotTable value filter

Use a value filter when you want to remove a row or category from the PivotChart because its summarized value is zero. Since a PivotChart is linked to its PivotTable, filter the corresponding field in the PivotTable:

Click a cell in the PivotTable that feeds the chart.
Open the drop-down next to the row field containing the categories you want to filter.
Choose Value Filters > Does Not Equal.
In the filter dialog, select the value field to test, enter 0, and confirm.

The PivotTable and PivotChart then omit items whose selected summarized value equals zero. The items and their source records are not deleted, and you can clear the filter to show them again. If your goal is to show only positive values, a Greater Than filter with 0 is another option, but it also removes negative values. It is not the right choice if negative results should remain visible.

Think through what the chosen value field means. Suppose a PivotTable lists regions and shows both “Units Sold” and “Returns.” A filter based on Units Sold can remove a region with zero sales even if that region has returns. Conversely, a region with nonzero sales stays even if its returns are zero. A value filter tests the selected summary; it does not independently hide a category whenever any one of several chart series happens to be zero.

Hide zero values on the chart axis

If the unwanted zero is the axis tick label at the origin, format the value axis. Right-click the chart’s value axis and choose Format Axis. In the pane, expand Number, choose Custom, enter 0;-0;;@, and select Add if available. Use a format with decimal places or thousands separators if that matches the chart’s other tick labels.

This suppresses the printed zero on the axis; it does not remove the axis line, move the origin, or exclude zero from the chart’s scale. The line at zero can still be useful because it shows where positive and negative values meet. If the axis format is set to match the source, clear Linked to source before applying a separate format, when that option is available.

Axis formatting is different from formatting data labels. If both the axis and the points have a printed zero, you may need to format both separately. Also, do not confuse the value axis with the category axis: a PivotChart with text categories usually has category names along one axis and numeric scale labels along the other.

Why “show empty cells as gaps” does not hide zero

A common workaround is to change how a chart displays empty cells. That setting concerns cells with no value; a numeric zero is a real value, not an empty cell. As a result, telling Excel to display empty cells as gaps does not turn PivotTable zeros into blanks or remove zero-valued categories.

Likewise, the PivotTable option commonly used to show a message for empty cells—found in PivotTable Analyze > Options, under Layout & Format—is for empty cells in the report. It does not replace actual zeros. A source cell containing 0 remains different from a blank source cell, and a PivotTable may calculate or display those cases differently depending on the summary and layout.

Changing a source value to blank just to make a chart look cleaner can alter the meaning of an analysis. For instance, an average that includes recorded zeros can differ from an average that excludes missing or blank observations. If zero means “measured and none,” keep that value in the data and hide only its label when appropriate.

What changes after a refresh

Refreshing a PivotTable updates its summary from the source data; it does not normally mean that a display format is a data-cleaning operation. A zero-label format is still only a presentation choice, while a value filter continues to determine which summarized items are visible. However, chart labels, axis settings, and PivotTable layouts can be affected when fields or chart elements are changed, so review the chart after refreshing or rearranging fields.

If a zero category returns, check whether the filter is still applied and whether the refreshed value is actually zero. It may now be a small nonzero decimal that is merely rounded to “0” by the display format. For example, a value of 0.04 can appear as 0 with a whole-number format; a filter for values equal to zero will not remove it. Increase displayed decimal places temporarily to diagnose this. If the underlying value is nonzero, decide whether to change the summary, use a threshold filter, or simply keep the rounded display.

Excel for Mac and Excel for the web

The methods rely on PivotTable and chart features that are also available in desktop Excel for Mac, although the ribbon arrangement and pane labels can vary somewhat by release. Look for the chart’s formatting pane or the PivotTable field’s filter menu rather than relying on Windows-only keyboard shortcuts; no shortcut is necessary for these steps.

Excel for the web can open and work with PivotTables and charts, but some detailed chart formatting controls may not be available in the same way as in desktop Excel. If the Number section does not offer a custom format code or a needed PivotChart option, open the workbook in the desktop app and make the change there. Avoid assuming that a blank-cell chart setting will solve the issue in any version: it still does not treat a numeric zero as an empty cell.

Common mistakes and useful checks

Filtering greater than zero when negatives matter: this removes both zero and negative values. Use Does Not Equal 0 if the aim is to exclude only exact zeros.
Formatting the axis when the zero is a data label: axis tick labels and point labels have separate formatting controls.
Expecting a filter to remove a zero from every series independently: a PivotTable value filter is based on the value field you select, and may change which categories appear across the chart.
Assuming a displayed 0 is exactly zero: a rounded, small positive or negative value can look like zero. Increase precision to inspect it.
Replacing zeros with blanks without checking the analysis: blanks and zeros can have different effects on summaries and can communicate different facts.

For a quick decision: use the custom format 0;-0;;@ when the chart should retain its data and categories but omit printed zero labels. Use a PivotTable Value Filter when zero-valued categories themselves should disappear. Use axis formatting only when the zero on the scale is the specific distraction.

Frequently Asked Questions

Can I hide zeros in a PivotChart without changing the PivotTable?
Yes, if you only need to hide zero data labels or the zero tick label. Apply a custom number format to the chart labels or value axis. To remove categories from a PivotChart, filter the linked PivotTable; that also changes which items its report displays.

Does a custom number format delete or change zero values?
No. A format such as 0;-0;;@ changes how the value is shown. The underlying value remains zero and continues to participate in summaries and calculations.

How do I hide only zero bars but keep negative values?
Apply a Value Filter of Does Not Equal 0 to the relevant category field and select the value field you want to test. Avoid Greater Than 0, because that also excludes negative values.

Why does my chart still show a category after hiding its zero label?
Number formatting hides the text label, not the category. If the category itself should be removed, use a PivotTable Value Filter instead.

Will “show empty cells as gaps” hide PivotTable zeros?
No. A zero is a numeric value, while an empty cell has no value. The empty-cell setting does not convert or filter zeros.

Why does a value that looks like zero remain after filtering?
It may be a small nonzero value rounded for display, such as 0.04 shown with no decimal places. Temporarily increase the displayed precision to see the underlying magnitude, then choose a suitable filter or format.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.