Excel Graph: Hide Zero Values Without Hiding Data

Charts & pivot tables · Updated November 17, 2026

To hide zero values in an Excel graph while keeping the underlying data, create a helper column that returns #N/A for zeros, then chart that column; Excel does not plot the #N/A points. If you only want to hide a zero label—not remove the plotted value—apply a custom number format to the chart labels or axis instead.

Why zeros appear in Excel graphs

Excel charts plot the values in their source range, and a numeric zero is still a real value. In a column chart, a zero-height column may be difficult to see, but its category remains part of the chart. In a line chart, a zero can pull the line down to the axis. Data labels may also display a visible 0, even when the point itself is not prominent.

That means “hide zero values” can describe several different tasks. You might want to remove zero points from the plotted data, suppress only the printed zero labels, or remove entire categories whose values are zero. Those results are not interchangeable. Choose based on what the graph needs to communicate, and keep the original values intact when you still need them for calculations or auditing.

  • Omit zero points: Use a helper range returning #N/A for zero values.
  • Hide zero labels: Apply a custom number format to data labels or the value axis.
  • Remove whole categories: Filter the chart’s categories or its source data.

Best method: use a helper column with NA()

A helper column is usually the clearest way to omit zeros from a chart without changing the source data. Assume the original values are in B2:B4, with 12 in B2, 0 in B3, and 8 in B4. Enter this in C2 and fill it down through C4: =IF(B2=0,NA(),B2). The helper results are 12, #N/A, and 8. Select the categories and helper values when creating the chart, or use Chart Design > Select Data to change the chart’s series to column C.

The NA() function returns Excel’s not-available error. Excel charts do not plot a #N/A point, so the zero disappears from the chart while the original zero remains in column B. For a line chart, the missing point normally appears as a break in the line rather than a point at zero. Check the result in your chosen chart type, especially if the shape of a line or area is important to the meaning of the graph.

This approach works well for ordinary charts and can be extended to multiple series: create one helper column for each series you want to clean. Keep the category labels aligned with the helper values so dates or names still appear in the intended positions. You can hide the helper columns after building the chart, but first confirm that the chart still displays the series; by default, Excel may omit data in hidden rows or columns.

Hide zero data labels with number formatting

If the point should remain in the chart but its printed label should disappear, format the labels rather than changing the data. Right-click a data label and choose Format Data Labels. In the formatting pane, open Number, choose Custom, enter 0;-0;;@ in the format-code box, and select Add. If the label format is linked to the source cells, turn off that link if necessary so the custom format can take effect.

The four sections in a custom number format represent positive numbers, negative numbers, zero, and text. In 0;-0;;@, the third section is empty, so a zero label is displayed as nothing. Positive values still appear as whole numbers and negative values retain a minus sign. For one decimal place, use 0.0;-0.0;;@. These formats affect how numbers are displayed; they do not turn zeros into blanks or remove zero-height columns.

You can use the same principle for an axis. Right-click the value axis, choose Format Axis, then open Number and apply the custom format. This hides zero tick labels but does not remove the axis baseline or alter the plotted data. If your goal is to make the chart’s scale easier to read, consider whether hiding the zero tick could make the scale less clear to readers.

Using blanks instead of zeros

Another option is to make the chart source cell blank. In a helper range, with the original value in B2, this formula returns an empty text string when B2 is zero: =IF(B2=0,"",B2). For the example values 12, 0, and 8, the helper cells display 12, a blank, and 8. This can be useful when a blank is also the right representation for the data, but it is less dependable than NA() for controlling chart behavior.

A formula that returns an empty string is not the same thing as a genuinely empty cell. Depending on the chart type and how Excel interprets the source, it may be plotted like zero rather than treated as a gap. To inspect the chart’s treatment of genuinely empty cells, select it and choose Chart Design > Select Data > Hidden and Empty Cells. Available choices can include displaying empty cells as gaps, zero, or connecting data points with a line. This setting concerns empty cells; it is not a general switch that hides numeric zero values.

If you manually clear a source cell, you have removed its value, which may affect formulas or other reports that use that range. A separate helper range avoids that side effect. For a chart that must reliably omit selected numeric points, use NA() and test the displayed result instead of assuming an empty-looking cell will behave as a gap.

Filtering categories and hidden source rows

