2

I'm collecting data for various series (A-G), at sporadic intervals. I want a line chart, with one line for each series, that is connected even if there are intervals where no data was collected for that series.

So far no matter how I do it, it's just displaying the disconnected data points.

Sample data:

 Fetch_Date A   B   C   D   E   F   G
    5/12/11 1483                        
    5/13/11     146                 
    5/16/11         2036    731         
    5/17/11                     1091    
    5/22/11                 208     1154
    7/8/11              277         
    7/11/11     113 1983        201 1096    
    7/13/11                         3047
    7/21/11 1846                        
    8/15/11     71      792 150 1012    
    8/19/11         1418                
    8/22/11                         2451
Improve this question

2 Answers 2

Reset to default
3

If you replace your blanks with #NA you will get a nice continuous graph.

You have two options:

So you could have your raw data set in one sheet/location in the sheet and then use this formula to repeat all the data

=IF(correspondingCell="",NA(),correspondingCell)

Or you could do a find and replace where you leave the find blank and the replace would be "=NA()". (no quotes)

All the data would end up looking like this either way:

A   B   C   D   E   F   G

1483    #N/A    #N/A    #N/A    #N/A    #N/A    #N/A
#N/A    146 #N/A    #N/A    #N/A    #N/A    #N/A
#N/A    #N/A    2036    #N/A    731 #N/A    #N/A
#N/A    #N/A    #N/A    #N/A    #N/A    1091    #N/A
#N/A    #N/A    #N/A    #N/A    208 #N/A    1154
#N/A    #N/A    #N/A    277 #N/A    #N/A    #N/A
#N/A    113 1983    #N/A    #N/A    201 1096
#N/A    #N/A    #N/A    #N/A    #N/A    #N/A    3047
1846    #N/A    #N/A    #N/A    #N/A    #N/A    #N/A
#N/A    71  #N/A    792 150 1012    #N/A
#N/A    #N/A    1418    #N/A    #N/A    #N/A    #N/A
Improve this answer
3
  • I guess if you want to go one step further/if you need to actually look at the data and make sense of it you can use conditional formatting to turn the font in all those cells with #N/A in them to the background color so they kind of disappear from sight while retaining the NA value.
    – Brad
    Commented Sep 16, 2011 at 21:01
  • I'm on Excel 2007, this doesn't work, will be obliged if you could tell how to do it in 2007? Also, the connect data points with a line option is grayed out. I'm using Line chart.
    – 0fnt
    Commented Mar 25, 2012 at 15:41
  • When I wrote this answer I was using and had in mind 2007. I'd say try again and if you really can't make it work, write up a new question with what you tried and link to this example.
    – Brad
    Commented Mar 25, 2012 at 15:58
0

If those cells are truly blank, go to the Edit Source Data dialog, click on the Empty and Hidden Cells button at the bottom, and for Blank Cells, choose Interpolate, which will draw a line connecting the points on either side of the missing data.

Improve this answer
2
  • I probably should have noted I'm using Office 2003 - don't see that option, is that in a newer version?
    – DoraTrix
    Commented Sep 20, 2011 at 21:04
  • In 2003, select the chart, go to Tools menu > Options > Chart tab, and choose the Interpolate option near the top. Brad's NA() approach works as easily, and if those cells are formulas that return "", Brad's answer is the way to go.
    – Jon Peltier
    Commented Dec 1, 2011 at 16:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .