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, the line drops to zero. How do I get the line to simply stay "blank" 

Charting
excel

1 Reply

  • Jason_M's avatar
    Jason_M
    Brass 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.