Excel Line Chart: How to Ignore Zero Values in a Chart

Yes. To make an Excel line chart ignore numeric zeros, create a helper column that returns NA() for zero values, then chart that column instead of the original data. For values in B2:B13, put this in C2 and fill down: =IF(AND(ISNUMBER(B2),B2=0),NA(),B2). A zero becomes #N/A, which Excel does not plot as a data point; nonzero numbers remain unchanged.

Why Excel Plots Zero Values

A zero is a real numeric value, not a missing observation. Excel therefore plots it at the zero position on the vertical axis. If the surrounding values are 12 and 15, for example, the line drops to zero and rises again. That is correct when zero means “none,” “no sales,” or “no activity.” It can be misleading when zero is being used as a placeholder for “not recorded yet.”

Excel does not provide a general chart switch that says “ignore every numeric zero.” The chart reads the values in its source range, and a number in that range is treated as data. To omit selected points, change what the chart receives—usually with a helper range—or change the source data itself if zeros genuinely should not be there.

This distinction matters because removing zeros changes the message of a chart. A zero in a monthly revenue series may be an important result; a zero in a temperature reading may be a valid measurement. Decide whether zero means a real value or an absent value before hiding it.

Use a Helper Column to Omit Zeros

A helper column preserves the original data while giving the chart a separate set of values. In the example below, column A contains dates, column B contains the original measurements, and column C contains chart-ready values. With the original numeric values in B2:B13, enter the formula in C2 and copy it down: =IF(AND(ISNUMBER(B2),B2=0),NA(),B2).

For example, if B2:B4 contains 12, 0, and 15, the helper results are 12, #N/A, and 15. The zero is not drawn as a point. The original zero remains visible in column B, so calculations and the source record are not silently changed. The formula also leaves genuinely blank cells blank because they are not numeric zeros.

Enter a label such as “Chart values” in C1 and add the formula to C2.
Fill the formula down to the last row of the data. In Excel for Microsoft 365 on Windows, select C2:C13 and press Ctrl+D to fill down after entering the formula in C2. On Mac, use Command+D.
Create or update the line chart so its series uses the dates in column A and the helper values in column C, rather than the original values in column B.
Check that the plotted line skips the zero rows and that the remaining values still match the source.

If you already have a chart, select it and choose Chart Design > Select Data. In the Select Data Source dialog, edit the relevant series so its values refer to the helper range. Keep the category-axis labels linked to the date or label column. If the chart includes headers, do not accidentally include those text labels as data points.

What Happens to the Line at an Omitted Zero?

Omitting a point does not always mean the line will look exactly as intended. A line chart can show a gap where the omitted point was, or it can connect the neighboring plotted points depending on how the chart handles the missing point and its settings. With #N/A, Excel does not plot the error value as a normal point. Inspect the result rather than assuming that “not plotted” means “the line will bridge the gap.”

To review how Excel displays empty cells, select the chart and choose Chart Design > Select Data > Hidden and Empty Cells. The dialog lets you show empty cells as gaps, as zero, or connect data points with a line. These choices are useful for genuinely empty cells. They are not a replacement for converting numeric zero values into an omitted value, and a formula result such as #N/A is not the same thing as an empty cell.

If continuity across omitted observations matters, test the chart with a small sample containing a value, a zero, and another value. Compare the gap and connection options, then use the appearance that communicates the data honestly. In some cases a gap is important because no measurement exists; in others, a connected line makes the trend easier to follow without claiming that the omitted value was zero.

Why Not Use a Formula That Returns an Empty String?

A common alternative is a formula that returns "" for zero. That can look like a blank cell on the worksheet, but it is still the result of a formula, and chart behavior for formula-generated empty strings can differ from what readers expect. Depending on the chart and empty-cell settings, the point may appear as zero or be treated in another way. For a dependable way to prevent a numeric zero from being plotted, NA() is clearer.

