Posted on 22 February 2010
Heat maps are pretty versatile. They have been used for everything ranging from charting home prices to changes in the stock market.
(Average Listing Price for US states, week ending Feb 17, 2010. Source : www.trulia.com)
(SNP performance, Feb 19, 2010. Source : www.finviz.com/map.ashx)
Inspired by the last ball win over the South Africans yesterday night, I wondered if some of that action could be captured in ...
Posted on 03 February 2010
So how do you fit multiple scales on a single excel chart? No, we are not referring about the primary and secondary scales on an Excel chart here, we are talking about adding a customized scale for each category in the chart.
Take for example the receipts portion of the U.S. federal budget - a data set with multiple categories such as individual income, social security, excise, estate and other taxes & duties.
Posted on 12 January 2010
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 ...
Posted on 06 January 2010
A what!!! A Square Bubble Heatmap Chart !!! What has the Bison been eating lately. Frankly .. I don't really have a name for this chart. It is a bubble chart. The bubbles are square. And it's a heatmap. So I'll call it a Square Bubble Heatmap Chart or better still, S.B.H.C. for short 8-) .
The Basic Data Set For the Bubble Chart
Now let us assume for a moment that ...
Posted on 29 December 2009
are an interesting way to look at data. Here is the first preview of the treemap add-in for excel. Being a demo, the number of data points are limited to 50.
Here the a demo file containing the treemap add-in for Excel
. (The demo treemap version is limited to a maximum of 50 rows and is meant for evaluation purpose only. The full version does not contain this restriction.)
Posted on 24 December 2009
Its been a few days since I posted. In case you were wondering what I was up to - yours truly was busy coding a program to create treemaps in Excel. Treemaps are interesting creatures. You can pack a few thousand data points in a single treemap and utilize just about the same space as any of the traditional charts. The term 'treemap', as we see it being used nowadays, actually refers to the inward growing members of the treemap ...
Posted on 16 December 2009
In Six Sigma Control charts using Excel
we saw how to make control charts
using named ranges. From there on, it was a short hop to porting the logic to VBA. So here is the VBA code for automating the creation of a six sigma control charts. It has been tested with Excel 2000, Excel 2007 and Excel 2010.
How to Use the Control Chart Program
1. Simply click the 'Make Control ...
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 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 ...
Posted on 10 December 2009
Always wanted to make a stacked bar graph but were afraid connoisseurs would smirk. Let me present - the stacked bar graph with helper bars - the latest addition to the charting fraternity. (Drum rolls)
In case you were wondering how this one got made, here are the steps:
Set Up the Data for the Bar Graph
In this example, we pick up ...
Posted on 01 December 2009
A timeline chart allows the reader to understand the chain of events as they take place over a period of time. The events are typically represented as markers along a horizontal or a vertical line.
In our previous post on adding interactive labels to chart
, we saw how to fit a large number of event descriptions in a single chart. Today we look at a similar approach in order to ...
Posted on 28 October 2009
A scatter chart helps to plot data with two or more variables. One great advantage that a scatter chart has is the ability to show a relatively large number of data points in a small area. However, as the number of points plotted within the same space increases, it sometimes becomes difficult to distinguish individual data sub-sets.
One quick solution to group all the points within the desired data sub-set is ...
Posted on 25 October 2009
In Applying Offset Labels to Chart
we looked at how a chart's labels can be offset to avoid cluttering. But what if you wanted to use the chart to narrate a story - and a pretty long one at that? How would you do it? If you turn on the chart axis labels, the entire thing gets messed up. Roots growing out of the chart axis wasn't exactly the point that we would like to highlight to someone who's ...
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 ...
Posted on 07 October 2009
) posted this line chart sometime back. A recreated chart is shown below where labels have been added to last point of each series in the chart. If you noticed, the labels have also been enhanced to show both the series name and the value of the data point. Lastly, the Y Axis of the chart has been offsetted to crossover at the last category.
The benefits are obvious - the ...
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 ...
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 ...
Posted on 30 September 2009
can be used to format numbers, text, dates and time
. The technique can be extended to charts as well where the custom format feature can be used to color the chart axis and chart labels.
In this example, let's say we wanted to show the sales numbers for a particular duration as a chart. We would, in addition, like to have some kind of separation between what is considered below, average and above average performance. Custom format ...
Posted on 24 September 2009
In Chart Label Trick - Descriptive Labels
we talked about adding custom labels to chart. In this article we discuss offsetting chart labels - which means positioning the labels above or below the actual data points at a predetermined distance.
Getting the chart data in place
Like always, we first get the chart data in place. For the purpose of this chart, we will use two data series - the first ...
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 ...