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

An Excel Chart’s Journey Across Versions

Posted on 10 January 2010

Sometime back I wrote a post on making control charts using Excel. The chart was initially created in Excel 2003. The chart consisted of the plot values, the average line and upper & lower control limits. While the main data series was a line chart, the average, upper and lower control limits were plotted as dots of an X-Y and then were extended into lines using the horizontal error bars. Shown below is a sample. ...

Posted on 13 December 2009

One axis charts combined with the histogram are a great way to summarize unidimensional data. Excel does not provide provide in-built support for one axis frequency distribution charts but they can be created without breaking into a sweat. The Histogram The histogram trades off detail for ease of comprehension. Let's look at the chart below. This one's based on a set of 250 data points with ...

Change in Position – Which Chart Should I Use?

Posted on 16 October 2009

As an analyst, I often need to keep track of the change in position of various players in the industry. More often than not, the position change needs to be tracked between two specific points in time - say quarter 1 vs. quarter 2 or perhaps year 1 vs. year 2. In this article we take a look at a few alternative approaches to plotting position changes and find out which one serves us best. For this example let's take ...

Probability Charts – The IMF Way

Posted on 02 October 2009

IMF's world economic outlook report bought forth some interesting charts. The charts in question depicted the probability associated with future data points and highlighting the zones of uncertainty associated with each estimate. You can access the entire report here. Creating a Chart with Probability Bands around the chart data Often times there is a bit of uncertainty associated with data points. "Confidence level" is a term often used by statisticians to ...

Butterfly Chart – Excel Chart with Dual Converging Scales

Posted on 01 October 2009

A Butterfly chart is a chart where two entities are compared side by side using scales meeting at the center. Due to its shape, the chart resembles a butterfly and hence the name. These charts are sometimes also known as Funnel or Tornado Charts though I find "butterfly" to be a better description as it allows for a greater variation in shape than a funnel or a tornado does ! So let's jump ...

Chart Label Trick – Converting Chart Axis Labels to Table

Posted on 18 September 2009

The labels used with the chart category axis are probably the most ignored part of the entire chart making process. You see, the labels on the value axis can be modified, the bars, the lines and the plot area - all of them can be embellished - colors added, widths adjusted, descriptions applied - the works you know ! Even the poor little gridlines can afford a makeover. But what about the chart's category axis label - what do ...

Chart Table – When Chart meets Table

Posted on 15 September 2009

Chart table - as the name suggests - is a beautiful fusion of a chart and a table giving rise to a visually appealing yet information rich presentation of data. Not that the concept itself is anything new - experts have openly and vociferously championed the cause of the chart table, its various facets have been discussed at length in numerous forums and the latest versions of excel come equipped with tools that further aid their creation (namely sparklines). However ...

Chart Label Trick : Applying Descriptive Labels to Chart Series

Posted on 12 September 2009

Chart labels provide information related to the underlying data. In Excel, by default, chart labels can only consist of the following three - Series Name, Category Name and Chart X & Y Values. So what about situations when you want to convey additional information - for example, say a short description about the chain of events that had a material impact on sales (or say when you ...

Bar Chart with an Average Line for Each Group in Chart

Posted on 04 September 2009

In our previous article we covered adding an average line to a bar chart. At times, however, it may be useful to show an average line for each period or group when the chart consists of more than one such period or group. The advantage here is apparent - not only can the reader view the data points individually, he/she is also able to build a map of how various groups are placed with in relation to each other....

Interactive Chart in VBA using Mouse Move Event

Posted on 02 September 2009

VBA can be used to make interactive charts that respond to mouse movements. In this article we first start of by making a simple bar chart embedded in a chart sheet and then use the mouse move event of the chart sheet to make it interactive. Create a simple Bar Chart For the purpose of this exercise, we take the list of top 15 billionaires and sort it based on their networth. We ...

Bar Chart with Average Line

Posted on 24 August 2009

A Bar Chart is used to represent data using horizontal bars. One way in which you can augment a bar chart is to add an average line. Create a bar chart Create a bar chart in Excel using any particular data set at your disposal. In our case, we took the Forbes list of the richest people on earth (2009 figures). Here's how the data looks like. To create a bar chart, select ...

VBA Chart

Posted on 20 July 2009

Making a chart using VBA can be easy. VBA provides a handle to a chart object using the "ChartObject" class and to the chart data series trough the "Series" class. Let's look at a basic example where we use VBA to create an empty chart to our spreadsheet and then add data to it. [cc lang="vb"] Sub CreateChart() Dim ChartObj As ChartObject Dim ChartSeries as Series Set ChartObj = ActiveSheet.ChartObjects.Add ( Left: = 100, Width: = 550, Top: = 75, Height: = 325) Set ...

Javascript Charting Library

Posted on 06 July 2009

A Javascript charting library is a compilation of javascript code which helps you create charts in a browser. Here's a list of 5 Javascript charting libraries that I had a chance to review : (Each of the Javascript charting libraries reviewed below would offer its own custom functions to interact with the chart object. If a chart fits your need, you may want to read the chart library's API documentation in greater detail. However, if you're short on development time, this ...

How to Choose a Chart

Posted on 03 July 2009

How to choose a chart? Which chart would best fit the data set that I have? How do I choose the better chart type from Excel? Finding the right type of chart for the data set that you have can be a bit of a challenge. Given a set of seemingly equivalent alternatives, picking one chart over the other can turn out to be quite an interesting exercise. The chart chooser diagram can help you make a choice in situations ...