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've got data based on over 50+ years for various products. Not all products have data for each year, so the line is not always shown from point to point - Excel bug or is there a work around?

I've created a line and X-Y scatter charts to show the movement (quantity sold) of these products over the years. It works well, except where the data points are too far apart i.e. 1965 and then 1975. For some reason there is no line.

It's not perfect data because of the missing years, but I can live with that, I just want to see the trend, and not just sporadic dots; squares or crosses.

Any help or links greatly appreciated.

Mike

share|improve this question
add comment

3 Answers

up vote 10 down vote accepted

Hopefully the following image is self-explanatory:

enter image description here

The complete command sequence in Excel 2007 is:

  1. Select your chart
  2. Select Chart Tools → Design on the Ribbon
  3. Select "Select Data"
  4. Select "Hidden and Empty Cells"
  5. Select "Connect data points with line"
  6. Select OK.
share|improve this answer
add comment

You probably want an X-Y scatter plot with lines. X = year, Y = quantity sold.

A normal line chart treats the X values as labels whereas an X-Y scatter plot treats the X values as true values.

share|improve this answer
1  
I've tried the X-Y as well but the same probelms with the missing lines –  RocketGoal Feb 17 '11 at 8:59
add comment

Have you tried adding a TrendLine? Right click on your graph and select "Add a trendline...". Select the trendline you want and voila!

share|improve this answer
    
I've looked at the trendline option, but unfortunately it's not suitable. The closest one is the "2 x average" choice but that isn't close enough - line does not disect all the points. –  RocketGoal Feb 17 '11 at 9:00
add comment

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.