Symptoms
When you create a Line chart in Microsoft Excel, the chart may contain a gap in a line.
Cause
This issue may occur when the data range for the Line chart contains a blank cell.
Workaround
To work around this issue, use one of the following methods.
Method 1
Plot empty cells as interpolated. To do this, follow these steps:
Excel 2000 - 2003
-
Click your Line chart to start it.
-
On the Tools menu, click Options.
-
On the Chart tab, click Interpolated, and then click OK.
Excel 2007
-
Select the chart, and right click anywhere within the chart.
-
Click Select Data and then click Hidden and Empty Cells.
-
Click to select Connect data points with line, and then Press OK twice.
Method 2
Use the NA function in the blank cell of the chart's data range. To do this, use one of the following methods:
-
In the blank cell, type #N/A.
-
In the formula bar for the blank cell, type =NA().
-
If the cell range for the Line chart uses a formula to obtain values from a different cell range, and if you do not want the Line chart to plot 0 (zero), type the following formula in the formula bar:
=IF(SUM(range)=0,NA(),SUM(range))Note range is the cell range that is outside the cell range for the Line chart.