Forum Discussion

DRTE's avatar
DRTE
Copper Contributor
Sep 28, 2026

How to stop Line Chart from showing zero for a blank cell

I have a series of simple line chart showing historic annual data. In some cases, when the cells are blank, the line is also "blank" - i.e. is not shown. But in other charts, when the cell is blank...
Charting
excel
Jason_M's avatar
Jason_M
Iron Contributor
Sep 28, 2026

Check the empty-cell setting on each affected chart:

  1. Right-click the chart and choose Select Data.
  2. Click Hidden and Empty Cells.
  3. Under Show empty cells as, select Gaps, then click OK.

That should stop genuinely empty cells from being plotted as zero.

If it still happens, check whether those “blank” cells contain a formula returning "". They look empty, but Excel may handle them differently from cells with nothing in them. For missing chart data, return NA() instead, for example:

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

If your Excel version offers Show #N/A as an empty cell in the same chart settings, enable it and keep Gaps selected.

NA() displays #N/A in the worksheet, so use a separate helper column for the chart if you want your original table to keep looking blank.

DRTE's avatar
DRTE
Copper Contributor
Oct 07, 2026

Thanks for the tip.

I did as you suggested.

But even though the cell appeared to be blank, I noticed that when I held the cursor over that data point in the chart it was showing as "-". It did NOT show this on the cell itself. Nor was there any formula or calculation for that cell. But when I simply clicked Delete for that cell, the chart changed to show a gap.