Here’s a slightly dated chart from NY Times showing volatility on the Wall St across the century. The chart does commendable work in conveying information about a longish time series pretty effectively. I found some great chart design principles at work here that I could utilize for day to day charting needs.
(Click on image to open larger version. Click here for the original version)
Here’s a look at a few of those amazing things that I picked up from this chart (I am sure when you take a closer look, you will find more). I will also attempt to show how they can be reproduced using Excel.
The Chart Ought to be Clutter Free
The first time you look at the chart, one could probably miss the fact that you are looking at about 100 years worth of data. Everything about the chart characterizes simplification. “Above all else, simplify”.
Here’s the Excel reproduction of the NY Times chart. Please bear in mind that I’ve stripped off a few details, the data is random and the chart isn’t as handsome as the original )
Softer Gridlines – To Avoid Chartjunk
Since the chart covers a long timeline, the gridlines help to break-up the time series into smaller, more digestible pieces. The authors have taken care to keen them subtle and in the background using softer colors.
In Excel, you can make the gridlines a little more subtle by first modifying the default palette colors and then using them to paint the gridlines.
X-Axis Labels in Proportion to the Overall Timeframe Covered
Oftentimes, when one has too many data points in the chart, the X-axis suffers with data overload. One often sees too many being forced to occupy the same space. This is 100 years of data and all the chart makers have used are 11 X-Axis labels. The result – more clarity.
In Excel you can add custom format the chart’s axis so that they become easier to grasp for the reader. Here’s how we converted something like “1-Jan-1910” into “1910’s“.
(Note: The label’s on the recreated chart are not perfect (say a 1980’s does not span the range between 1980 and 1990 but actually marks the year 1980). There are ways to get this right but are beyond the scope of this post.)
Y-Axis Labels Formatted to Ease Comprehension
I’ve been guilty of leaving the labels on the Y-Axis as such without providing the reader with any information on the unit of measurement. A point on the scale which reads “10” could be anything – 10 years, 10 litres, 10 tons, 10 billion. How many times have you seen people ask the following question in a presentation,
“So the units on the Y-Axis represent (something) , right?”
I have been asked that question more times than I would wish to remember and I kick myself in the … you know what … everytime for missing the obvious.
In this case, the labels have been formatted with a % suffix so that one does not have to ask that question. The datum (zero) has been omitted. The same can be achieved in Excel by again using custom formatting. This time around, we take use of three conditions – the format to use when the number is positive, the format to use when negative and the third one for zero. (In case you missed the link above, here’s a bit of information on the custom format feature in Excel.)
Add Descriptive Information – But Don’t Overdo
I like to pack my charts with as much descriptive information as possible till such time that they don’t interfere with comprehension and readibility. Most of the charts that you see in the corporate world are not exactly as small as sparklines. This means that a typical chart will have a lot of space that can be used for other things – such as the empty region underneath an area or a line chart. In this case, the empty space below the plot has been used effectively by adding major events descriptions there. Also worth noting is that the minor events have been left out. I think a few other pivotal events could have been included but that’s the point – you can’t make everybody like what they see. Be bold – don’t give in the urge to show everything.
You would have also probably noticed that the legend is shown right next to the chart label so that nobody misses it.
Avoid Colors Unless They Convey Information
Use colors to show additional information, preferably another dimension of data. Be subtle and be intuitive. Stephan Few has some great thoughts on using colors with charts here.
The color range from dark blue to pale brown signifies the return from the index (-25% to +25%). As you would have sensed, periods of low return are typically followed by periods of high volatility.
Like everything else in Excel, there will be a way to add the color phase band to this chart but I leave that challenge to you
Here’s a copy of the reproduced volatility chart for download