Show gaps instead of zeros in an Excel line chart

Decide whether the value is zero or missing

A recorded sale of zero is data. An unreported sale is missing data. Plotting both as zero creates an apparent drop that your observations do not support.

Set empty cells to gaps

1. Select the line chart and open Chart Design → Select Data.
2. Choose Hidden and Empty Cells.
3. Under empty-cell display, select Gaps.
4. Confirm that the missing day breaks the line while the true zero remains at zero.

Connect data points with line is a different presentation choice: it bridges missing observations. Choose intentionally rather than using it to hide missing values.

Handle formulas returning an empty string

A formula result of "" is a zero-length string, not a genuinely empty cell. Some charts plot it as zero. You can use a dedicated chart helper column returning #N/A instead:

=IF(A2="",NA(),A2)

This preserves a numeric zero. Treatment of #N/A depends on your Excel version and chart settings. If available, inspect Show #N/A as an empty cell and confirm the plotted result rather than assuming identical behavior across versions.

Check the final chart

Inspect a missing observation, a real zero, and a hidden source row. Explain missing data in a chart note so readers understand why the line is broken.

Download the example

UTF-8 CSV data. Import with Data → From Text/CSV. Set identifier columns to Text when leading zeros matter. Charts and formulas are added using the steps in this guide.