If each zero belongs to a category that should disappear entirely—for example, a product with no sales—filtering categories may be more appropriate than omitting individual points. Select the chart and use its Chart Filters button, when available, to deselect categories. This removes those categories from the displayed chart, rather than merely leaving a blank position for a zero point. It is a manual selection, however, and may need updating when the data changes.

You can also filter the source table, but check whether the chart is set to include data in hidden rows or columns. Select the chart and open Chart Design > Select Data > Hidden and Empty Cells; the option to show data in hidden rows and columns affects whether hidden source data is still plotted. The exact options available depend on the chart and Excel build. Filtering the worksheet does not always give the result people expect if the chart is configured to include hidden data.

For a report that updates as values change, a helper column is often easier to maintain than repeatedly filtering categories. For a one-off presentation where zero categories should simply be omitted, chart filtering may be quicker. Avoid deleting source rows just to tidy a chart unless you are certain other formulas, tables, or reports do not rely on them.

Choose the right approach for the chart

Think first about whether you want to hide a mark, a label, or a category. The helper-column method changes what the chart plots, while custom number formatting changes only what a reader sees as text. Filtering changes which categories are included. A useful rule is to use the least disruptive method that creates the intended visual result.

  • Line or scatter chart: Use NA() when zero should not be represented as a plotted point. For a scatter chart, keep the X values aligned with the Y values and suppress the Y value for the point you want omitted.
  • Column or bar chart: A zero already has no visible bar height, so hide its data label with a custom format if the unwanted element is the printed 0.
  • Chart with many categories: Filter categories only when removing their labels and positions is intentional; use a helper column if the categories should stay in place.
  • Chart used alongside calculations: Preserve the original values and build the chart from a separate helper range.

In Microsoft 365 for Windows, the ribbon paths above are the standard chart-editing route. Excel for Mac offers comparable chart-formatting and data-selection controls, though pane layouts and wording can vary by release. Excel for the web supports many basic chart edits, but some detailed formatting controls may be more limited; if a custom chart number format is unavailable in your browser version, open the workbook in the desktop app.

Common problems and how to fix them

The zero still appears. Confirm that the chart series uses the helper column, not the original value column. Open Chart Design > Select Data and inspect the series range. Also check whether the zero comes from a different series or a data label tied to another range.

A label disappears but the mark remains. That is expected when you use a custom number format. Formatting changes the displayed label, not the underlying chart point. Use NA() in a helper range if the plotted point itself must be omitted.

A value that looks like zero is still plotted. It may be a small nonzero number rounded for display, such as 0.004 shown with no decimal places. The test B2=0 only catches an actual numeric zero. Decide whether small values should count as zero and set a deliberate threshold; do not hide them accidentally just because the chart label rounds them down.

The chart shows a gap where you expected a continuous line. A missing point can interrupt a line. That may be an honest representation of unavailable data, but it may not suit a chart where the line should span missing observations. Review the chart type and its empty-cell settings; do not replace missing values with zero merely to connect the line, since that changes the meaning of the data.

The helper cells show errors. #N/A is intentional in this method. It prevents the zero from being charted, but it will also be visible in the worksheet. Keep the helper column separate from user-facing tables, or place it on a dedicated sheet, and label it clearly so another workbook user knows why those errors are present.

Frequently Asked Questions

Can I hide zero values in an Excel chart without deleting them?
Yes. Keep the original values and chart a helper range that returns NA() for zero values. Alternatively, use a custom number format if you only want to hide zero labels.

How do I hide 0 labels but keep the bars?
Right-click a data label, choose Format Data Labels, then under Number apply the custom format 0;-0;;@. The zero-height bar remains; only its printed label is suppressed.

Does Excel have a chart setting to ignore all zeros?
There is not a universal chart switch that removes every numeric zero. The hidden-and-empty-cells setting controls empty cells, while numeric zeros need a helper range, filtering, or display formatting depending on the result you want.

Will a formula returning an empty string hide a zero in a chart?
Not reliably in every chart context. Excel may treat a formula result of "" differently from a genuinely empty cell. Use NA() when you need the chart to omit a numeric point, then confirm the chart behaves as intended.

Can I hide zero values on the chart axis?
Yes. Apply a custom number format such as 0;-0;;@ to the value axis through Format Axis > Number. This hides the zero tick label, not the axis line or zero-valued data points.

Related

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.