Missing lines between data points that are far apart

[NeverHappyMike]

Hi


I'm trying to figure out how to make an X-Y Scatter include a line between data points even if they are far apart - 5, 6, 7, 17 , 29, 32...


I have a dataset that has products sold over years. Unfortunately, there are missing data points for some products over a number of years, i.e. 1965, 1966, 1976, 1979, 1986 - but I can live with that problem.


What I need is for the lines to go between the data points no matter how far aprt they are. I want to see more than just dots and squares.


Any help would be greatly appreciated - it's driving me nuts!


Mike.

 

[Hui]

Have you tried it as a Line Chart, Not a scatter chart

You will loose your horizontal scale

 

[NeverHappyMike]

I've tried both types - line and X-Y, and the same problem persists.

 

[Hui]

Can you post the file somewhere ?

http://chandoo.org/forums/topic/posting-a-sample-workbook

or email me

email is at bottom of http://chandoo.org/wp/about-hui/

 

[Luke M]

Are there gaps in your actual data layout?

 

[NeverHappyMike]

Link below to the data. It's in Google Docs. It did have an example of the X-Y scatter chart, with it's gaps, but that's been removed for some reason.


Anyway, if you create an X-Y you'll see that the lack of data in all years means there are gaps.


Any advice on how to add the lines between all dots/crosses/cricle/etc greatfully appreciated.


Yearly Data


Mike.

 

[Luke M]

With the chart selected, go to Tools - Options, Chart, plot empty cells as "Interpolated".

 

[NeverHappyMike]

@Luke M.

That sounds simple...but I'm struggling in Excel 2007 to find the options you have mentioned.


After the chart is selected I have the basic ribbon options of Design/Layout/Format. Where does it come under this?


Thanks

Mike.

 

[vijaySharma]

Mike,


Select you chart

On the ribbon, on the Design Tab under Chart Tools

Click on the Button Select Data

In the Select Data Source dialog box,


Click on Hidden and Empty Cells


choose from Show Empty Cells as

Gaps

Zero


HTH


~Vijay Sharma

sharma.vijay1-at-gmail.com

+91-9811996454

 

[NeverHappyMike]

@Vijay Sharma

Many, many thanks. For my needs I've chosen Connect with line, but that hidden gem is one to remember. Therefore I've copied and pasted you answer into this one so people can see what the answer was.

• Select you chart
  • On the ribbon > Design Tab (under Chart Tools)
    • Click on the Button Select Data
      • In the Select Data Source dialog box,
      Click on Hidden and Empty Cells
    
    Choose from Show Empty Cells as
  
  Connect data points with line

Victory!


Thanks Mike.