In The Problem with Marimekkos, I used a panel chart to separate the data from a single complex and hard-to-decipher chart into four much simpler charts, following the lead of Stephen Few of Perceptual Edge. I used a trick to place two separate legends into the bottom two panels of the chart.
I introduced a method for placing two legends into a chart in Legend Entry Tricks in Excel Charts, and a few additional tricks were discussed in the comments of that post. These techniques were not what you might call elegant, requiring non-dynamic graphical objects or superposition of multiple charts.
In this post’s panel chart, the legends are not really official Excel chart legend objects. But if there’s one thing I’ve learned, it’s that I can usually coerce one easy to use chart element to substitute for another which is more difficult to deal with.
The clever reader may recognize the legend keys and legend entries in the panel chart as markers and data labels of an XY series added to the chart. The XY series and labels use the following data. The X values in the first column indicate that the points will be aligned above the 14th and 35th columns of the lower panels. To get these numbers I merely counted columns or spaces between groups of columns, starting with 1 for the half-space at the left edge of the chart. The Y values correspond to the vertical scale of the bottom left panel. These values can be adjusted to fine-tune the positions of the fake legend entries.
Copy the first two columns of this data range, select the unlabeled chart. . .
. . . and use Paste Special to add the data as a new series, with category data in the first column. I had already added a handful of XY series to the chart to produce the four vertical scales in the chart, so my newly added series was automagically added as an XY series. If your series was added the the chart as a column series, right click on it, choose Chart Type, and select an XY type. Then you probably have to format the new XY series to use the primary chart axes.
Run, don’t walk, to Rob Bovey‘s Applications Professionals web site, and download his Chart Labeler. Rob calls it the XY Chart Labeler, but it works for any chart type that supports data labels. It’s free, easy to install, and easy to use. Add the labels from the third column of the data range shown above to the points of the XY series.
Finally, format the XY series. Remove the connecting line, and fill each marker with the color of the bars that it represents.
Pretty sneaky! That trick alone is worth the price of membership to this blog.