Forum Discussion
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...
Check the empty-cell setting on each affected chart:
- Right-click the chart and choose Select Data.
- Click Hidden and Empty Cells.
- 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.