PivotChart broken lines

I have a PivotChart where the data comes from a large tables of rows, organized as a sort of database.

I'm trying to organize the PivotChart according to the following rules:

1) There should be one line on the chart, and one legend entry, for each unique value of field "Rank".
2) The X-axis should correspond to a field named "Rating".
3) The Y-axis should correspond to a field named "Score".

I have this working almost perfectly, except for a slight problem - For every possible unique value of the X-Axis, there is often one or more of the "ranks" (lines) that does not have a datapoint for that rating value. This results in the line breaking. It will connect two adjacent markers on the chart as long as, for a single line, that line has datapoints at both values. But whenever there is a gap, it just stops the line.

Is there a way to get it to always connect adjacent markers? I've attached a picture demonstrating the problem.

I want all the green triangles to always be connected, all the blue diamonds to always be connected, and all the red squares to always be connected.

Is this possible?

Hi bruiser,

A sample file would sure be a help to see if our suggestions work.

Have you tried to filter out all zero values in your Value data?
Have you tried a different type of chart?

I'd try both of the above on my data and then give us a short sample and let us work from there.

Sure, sorry about that. I've attached a workbook to this post.

I haven't tried filtering out zero values, because there actually are no zero values. Just values for which there is no data at all. But they still need to be on the chart, because other lines do have datapoints for those values.

Hi Bruiser,

I don't feel like I've made any progress on this puzzle. Find the attached where I tried
1. A scatter line using a values paste of the pivot table data.
2. A bar chart
3. Showing the data in a variety of ways without success.

Perhaps Andy Pope our resident graph guru can jump on this.

My next step was going to add values in the data to fill in the holes. I don't think that is what you want. I'd go with a bar chart or perhaps a stacked bar to show your results.

I could live with adding values into the missing places to fill in the holes. I was eventually going to disable all the markers anyway so you wouldn't notice. But this would be pretty difficult I think, because the values would have to be perfectly interpolated across the range. I think it would be more likely for it to be possible to connect the markers than it would be to do something like that.

This spreadsheet is being used to record data of an ongoing process. Eventually, I will have datapoints for every value on the x-axis for every line, but it will take months for that to happen. Once it does, this won't be an issue anymore. But still, since it's a long way out, it's kind of annoying to look at the graph all broken up like this.

bruiser, please don't quote whole posts. It's clutter and creates cleanup work

Click the chart, click Design - Select Data - Hidden and Empty Cells - Connect data points with line

See attached. Is that what you want?