Combining chart types

Wow, it’s been a long time. This is a post I had planned for some time ago but didn’t get around to until now – as many All Black supporters might say, about bloody time…

In a made-up scenario I have two rates to report on, Metric 1 and Metric 2. An indicator of good performance is that Metric 2 sits 5-10% above Metric 1. So when I plot the monthly data, as well as the two Metrics time series I’ll add in another series called Difference, that will be calculated as Metric 2 – Metric 1. I’ll also add in the constant series Target 1 (5%) and Target 2 (10%), and what I want to see is the Difference series sitting between these two targets. Here’s what that looks like:

Now, I don’t know what you think of that, but to me it’s confusing. The difference between Metric 1 and 2 and where it sits in relation to the 5% and 10% marks is the key indicator I need to follow, but both Metric 1 and 2 are also important to provide context. Putting them all together on the same line chart makes it harder than it needs to be to see what the indicator is doing.

Thankfully, there is a much simpler way to present this, which avoids the need for the eye to track all five time series together on the chart, and also manages to signal much more clearly where the key indicator is in relation to its target. To do this I can use the relatively simple expedient of combining different chart types.

First, I right-click the Metric 1 time series, select ‘Change Series Chart Type…’ and pick out the ‘Stacked Area’ chart type:

I also format the fill of the area series to a red colour:

Next, I make the same change of chart type to the Target 1 time series. I change the colour to orange (or amber if you prefer), and the name of the series to ‘0-5% above’:

I remove both the Difference and the Target 2 time series from the chart, and add the Target 1 series in again, by copying the data range from the worksheet, selecting the chart and pasting. Once that’s done, I again change the chart type of the new series to stacked area, set the fill colour to green and rename it to ‘5-10% above’. I also change the line colour of the Metric 2 series to a dark blue to provide a little more contrast against the area charts:

And that’s done. We now don’t need to show the ‘Difference’ series at all, as the magnitude of the difference between Metric 1 and 2 is clear without it. I also find the chart a lot simpler and more visually appealing. I should note in passing that I very rarely use the stacked area chart type, but it makes sense to me in this context.

Combining chart types like this is a simple and effective but under-used method – I guess that’s one side effect of the chart wizard, people assume that once the chart type is selected, that’s it. I first came across the idea at Jon Peltier’s site (this page has a lot of cool examples), it’s been a handy tool ever since.