Interpolate empty cells in Excel line charts

October 7, 2007 Jesper Excel Go to comment

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:

  1. Click your Line chart to activate it.

  2. 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% [?]

5 Responses to Interpolate empty cells in Excel line charts

  1. Manish | #1
    On August 10, 2010 7:21 am

    thanx It is working with #N/A….

  2. SA | #2
    On June 17, 2010 12:23 pm

    Net has proven to be my saviour time & again. This time its no different. Thanks!
    :-)

  3. James | #3
    On June 16, 2010 2:23 pm

    Thank you! Been hunting everywhere for how to do that. :-)

  4. Marcus | #4
    On June 9, 2010 4:04 pm

    Leeann: If you do not want the data plotted, simply leave the cell empty.

    Thanks for explaining how to interpolate data!

  5. Leeann | #5
    On May 17, 2010 7:57 am

    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?