Categorized | excel chart

Volatility Chart in Excel – Lessons in Chart Design From NY Times

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


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Daniel Ferry wrote:

    I thought I’d take a stab at recreating this intriguing chart in Excel.

    For those that have Excel 2007 you can grab my workbook here:

    For those that don’t, here is an image of the chart:

    I’m happy with the look, but it needs work to improve efficiency. The chart takes 30 seconds to render on my PC.

    One of the aesthetic high points of the chart is that outliers draw beyond the grid lines. This cannot be done in Excel using the built-in grid lines, as they will always fill the entire plot area. So my version creates grid lines from data. I like the result.


  2. Ajay wrote:

    Amazing effort Daniel – neat and elegant. Congratulations !!!

    “…the chart takes 30 seconds to render on my PC..” – when you are going to stretch Excel to its limits, that’s expected :-) Actually I could not render it using Excel 2003 at all and had to ‘resort’ to Excel 2010. It took about 7 odd seconds for me but the results were overall nice. The event and year labels were missing though and I suspect that’s to do with porting across versions.

    For the benefit of the readers, here’s Daniel’s version:


    Again a very good effort !!!

  3. Jeff Weir wrote:

    Hi Ajay. In my opinion, replacing the colour index with a simple line chart will not only make this graph more easily interpretable, but will also allow better ‘resolution’ of the annualised series.

    To quote from Stephen Few’s book Now you see it: “Color is good at drawing your attention to something if used sparingly, but is one of the ‘pre-attentive attributes’ that is not quantitatively perceived in and of themselves”. Or put another way, it would be much easier to compare one point on the graph with another or to pick out recurring trends (if there are any) using a line graph overlaid on the original series, rather than having to translate between colours and numbers as it stands.

    By better resolution, I mean that the other advantage of using a line graph over the colour index is that you can simply average the daily fluctuations, rather than having to average them and then place them into arbitrary ‘bins’ (in a same way that a histogram treats data).

    Which raises another point: It’s not actually clear to me whether the colour range signifies a -25% to +25% range, or whether it signifies a greater range to an unspecified outer limit (i.e. for the positive side, you could interpret it as having separate bins of 0 to 10, 10 – 25, and 25+). Regardless, these bins really should have even increments.

  4. Ajay wrote:

    @Jeff – I would have agreed with what you are saying but can’t help thinking that a line chart on top of the volatility lines may take the ‘glamor’ away (if I could put it that way). I can’t think of a way I can have it both ways – but maybe we are thinking on different lines. As regards the color values, I am not sure how many of our readers picked that one up but I sure did miss it completely. Nice observation about the open ended scale and the irregular bins – kind of obvious now that I look at it.


  5. Daniel Ferry wrote:

    The original chart in the NY Times was definitely not created in Excel. I think that we are sometimes so accepting of the limits to data visualization that Excel imposes that we overlook creative ways to express data.

    This chart in particular intrigued me so much that I went to the trouble to recreate it in Excel and Ajay was kind enough to post it here. It is intriguing because it has a massive amount of flair. The outliers of daily points flying off the chart, the subtlety of the grid, the spartan use of color are all quite beautiful. The color band is not only meaningful, but artistic as a backdrop for the daily swings.

    I disagree with Jeff. The colors are not supposed to be equally incremented. You might want to argue where each category should start or end, but basically light blue means a poor year, medium blue means really bad year, and dark blue means cataclysm. The intensity of the color follows the intensity of the volatility in both directions. Any more than three bins in either direction and the chart would become difficult. As it is, it’s extremely easy to understand. The open ended-ness of the most volatile (intense) bins is not a mistake – it’s the point.

    Jeff’s idea misses the entire point of the chart and comes from the sandbox of Excel’s constraints. The designer of this chart does not play in that sandbox.

  6. Jeff Weir wrote:

    @Danial: Who said anything about Excel?

    My thoughts don’t come from the sandbox of Excel’s constraints, but rather from a basic understanding of how the brain perceives quantitative information visually, as relayed by books from the likes of Edward Tufte, Stephen Few, and Colin Ware to name a few.

    The brain is the sandbox we all have to play in, regardless of what software package is used to whip up a graph.

    I’m not against creative ways to express data…unless they are not the BEST way to display the data.

    Stephen Few’s teachings certainly don’t come from the excel sandbox…he’s scathing of excel. But then he’s scathing of any bad chart design, regardless whether its produced on excel or the hottest design software on the planet. So I imagine he’d agree that replacing the colour index with a simple line chart will not only make this graph more easily interpretable, but will also allow better ‘resolution’ of the annualised series.

    My gripe with the scale on the boxes was that it’s unclear what the scale is…but if it were meant to be openeded then the chart designer is telling us “whenever you see a light orange box, the value ranges somewhere between 25% and infinity”. I don’t think its “the point” so much as sloppy design.

    Yes, the color band is meaningful, and artistic as a backdrop for the daily swings. But if it is truly an important source of information, how do its merits as rendered stack up against the merits of a line chart?

    @Ajay: It’s okay that a line chart on top of the volatility lines may take the ‘glamor’ away…PROVIDED it makes the chart easier to comprehend. If my life depended on taking action from a chart, I would hope glamour was sacrificed for comprehension anyday 😉

  7. Ajay wrote:

    @Jeff – I like your last statement. I guess it’s an individual preference – this trade-off between ‘glamor’ vs ‘comprehension’ thing. Honestly, if comprehension isn’t an issue, I am much more amenable to things being shaken and stirred :-) However at the same time I do feel that the ‘comprehension limit’ may be different for different set of people – just as an algebra problem probably would be for ‘math people’ vs. ‘language people’. In my limited experience I haven’t come across too many charts that are pivotal to a reasonably complex decision making process. Let me also say that I have a vested interest in championing the cause for more ‘glamorous’ charts in Excel, if only to prevent suffering a lifetime of torture through insipid bars and lines :-)


  8. Jeff Weir wrote:

    Hi again Ajay. Sentence for you to complete: if comprehension isn’t an issue, then the purpose of the graphic is….


  9. Jeff Weir wrote:

    Stephen Few has a great post at on accessing the effectiveness of a new dashboard’s design. Many points are applicable not just to dashboards but to stand-alone graphics…certainly if graph/graphic designers followed the “Specific Parts of the Dashboard” part then we’d have much more meaningful graphs.

    Under the heading Specific Parts of the Dashboard :

    * Does the way that each measure is displayed express the information in a way that directly supports your needs without having to do conversions or calculations in your head? This could involve something as simple as graphing the variance between expenses and budget directly, rather than making you compare two lines on a single graph.
    * Can you rapidly (1) discern the value of each measure, (2) determine whether it is good, bad, or otherwise, and (3) compare it to something that allows you to judge the level of performance?
    * Do you have enough information about each item to determine if you must respond in some way?
    * If you need to respond to something, can you easily get to any additional information that is needed to determine how to respond?
    * Can you perceive each measure as precisely as you need to without being forced to wade through more precision than you need?
    * For each measure, can you tell if performance is improving, getting worse, or holding steady? For those measures that lack trend information, would the dashboard be more useful if it were shown?

  10. Daniel Ferry wrote:


    This is getting a little silly.

    Intelligent people can disagree.

    But I would like to point out that you are misreading the chart. The chart author is not saying, as you put it, “whenever you see a light orange box, the value ranges somewhere between 25% and infinity”.

    Instead he is saying that whenever you see a DARK orange year, that year was devastating. Maybe, a change to the legend where labels such bad, worse, and worst, instead of the percentages would work better for some, but this is what he is conveying, and it was not sloppy, it was discreet.

    Stephen Few is a renowned expert in these things. Before I sent the chart to Ajay, I sent it to Steve and asked his opinion of both the original chart in the NY Times and my reconstruction of it in Excel. Here’s his response:


    Beautifully done. Not only did you manage to overcome the technical challenge of doing this in Excel, but you recreated the fine visual design of the original with care. I agree that one of the highlights of this design is the way that true outliers pop out by appearing to exceed the boundaries of the plot area.

    Excellent work.

    Take care,


    Jeff, if you don’t like the chart, that’s fine. But some of us do. My interest was seeing if it could faithfully be reproduced in Excel, being that it was so un-Excel like.

  11. Jeff Weir wrote:

    Of course intelligent people can disagree.

    All I’m doing is critiquing the chart from my perspective. That’s why my comment starts off with ‘In my opinion…’ and why I prefaced my criticism of the legend with ‘It’s not clear to me…’ I elaborate in both cases on what I think is wrong, and state how I think it could be made better.

    On the other hand, your comment simply states that my idea “misses the entire point of the chart and comes from the sandbox of Excel’s constraints.” It would be fairer if you could elaborate on which part of my comment you disagree with, and why. What is the entire point of the chart that I have missed? Why does my comment come from the sandbox of Excel’s constraint? That would be much more helpful…I might learn something.

    It’s interesting that Stephen Few thinks the design is fine. Perhaps he hasn’t considered the points I made above, perhaps he has. The rendition of the annual series as colour blocks seems counter to his own words.

    The chart has many good points. I’m just pointing out that I think it could still be improved.

    I didn’t misread the chart legend, but I did mistakenly write ‘light orange’ above when I meant to say dark orange.

  12. Jonathan R wrote:

    My main grievance with the chart’s design is that the colors chosen do not have proper associations with positive and negative — I don’t think of orange as a “good” or “improving” color and I don’t think of blue as a “bad” or “degrading” color. Especially not in pastels the way they were chosen.

    This makes it very difficult for me to pull any meaning from the band of coloration, because it just doesn’t click with my thought process that blue is something I don’t want to see (Assuming my desire is for years of growth) A line chart would avoid any personal color issues, though choosing more standard colors could have also done similar (Such as green for growth and red for decline).

  1. Dow Jones Volatility Chart | Excel Hero Blog wrote:

    […] couple of months ago Ajay at DATABISON posted about replicating a very interesting chart he found in the NY Times. Here is a link to the […]


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel