0

I built the following chart after some googling:

enter image description here

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:

enter image description here

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:

enter image description here

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):

enter image description here

2

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. enter image description here

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.

  • Fantastic, exactly what I needed! I can just delete column K altogether and just reuse the column for solid A since I put the zeroes there only to show one of the charts. – G. Bach Apr 15 '15 at 13:14
  • Just an update, this does not work in Excel 2007 or older - but that's a bug there, not intended behavior. – G. Bach Apr 15 '15 at 19:49
  • Should work in the older versions, there's nothing new or special about this technique. – Jon Peltier Apr 16 '15 at 8:08
  • @JonPeltier I tried it in Excel 2007, the option was grayed out. According to this, this is the case whenever not all data series are plotted as lines - unless I misread it, of course. They offer a VBA-based workaround, too. – G. Bach Apr 16 '15 at 23:21
  • 1
    Ah, I see what you mean. It can be done in 2007 with a little foresight. If you know you're going to need this, then construct the chart with lines only at first, change the setting to 'connect with lines', then change the series types accordingly. The VBA alternative sets the same property that the UI would do. – Jon Peltier Apr 18 '15 at 12:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.