Key words: Excel, interpolate missing data, line chart, skip empty cells
Versions: Excel 2003, 2002 and 2000
When you create a Line chart in Microsoft Excel, from data containing blanks cells you will notice that the line graph contains gaps for the missing data. Sometimes you may wish to plot missing data as interpolated and this can be achieved in two ways.
Method 1
In this method you tell Excel to plot empty cells as interpolated.by following these steps:
-
Click your Line chart to activate it.
-
On the Tools menu, click Options –> Chart tab –> Interpolated –> OK
Method 2
This method uses the NA function to tell Excel to interpolate the missing values. This can be done using one of the following methods:
-
In the blank cell, type #N/A.
-
In the formula bar for the blank cell, type =NA().
If the data series for the Line chart uses a formula to calculate values from a different cell range, and if you do not want the Line chart to plot 0 (zero), you could use following formula to plot missing data as interpolated:
=IF(SUM(range)=0,NA(),SUM(range))
Popularity: 16% [?]
thanx It is working with #N/A….
Net has proven to be my saviour time & again. This time its no different. Thanks!

Thank you! Been hunting everywhere for how to do that.
Leeann: If you do not want the data plotted, simply leave the cell empty.
Thanks for explaining how to interpolate data!
what if i do not want the data to be plotted, there are several #N/A between data, i do not want this data plotted, I want a gap?