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