Excel Line Chart Zero Values: What to Change

Excel plots a numeric zero as a real data point, so a line chart will show it on the zero line unless you change the chart’s source data or how empty points are displayed. To omit zero points, use a helper column that returns #N/A for zero values; to leave a break in the line or connect across the missing point, adjust the chart’s empty-cell settings.

Why Excel shows zero values

A line chart represents the values in its source range. If a cell contains 0, Excel treats it as a measured value—not as missing data—and plots it at the zero position on the vertical axis. If the chart’s axis minimum is above zero, the point may fall outside the visible plot area, but the value has not actually been removed.

Zeros may be entered directly, returned by a formula, or produced by a calculation whose result is zero. These situations can look different in the worksheet but generally give the chart a numeric zero to plot. For example, a formula that calculates no sales as 0 still gives the chart a data point at zero.

First decide what the zero means. If it is a genuine measurement, showing it may be important: hiding it could make a drop in activity or a value of zero harder to notice. If zero is being used as a placeholder for “not recorded,” “not applicable,” or “no result,” excluding the point can make the chart more representative of the data.

Hide zero points with a helper column

A helper column lets you preserve the original data while supplying different values to the chart. In Microsoft 365 for Windows, add a new column beside the source values, enter a formula that changes zeros to #N/A, and use that new column as the chart’s value series.

With dates in A2:A7 and measured values in B2:B7, enter this in C2, then fill it down through C7 and chart column C against the dates in column A: =IF(B2=0,NA(),B2). If B2 contains 0, the formula returns #N/A; if B2 contains 12, it returns 12. For example, source values of 12, 0, and 18 become 12, #N/A, and 18 in the helper column.

To change the chart’s series, select the chart and choose Chart Design > Select Data. In the Select Data Source dialog, select the series and choose Edit, then set its Series values to the helper-column range. Keep the date or category labels pointing to the original labels in column A. If the chart is based on an Excel table, adding the helper column to the table may make it easier to keep the chart range updated as rows are added.

The advantage of this approach is that the source values remain intact for calculations, auditing, and other reports. You can also label the helper column clearly, such as “Chart values,” so another person understands why it contains errors. The visible #N/A results are intentional; if you do not want them displayed in the worksheet, you can format or hide the helper column, while keeping it available to the chart.

Choose whether the line has a gap

Removing a zero point and deciding what happens to the line around it are separate choices. A gap shows that the chart has no plotted value for that category. Connecting the surrounding points suggests a continuous trend through the missing category. Neither choice changes the underlying data, but each can imply something different to a reader.

To change how the chart handles empty positions, select it and choose Chart Design > Select Data > Hidden and Empty Cells. In the dialog, choose whether empty cells appear as Gaps, Zero, or Connect data points with line. If the dialog offers Show #N/A as an empty cell, select it when you want the helper-column errors treated like missing points. The available options can depend on the chart type or Excel build.

Choose Gaps when a missing value should visibly interrupt the line.
Choose Connect data points with line when you want a continuous line between the available values. This can imply a trend across the omitted category, so use it only when that interpretation is reasonable.
Avoid Zero if your goal is to remove placeholder zeros; that setting explicitly plots empty positions at zero.

For example, if Monday is 12, Tuesday is an unavailable reading, and Wednesday is 18, a gap leaves the Tuesday position unconnected. Connecting the points draws a segment from Monday to Wednesday. The segment does not mean Excel has calculated a Tuesday reading; it is simply a visual connection between the neighboring values.

Why returning an empty string can be unreliable

A common alternative is to make a formula display a blank instead of zero. A worksheet cell that looks empty is not always a genuinely empty cell: a formula can return an empty text string, and chart behavior with formula-generated blanks can differ from the behavior of a truly empty cell. Depending on the chart and settings, the point may still be treated like zero rather than omitted.

For that reason, #N/A is usually the clearer choice when you want a formula-driven chart to skip a value. It signals that there is no plottable number for that point. If you prefer an empty-string formula, test it in the actual chart and inspect the result rather than assuming that a visually blank worksheet cell will be ignored. Keep in mind that changing the chart’s empty-cell setting does not necessarily make every formula-generated blank behave exactly like an unused cell.

