To remove visible zero labels from an Excel stacked bar chart, format the data labels with a number format that leaves the zero section blank. A zero-valued segment has no bar length, so if you need to exclude it from the chart’s plotted data rather than just hide its label, use a helper range that returns #N/A for zero values.
What a zero value means in a stacked bar chart
A stacked bar chart places series values end to end for each category. If a series value is zero, it contributes no length to the bar, so there is usually no colored segment to remove. The category and series can still be present in the chart, however, and a data label may display 0 at the point where that segment would have appeared. That label is often what people mean when they ask how to remove zero values.
It helps to distinguish three different goals:
- Hide zero labels: Keep the source data and chart calculations unchanged, but stop showing 0 as a label.
- Omit zero-valued points: Keep the chart connected to a data range, but make selected points unavailable to plot.
- Remove all-zero categories: Exclude a whole bar, such as a department with zero values in every series.
These are not interchangeable. Hiding a label changes only what the chart displays. Excluding a point changes what Excel plots, while removing a category changes which bars appear. Choose the smallest change that gives you the result you want, so the chart remains an accurate representation of the worksheet.
Hide zero data labels with a number format
If the bars look right and only the printed zeroes are distracting, a custom number format is usually the simplest fix. It does not alter source values, totals, or the stack; it only tells Excel to render numeric zero labels as blank. In Excel for Microsoft 365 on Windows:
- Select a data label in the chart. If labels are not already shown, select the chart, choose Chart Design > Add Chart Element > Data Labels, and pick a position.
- Right-click a selected label and choose Format Data Labels to open the formatting pane.
- Expand Number. If necessary, clear Linked to source, choose Custom as the category, and enter
0;-0;;in the format code field. Select Add if it is available.
The format sections are for positive numbers, negative numbers, zero, and text, in that order. In this example, positive values display as whole numbers, negative values display with a minus sign, and the empty third section suppresses zero. If the chart uses decimal values, use a suitable format such as 0.0;-0.0;; to preserve one decimal place for nonzero labels. Check the chart afterward to confirm that the labels still show the precision you need.
On Excel for Mac, select the label and open the data-label formatting pane; the precise pane layout can vary by release, but the custom number-format approach is the same. If the labels were added with Value From Cells, Excel is displaying text from worksheet cells rather than simply formatting the plotted values. In that case, use blank text for zeroes in the label source cells, or switch to labels based on values before applying a number format.
Exclude zero-valued points with a helper range
Use a helper range when you want Excel not to plot selected zero-valued points, while retaining the original data for calculations or records. A formula can replace zero with the worksheet error #N/A, which Excel charts generally do not plot as a data point. This changes what is supplied to the chart, so use it only when omitting zero points is the intended presentation.
For example, assume the original values for one series are in B2:B10 and the chart will use a helper series in C2:C10. In C2, enter the following formula and fill it down through C10:
=IF(B2=0,NA(),B2)
If B2 contains 0, the helper cell returns #N/A; if B2 contains 12, it returns 12. The chart can therefore omit the zero point while retaining the value 12. Repeat the helper-column pattern for each series whose zero points you want to omit, then update the chart to use the helper columns: select the chart, choose Chart Design > Select Data, and edit the relevant series ranges. Keep category labels pointed at the original category column.
The formula does not delete or change the original zero. It creates a chart-ready alternative, which is useful if other formulas, reports, or checks still need the actual value. The trade-off is that the worksheet will visibly contain #N/A in the helper cells. Label the helper columns clearly so another person does not mistake them for the underlying data.
Remove whole categories that contain only zeroes
If an entire category has zero in every series, hiding its labels will leave an empty category in the chart. To remove that bar and its category label, filter the source data or build a separate chart range that excludes the category. For a data table with filters, filter out the rows you do not want, then check whether the chart responds. Excel charts commonly omit filtered rows, but chart settings can allow hidden data to be plotted.
To inspect that behavior, select the chart and use Chart Design > Select Data > Hidden and Empty Cells. Review the setting for showing data in hidden rows and columns. If hidden rows are being plotted, clear that option when you want filtering to remove them from the chart. This setting applies to hidden data generally, so check the result if the workbook intentionally hides other rows that should remain represented.
For a reusable report, a separate chart source is often clearer than manually deleting categories from the original table. It also avoids losing source information. If categories are added or changed regularly, a filter or formula-driven source can be easier to maintain, although the exact formula-based approach depends on your Excel version and workbook layout.
Which method should you choose?
Use the method that matches the visual problem, rather than changing the data just to make a label disappear:
- Only the 0 text is unwanted: Apply the custom number format to labels. This is the least disruptive option.
- A zero point should not be plotted: Use a helper range returning
#N/Afor zeroes, and point the chart series to that range. - A category has no meaningful values at all: Filter or exclude the complete category from the chart source.
- The chart is meant to show every category for comparison: Keep the category, even if its bar is empty, and hide only unwanted zero labels.
A common misconception is that replacing zero with a blank always removes the point. Excel’s treatment of empty cells depends on chart type and the chart’s empty-cell settings; a blank may appear as a gap or be treated in a way that does not match your intent. A helper cell returning #N/A is a more explicit way to ask Excel not to plot a point, but it is still important to preview the chart and verify the result.
Check data labels and chart settings
When the zeroes remain, first confirm that you formatted the labels for the intended series. A stacked chart can have several series, and selecting one label may select only one data point or one series depending on where you clicked. Click a label once to select the label set for that series; if only one point is selected, click another label in the same series or use the chart’s selection controls to select the full series before formatting.
Also check whether labels show values from cells. If Value From Cells is enabled, the displayed text may come from a separate range and may not respond to the number format used for plotted values. Inspect the label options in Format Data Labels, then either provide blank label-source cells for zeroes or turn off cell-based labels if they are not needed.
For a helper-range chart, check that the chart series actually points to the helper cells, not the original range. Use Chart Design > Select Data to review the series values. If a zero segment still appears, verify that the source cell is exactly numeric zero and that the formula was filled down correctly. A value that looks like zero but is a small decimal, such as 0.0001, is not equal to zero and will be returned unchanged by the example formula.
Finally, confirm that the chart’s totals and labels communicate the same thing. A zero has no effect on a stacked total, but hiding a nonzero value or excluding the wrong point could make the graphic misleading. Keep the raw data available and consider adding a note when the chart intentionally omits values for readability.
Frequently Asked Questions
Why does my stacked bar chart show 0 even though there is no visible segment?
A zero has no bar length, but Excel can still display a data label for the point. Hide the label with a custom number format such as 0;-0;; if you want to keep the data unchanged.
Does hiding zero labels change the stacked total?
No. A number format changes how labels appear, not the cell values or chart calculations. A zero also contributes nothing to the stack total.
Can I remove zeroes from the chart without deleting them from my worksheet?
Yes. Use a separate helper range that returns #N/A for zero values and use that range for the chart series. The original values remain intact.
Will changing zeroes to blank cells always remove them from a chart?
No. Excel’s display of empty cells depends on chart settings and context. Check Chart Design > Select Data > Hidden and Empty Cells, or use a helper formula returning #N/A when the goal is to omit a point.
How do I remove an empty bar category, not just its zero label?
Exclude the category from the chart source or filter its source row. If the chart still shows filtered rows, review whether Show data in hidden rows and columns is enabled in the chart’s hidden-and-empty-cell settings.
Related
- Excel Number Format for Negative Numbers in Parentheses
- Excel Number Format for Lakhs With Commas
- Excel Number Format for Millions With One Decimal Place
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Clear an Excel Pivot Table: Filters, Data, or Table?To clear a PivotTable in Excel for Microsoft 365 on Windows, click inside it and choose PivotTable Analyze…
- Excel Column Chart With Two Y-Axes: Setup and TipsTo make an Excel column chart use two y-axes, select the chart and choose Chart Design > Change…
- Pivot Table Spill Error: Causes and FixesA PivotTable spill error usually means a dynamic-array formula cannot place its results because the intended spill range…
- Excel Histogram with Multiple Series: Methods That WorkExcel’s built-in Histogram chart is designed for one data series at a time, so selecting several columns will…
- Excel Pivot Table Columns Side by Side: How to ShowTo show PivotTable categories side by side, select the PivotTable, open the PivotTable Fields pane, and drag the…
- Excel Chart Title Formula: Link a Title to a CellTo make an Excel chart title dynamic, select the chart title, click the formula bar, enter a reference…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.