Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com Sep 16 '11 at 18:54

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 3 down vote accepted

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
share|improve this answer
    
Works great, thanks! –  Pastymage Sep 16 '11 at 20:47
    
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 Sep 16 '11 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. –  user18151 Mar 25 '12 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 Mar 25 '12 at 15:58

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.

share|improve this answer
    
I probably should have noted I'm using Office 2003 - don't see that option, is that in a newer version? –  Pastymage Sep 20 '11 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 Dec 1 '11 at 16:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.