Check whether the zero is real or just displayed

Before changing the chart, select the source cell and inspect its value and formula bar. A cell might show a dash, a blank, or a rounded number because of its number format, even though it contains a numeric zero. Conversely, a cell that appears blank may contain a formula. The chart uses the underlying value, not simply what the cell looks like.

Number formatting can hide zeros in the worksheet without removing them from the chart. For example, a custom number format may display zero as an empty-looking cell, but Excel still has a numeric zero to plot. This can make a chart seem inconsistent with its data table. Formatting is useful when you want a cleaner worksheet, but it is not a dependable way to exclude points from a chart.

Also check for formulas that convert missing inputs to zero, such as an IF formula with 0 as its result when a condition is false. If zero means “not entered yet,” it may be better to change the formula or keep a separate status indicator. Do not turn genuine zero readings into missing points just to make the line look smoother.

Other ways to change what the chart shows

A helper column is generally the most controlled option, but other approaches may suit particular cases. If a whole row or category should be omitted, filter the source data or exclude that category from the chart’s data range. Be aware that chart behavior for filtered or hidden rows depends on the chart’s settings. In Select Data > Hidden and Empty Cells, check whether hidden rows and columns are being plotted if the result does not match your expectations.

Changing the vertical axis minimum can make zero points fall outside the visible chart area, but it does not remove them from the series. This may be appropriate when the scale is intentionally focused on a higher range, but it can exaggerate differences and conceal meaningful values. Label the axis clearly and avoid using this as a substitute for deciding whether zero is valid data.

You can also remove a marker’s visibility by formatting the series, but that affects markers rather than the value itself. The line may still pass through the zero point. This is useful when you want a cleaner-looking line while retaining the data, not when you need to exclude zero values from the plotted path.

Common troubleshooting checks

If the zero point remains after adding a helper column, check that the chart series now references the helper range rather than the original values. Also confirm that the formula was filled down for every plotted row and that the chart’s category labels still match the intended dates or labels. A chart can retain its old series reference even after a new column is added.

The point is still at zero: inspect the series range in Chart Design > Select Data. It may still use the original column.
The line breaks unexpectedly: choose Connect data points with line if connecting missing points is appropriate, and check the option for treating #N/A as an empty cell if available.
A formula looks blank but plots at zero: verify whether it returns an empty string, a numeric zero, or a genuine empty cell. Try #N/A for points that should not be plotted.
Hidden rows still appear: review the Hidden and Empty Cells settings and the chart’s source range.

On Excel for Mac, the chart concepts are the same, but ribbon labels and dialog placement can vary by release. Select the chart and look for the chart design or data-selection controls, then locate the Hidden and Empty Cells options. In Excel for the web, chart editing controls may be more limited; if an option is unavailable, open the workbook in the desktop app to adjust the chart settings.

Frequently Asked Questions

How do I make Excel skip zero values in a line chart?
Use a helper column that returns #N/A when the source value is zero, and set the chart series to use that helper column. Decide in the chart’s Hidden and Empty Cells settings whether the missing point should create a gap or connect the neighboring points.

Does Excel treat a blank cell as zero in a line chart?
Not always. A genuinely empty cell can be shown as a gap, as zero, or connected to neighboring points depending on the chart setting. A formula that displays a blank is not necessarily treated the same way as a genuinely empty cell.

Can I hide zero values without changing the source data?
Yes. Keep the original values and create a helper column for charting. That way, your calculations and source data remain unchanged while the chart uses a version that skips zeros.

Will formatting zeros as blank remove them from the chart?
No. Number formatting changes how a value appears in a cell; it does not change the underlying numeric zero. Use a chart-specific helper range or another method that changes the plotted data.

How do I keep the line connected when a zero is omitted?
Use a helper range that marks omitted points as #N/A, then check Chart Design > Select Data > Hidden and Empty Cells for the option to treat #N/A as an empty cell and connect data points with a line. If those options are unavailable, Excel may not be able to produce that exact behavior with the current chart setup.

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.