There is a practical trade-off: the helper cell displays #N/A. That is intentional, but it may look untidy in a table used by people who do not understand the method. You can put the helper range on a separate worksheet, hide the helper column, or explain in a nearby note that #N/A marks values excluded from the chart. Hiding a column does not necessarily hide its chart data: use the chart’s Hidden and Empty Cells dialog to decide whether data in hidden rows or columns should appear.

When to Use Blanks, Zeros, or #N/A

Choose the representation based on what the data means, not just on which option makes the chart look smoother.

Use zero when the measured or calculated result really is zero and the low point should be visible.
Use a blank when there is no value to report and you want the chart’s empty-cell settings to control the gap or connection.
Use #N/A in a chart helper range when a value should not be plotted. This is useful when the source contains zeros that serve as placeholders, but the source itself must remain intact.

For instance, suppose a service records daily completed jobs, and a zero means the team completed no jobs. Removing that point would conceal a real outcome. If instead the system writes zero whenever a day has not yet been imported, plotting those zeros could suggest a sudden collapse in activity. In that case, clarify the meaning with the data owner and use a helper range to omit only the placeholder values.

Common Problems and Checks

The chart still drops to zero. The series may still point to the original column. Use Chart Design > Select Data and verify the series values refer to the helper column.
The formula marks blanks too. The formula shown checks that a cell is numeric before checking whether it equals zero. If your source contains formulas returning zero, those are numeric results and will be omitted, as intended by the formula.
A zero-looking value remains plotted. It may not be numeric zero. Check whether it is text, a small nonzero decimal displayed with rounded formatting, or a formula result that is not exactly zero. Increase the displayed decimal places or inspect the formula bar before changing the rule.
The chart has an unexpected gap. A gap can be the correct representation of an omitted observation. Review the empty-cell options, and decide whether connecting the surrounding values would imply continuity that the data does not support.
Some points disappear after hiding a column. In Hidden and Empty Cells, check whether the option to show data in hidden rows and columns is selected. Chart behavior depends on that setting.

Also consider the vertical-axis scale. If the chart still looks compressed after removing zeros, the remaining values may be close together relative to an axis that starts at zero. Changing the axis minimum can make differences easier to see, but it can also exaggerate them. Axis formatting does not omit zero points; it only changes the visual scale.

Alternatives to a Helper Column

You can delete zero entries or replace them with blanks in the original data, but that edits the source and may affect formulas, audits, or later charts. Use that approach only if the zero was an erroneous placeholder and the data policy calls for a blank instead. A helper column is usually safer because the original values remain available for checking.

For a one-off chart, you can also filter out rows, but that removes the entire row from the displayed dataset, not just one series value. It may be unsuitable when other columns on that date contain valid observations. A helper series gives more precise control and is easier to reuse when the chart refreshes.

In Excel for the web, the formula approach works in a worksheet, and you can create or edit charts with the available chart controls. Some chart-formatting dialogs and options may differ from the Windows desktop version. On Mac, formula syntax is the same, while ribbon placement and keyboard shortcuts can differ. If the Hidden and Empty Cells controls are not available in your version, the helper formula still provides the central solution.

Frequently Asked Questions

Can I make an Excel line chart ignore zero without changing the source data?
Yes. Use a helper column with a formula that returns NA() for numeric zeros, then point the chart series to that column. The original values remain unchanged.

Does Excel treat a blank cell as zero in a line chart?
Not necessarily. The chart’s Hidden and Empty Cells settings determine whether empty cells appear as gaps, as zero, or as connected points. Formula-generated empty strings can also behave differently from truly empty cells, so check the chart rather than assuming they are interchangeable.

Will #N/A show as a point on the chart?
Excel does not plot #N/A as a regular numeric data point. It may create a gap in a line, so review the result if you want neighboring points connected.

Can I hide zeros with a custom number format?
A number format can change how a zero looks in a cell, but it does not change the underlying numeric value. The chart can still plot that zero. Use a helper value that returns NA() when the point must be omitted.

Can I use this method for several lines?
Yes. Create a helper column for each series whose zeros should be omitted, and set each chart series to the corresponding helper range. Keep a series unchanged if its zeros are meaningful and should remain visible.

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.