Comment from Alistair Knock
Time: Wednesday, February 11, 2009, 8:32 am

Regarding multiple legends in a chart, it’s possible to do this dynamically by overlaying a transparent copy of the chart on top of itself, but in practice it isn’t worth the hassle of trying to get things pixel perfect. Set up the chart with all series and legends displayed, move legend on top of the chart, maximise the plot area, set the chart and object backgrounds to be transparent, turn off any ‘auto’ checkbox you see in the axes settings, then make a copy of the chart. Delete half the series from one and the other half from the other, and overlay one on top of the other (using the top left of the window helps here).

Both charts will then update in accordance with data changes, but you usually end up with some of the axes labels drifting away from other other… you could turn off everything on one chart, but on a complex graph there’s a niggling fear that the plotted points will do noticeably so as well.

Comment from derek
Time: Wednesday, February 11, 2009, 12:43 pm

Would it not be easier to have the top chart with no graph area border or fill, but a solid plot area, and the bottom chart with a solid border and fill, but a slightly smaller plot area hidden under the plot area of the top chart? No deleting of everything required, just shrinking the one chart plot area until it’s completely hidden under the other. The bottom legend must stay away from the top plot area, though.

Comment from Matt Healy
Time: Wednesday, February 11, 2009, 10:17 pm

One of my favorite tricks is to use one or more Text Box objects instead of legends on top of a Chart, because Text Boxes are much more flexible. Not only can they be formatted however one desires, but also a Text Box can have DYNAMIC content. Click on the Text Box, then type a cell reference into the Formula Bar and the value of that cell will be displayed in the Text Box, and then of course any formula one desires can go into that cell. I like to use Forms widgets like spinners or scrollbars in conjunction with OFFSET formulas so the contents of the Text Box change automagically.

Comment from derek
Time: Thursday, February 12, 2009, 5:21 am

One headache I have regularly that y’all might be able to give me some ideas about is the opposite of multiple legends in one graph: one legend for multiple graphs. On one level it’s as easy as deleting the legends from all but one graph, but that’s often visually unbalanced. I sometimes end up creating a picture object to sit in a convenient spot in the middle of the group.

Comment from derek
Time: Thursday, February 12, 2009, 7:36 am

So obvious now you mention it!

One other technique you (and Matt) might be interested in that I’ve used in the past is extra data series, for scatter charts or bubble charts. This was for when I wanted:

a) to comment on the meaning of error bars in a scatter chart. Making extra series lets me show and label a sample set of error bars.

b) to give a scale to bubble charts. Two or three sample bubbles would be labelled with their sizes, to enable the viewer to interpret the bubbles in the chart.

c) where I’ve used data labels instead of Excel symbols in scatter charts. Data labels can be much more complex in meaning than the small simplistic set of symbols available in Excel. But then I have to craft a custom legend for them.

d) as above, but where I’ve resorted to Autoshape drawings as symbols. This is fraught with potential for inaccuracy the way Excel draws shapes, but where I feel confident it works, I want a way to show the legend. Excel’s own legend facility produces awful squashed things in place of the proper shapes.

Comment from Paul
Time: Tuesday, October 13, 2009, 4:33 am

Hello Jon, I am rather confused about something I thought maybe simple to do that is proving not and I am hoping from having read your articles here that you maybe able to assist.

When creating a simple line chart, I have 5 series of data, to the left of the chart is an area (using data validation by list) which allows the user to choose what they want to appear on the chart. This is done by 5 drop down boxes.

If the user chooses a blank because they only want to show for example 4 series of data then choosing the blank obviously removes the data from the graph. However it still leaves the series line in the legend. So what I need to try and find out is how to remove the line series when a blank is selected and if something is selected then to put the series line back into the legend…….any ideas ?