Excel Graphs: Ignore Zero Values Without Hiding Data

Charts & pivot tables · Updated November 17, 2026

Yes. To keep zero values out of an Excel graph without changing your source data, make a helper range that returns #N/A for zeros and the original value for everything else, then chart that range. Excel does not plot #N/A values in a chart by default; if you want to handle genuinely blank cells instead, you can change the chart’s empty-cell settings.

What “Ignore Zero” Means in an Excel Graph

A zero is a real numeric value, not an empty cell. In a column chart, Excel normally draws a bar with zero height; in a line chart, it may put a marker at zero or pull the line down to the zero level. That can make a chart look misleading when zero actually means “no reading,” “not reported,” or “not applicable.”

First decide what the zero represents. If it is a measured result, such as zero sales or zero degrees, removing it hides meaningful information. If it is a placeholder for missing data, excluding it may make the chart easier to interpret. The best solution depends on that distinction: preserve valid zeros, and suppress only the zeros that are acting as placeholders.

The Most Reliable Method: Use a Helper Range

A helper range leaves your original values untouched and gives the chart a separate set of values to plot. In Microsoft 365 for Windows, this is usually the clearest option because it makes the chart’s treatment of zeros explicit and lets you check the source data at any time.

  1. Suppose your category labels are in A2:A7 and your measurements are in B2:B7. Put a heading such as “Chart values” in C1.
  2. With the original measurements in B2:B7 and the first helper result going in C2, enter this formula in C2: =IF(B2=0,NA(),B2).
  3. Fill the formula down through C7. For example, if B2 contains 12, C2 returns 12; if B3 contains 0, C3 returns #N/A.
  4. Select the category labels in A2:A7 and the helper values in C2:C7, then insert or update your chart to use those ranges. If the chart already exists, use Chart Design > Select Data to change its series values.

The chart omits the points or bars corresponding to #N/A, while column B still contains the original zeros for calculations, auditing, and other reports. You can hide column C if you do not want the helper values visible, but keep in mind that hidden source data may not appear in a chart unless the chart is set to show it.

This method also works when values come from formulas: put the conditional formula in the helper range rather than replacing the formula that produces the original result. The helper cells will show #N/A for excluded entries, so keep them separate from totals or other calculations that are not intended to include chart-specific errors.

When Blank Cells Are the Better Choice

If the source cells are genuinely empty, Excel can display those gaps according to a chart setting. Select the chart, then choose Chart Design > Select Data > Hidden and Empty Cells. In the dialog, choose how to show empty cells: as gaps, as zero, or by connecting data points, when those options are available for the chart. For a line chart where a missing observation should create a visible break, “Gaps” is often the most faithful choice.

This setting controls empty cells; it does not turn numeric zeros into blanks. A cell containing 0 is still a zero, so changing the empty-cell setting alone will not remove its bar or marker. You need to change the plotted data, such as with the helper formula above, if you want to exclude numeric zeros.

Be cautious with formulas that return an empty string. A cell displaying nothing may contain a formula, so it is not necessarily a truly empty cell, and chart behavior can differ from what you expect. If the goal is reliably to omit a plotted value, returning #N/A in a chart-only helper range is generally more dependable than returning an empty string.

How the Result Changes by Chart Type

Excluding a value does not have exactly the same visual effect in every graph. In a column or bar chart, a zero-height bar is usually unobtrusive, but its category label may remain. Replacing the value with #N/A removes the plotted bar; it does not automatically remove the category from the axis. If you need the category itself gone, you may need a filtered or compacted source range as well.

In a line chart, zero can be especially noticeable because it may create a sharp drop. A missing point can instead leave a gap in the line, which tells the reader that no value is being shown. Connecting the points across a missing observation suggests continuity between the surrounding measurements, so use that choice only when it suits the story the data should tell.

For an XY (scatter) chart, the plotted position depends on both the X and Y values. If a zero is in the Y range, excluding that Y value removes the point; if zero is in the X range, it may represent a legitimate position on the horizontal scale. Check which series range contains the zero before changing the chart data. A zero in the horizontal or vertical axis scale is different again: it is part of the scale, not a data point, and hiding a series value will not remove the axis origin.

Keep the Original Data and Chart Data Separate

It is tempting to delete zeros or overwrite them with blanks. That can damage the workbook’s meaning: later formulas may need the true value, and another chart may need to display it. A helper range is reversible and documents the rule directly in the worksheet. Give the helper column a clear heading, and add a note if zero means “not recorded” rather than a measured zero.

There are other ways to control what a chart includes, but each has trade-offs:

  • Helper range with NA(): best when the source should remain intact and only the chart should omit zeros.
  • Filter the source table: useful when you also want the worksheet view to exclude zero rows. The chart’s behavior depends on its settings for displaying hidden or filtered data.
  • Manually exclude cells from the series: practical for a small, rarely changing chart, but tedious when the data updates.
  • Change zeros to blanks in the source: appropriate only when those cells truly represent missing data and other workbook calculations can safely use blanks.

For a changing report, consider whether the helper formula should apply to every new row. If the source is an Excel Table, adding a row can extend calculated columns automatically, which helps keep the chart-ready values consistent. Check that the chart’s data range also includes new rows; a correct helper formula cannot plot rows that are outside the series range.

Common Problems and How to Fix Them

  • The zero still appears: verify that the chart series points to the helper column, not the original values. Select the chart and inspect Chart Design > Select Data.
  • A line has a gap: that is often the expected result when a point is not plotted. If connecting across missing values is more appropriate, review the Hidden and Empty Cells options.
  • The category label remains: excluding a value does not necessarily remove its category. Filter or restructure the chart’s source categories if the label itself should disappear.
  • A value that looks like zero remains: a calculated value might be a tiny decimal rather than exactly 0. For instance, rounding or floating-point calculations can produce a number close to zero. Decide on a meaningful tolerance before excluding near-zero results; do not hide small but valid measurements by accident.
  • The chart changes when rows are hidden: open Hidden and Empty Cells and check whether the chart should display data in hidden rows and columns. The choice affects more than just zeros.

Also distinguish a data value from a data label. If the point should remain in the graph but its label should not display “0,” changing the series to #N/A is the wrong fix because it removes the point. Instead, adjust the label’s number format or use a separate label range, and confirm the result on the chart so the axis and plotted data still communicate the intended value.

Choosing the Right Approach

Use NA() in a helper range when you want to exclude numeric zero values from the plotted series and preserve the source. Use empty-cell settings when the source already has real blanks and you want to decide whether those blanks create gaps, zeros, or connected lines. Filter or rebuild the source when the category itself should vanish, not merely its plotted value.

Before sharing the graph, check a few representative cases: a normal positive value, a genuine zero, a missing entry, and, if relevant, a negative value. Confirm that the chart tells the same story as the underlying data. A graph that omits zeros can be visually cleaner, but readers may otherwise assume the omitted categories had no observation. A legend note or chart subtitle explaining that zero-coded missing values are excluded can prevent that misunderstanding.

Frequently Asked Questions

Can Excel hide zero values in a chart without deleting them?

Yes. Put a formula such as =IF(B2=0,NA(),B2) in a separate chart helper range, using B2 as the source value, and chart the helper results. The original value remains in the worksheet, while Excel omits the #N/A result from the graph.

Does “Show empty cells as gaps” ignore zero?

No. That option applies to empty cells, not cells containing the number 0. Use a helper range that returns #N/A for zeros, or otherwise change the chart’s source data, if numeric zeros should not be plotted.

Will a chart formula that returns an empty string work?

It may not behave like a truly empty cell in every chart situation. If dependable omission is important, use NA() in a chart-only helper range. Use empty strings only after checking how your particular chart displays them.

Can I remove the category label as well as the zero point?

Not simply by replacing the plotted value with #N/A. The category can remain on the axis even when its point or bar is not plotted. Filter the source or build a compact chart range that excludes both the category and its value if the label must disappear.

Should I hide zero sales or measurements?

Only if zero means missing or not applicable in your dataset. If zero is a valid observation, removing it can distort the message of the chart. Keep valid zeros visible and use a separate marker or note to distinguish them from missing data when necessary.

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.