The problem of too many lines on a time series line chart

The challenge is how to show several years worth of data in a time series using lines. Even William Playfair struggled with this one















There are two approaches which might help us here

• Progressively fade the years out as they get further into the past
• Band several years together to produce a background range

This article shows how to do both of these using Excel.


The problem

We will use some data from my world - it  does not really matter what it is. We have weekly totals for five successive financial years

So we highlight the five years and insert Excel's default line chart, which produces this:

It's a horrible mess. Obviously we will work on it a bit: Move the legend to the bottom, stretch it out

It is still extremely hard to follow. The problem is that there are too many lines criss-crossing each other, and the colour of the lines has no real meaning

I regularly see charts like this  - often with even more lines. The analyst has simply trusted to Excel's standard chart gallery and colour schemes. Excel has blown them a raspberry. Excel is a brilliant software package, but you often have to twist its arm to get it to do what you need.

Progressively fade out earlier years

This approach tries to put meaning into the colour of the lines. Most of us will probably agree that it is worth looking at the position at the same point last year. Is the year before that of equal significance? Or the year before the year before? Probably not.  So this approach dilutes the visual impact of earlier years. You can still see them but they clash less.

This is a big improvement. We have used standard Excel colour palette options (we could use a  colour selecting utility to make ore precise custom choices). We have also adjusted the contrast of the gridlines, axis and legend labels,

While it is better, it is still very 'busy'. So the next step would be to sacrifice the oldest year of data

This is a lot clearer - it allows a greater contrast between the most recent year and the one before.

It's still quite busy. What does removing another old year look like?

That looks about right for this pattern of data.

We maybe need to double check that the our colour scheme is giving us something extra (rather than it just being  a result of reducing the number of data series),  So here is the same chart again using the the Excel default colours

Yes the 'fading' scheme is much clearer, and the other adjustments to contrast also help.

Band past years together

What if we do not want to lose sight of the earlier years? A way to retain some of this information is to calculate the range of vales for each week over several years. This takes a little bit more setting up.

First add some calculated columns for the Minimum, Maximum, Range (i..e Maximum - Minimum) and pull through the latest year as a repeat column

Then use the three columns Minimum, Range, Last Year to create the graph.

Start by inserting a Stacked Area graph

Next adjust the data series properties so that the Min series is invisible and the Range (Max-Min) is a faint colour

Next change the chart type for the Last Years (2017-18) data series from Stacked Area to Line

That's more or less it - it just needs some tidying up

• Move the legend  from the side to underneath
• Delete the Min from the legend
• Change the legend label for Min-Max to something more meaningful
• Adjust contrast on grid-lines and axis labels
• Adjust line colour of main series to taste

So that's about there now. It allows us to see what the weekly pattern of  the year in question was against the range of the four years before it 

For me this is the best way to look at this particular pattern of data. For other patterns I may think differently but it's a good starting point

The advantage of Excel is that you can quickly knock together different versions to see what they may show

Some other thoughts and possibilities

One approach that might be suggested from modern data visualization thinking is 'small multiples'. In this approach, rather than combining the years into a single chart, each year would be plotted singly and then all would be aligned into an overall view

Here's a quickly assembled version in 'small multiples' style

This is ok in the sense of showing the pattern for each year. But how easy is it to see whether 2017-18 values stray outside the 'typical' range?. Does this version of small multiples offer much more than simple sparklines?

So maybe to make caparisons easier in the 'small multiple' version we might have to add some solid fill?































Using a solid fill below the line, and containing the plot area makes the comparisons easier. But it still requires a lot of mental gymnastics to make and hold the comparisons

If you able to use these charts in an interactive setting then there are options for rotating through successive years using a pull-down list - as the following illustration roughly simulates: