Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 Sep 16 '11 at 18:54

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

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


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. – 0fnt 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

You must log in to answer this question.

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