MS Excel - adding line to some series in stacked bar chart

Question

I built the following chart after some googling:

It has two levels of category axes, the bars you see are stacked bar chart types. You can find the file with the data and the charts here if you would like to see the details.

My concern is the line for A. The data I'm processing is collected on several dates with unvarying categories. The solid and striped bars of the same color measure different things for the same category. What you don't see about the construction of the chart is that it thinks that each bar (and each gap) is in its own category, and I just let the gaps (which correspond to empty rows in the data) and striped bars have empty cells as their category reference so the secondary category axis doesn't clutter.

I would like to add lines connecting all solid bars of a category (data may be collected on arbitrarily many dates). How I went about it is that I duplicated the data for the solid bars, used those duplicates to add a second series of stacked bars on top of the solid ones, then changed the type of that duplicated series to line chart. I then added dummy values for the categories where no solid bars show to make a straight line (using a linear function). If I drop the markers for the line, everything looks fine.

Now what I don't like about this is that if I filter the chart to only show data concerning series A and B, for example, the values for the line will no longer belong to a linear function, and it will have dents:

So, what I actually would like to have is a line that only plots itself in the subcategory A within every main category, and another line that does the same in B and so on. I seem to remember earlier versions of Excel (2003 or so) had the functionality that I could connect all data points of a series with a line, but I'm not sure that actually was the case.

Is there a way to do what I want without having to VBA the values for the line every time I filter? I've never used VBA before (I do know how to program, but I was hoping there was an automatic way to achieve this).

---

Other things I tried: just using 0 entries in all other categories produces this:

Restricting the data range for the line chart to only the values relevant for the solid bars for A, I get this (line now pink to make it stand out):

Answer 1

Right-click the chart and select "Select Data". In that dialog click the button "Hidden and empty cells" and then tick the option "Connect data points with line".

Now you can remove the values in column K, except for the ones in row 2 and row 14. The line now only has two data points, connected with a line.

If you now hide the rows for C and D, the line still connects the two A columns nicely.

In Excel 2007 you cannot access the "Connect data points with line" option unless all series types are lines. You can start with a line chart, choose this option, then change selected series to columns. Alternatively you can temporarily change columns to lines, choose the "Connect data points" option, then change back to columns.