Somnath's Excel Charts

In one of his blogs excelcharts.com , Jorge Camoes talks about recreating the iconic "Playfair" charts. William Playfair (read more about him here), universally heralded as the "founder of the graphical method of statistics" is credited with inventing four types of diagrams. The Line graph and Bar chart in 1786, and the Pie chart and the Circle graph in 1801. In his post,  Excel charts meet William Playfair , Jorge includes pictures of the original Playfair England export and the prices of wheat and weekly wages charts alongside his recreated versions. He also lays down some rules for this challenge. "A single chart (no overlapping charts), no shapes/clipart to display data and, obviously, no Photoshop." The original Playfair trade-balance time-series chart from Wikipedia, and in below, my recreated version in Excel.

After creating my version of the Rugby World Cup Dashboard, it is time to redo their existing charts. My gripes were regarding the Treemap, the Stacked Bar and the Line charts, and here, I will attempt to offer easier, cleaner and more legible alternatives. My alternative to the Treemap Chart, The Stacked Bar chart,  and, the Line chart.

The Rugby World Cup Dashboard will live in my memory long for various reasons. Ever since I first received a mention of it in the pages of Bime Analytics , it haunted me with its collection of charts singularly inappropriate for their purpose. And what a collection of varied charts!! No two alike, it features a Treemap, an exploded Pie-chart, a Column chart, a Bar chart and a Line chart. Left to me, I'd have changed the colour of the theme, reduced the number of charts and designed the dashboard this way. The dashboard now includes more information, and all at the click of a button.

I often receive interesting ideas and suggestions from friends which forms the basis of some weird visualization. While personally, I prefer to keep my charts simple, effective and legible, I go creative listening to ideas from others. A friend of mine recently changed jobs. Before making his first presentation, he wrote to me about designing a chart which would WOW all his audience and help him to make a dramatic impact. The data was about units of products sold in each region and compared with similar data of competitors. Needless to mention, a simple column chart would have served his purpose well. But not to my friend. On his insistence, I drew up a dummy table, and served him the following. They look like abstract paintings. Right? Utterly amazed and curious, I thought of using this template on the Eurozone Debt data and came up with an even more wackier and colorful one. In both cases, the width of the lines are indicative of their values. I've ...

Unbeknownst to many, the BBC News business page is home to some interesting data-sets and wonderful infographics. So, when I came across the piece titled, " Eurozone debt web: Who owes what to whom? " which revealed, with the help of an interactive chart, what the countries owed each other, I knew I had to dabble with the data. Replicating that specific chart is close to impossible in Excel. But then, it got me thinking. do we really need a chart to visualize this data? Recognizing the boost conditional formatting received in Office 2007 and 2010 versions, I decided to use its power to mime a chart, rather than create one. I gathered the needed data from the information displayed in the chart itself to draft the table for the amounts the countries owed each other. And very shortly, within ten minutes, had my "mimed" chart ready.

After regaling us with an Unemployment Panel Chart a week ago, Paresh picked out yet another chart from the NYT pages and asked us to recreate it and improve it in the process. The chart in question is a dot-plot (learn about them here ), that shows the instances of repeat violations by big wall street firms after promising the government never to breach such acts again. Data shows 29 violations of section 17(a) of the Securities Act and 16 infringements of section 15(c) of the Securities Exchange Act between 1997 - 2011. The chart was easy to replicate in Excel. For improvement, I shaded every repeat instance in a different colour and added labels to show the count.

The fun of receiving feedback to blog posts is that one isn't quite sure what they might come up against. While mostly it provides fascinating discussions, insightful comments or explanatory questions, there is also the odd occasion when it throws up unusual requests. The other day I received an email in response to my post, The Cricket Graphs . The sender wanted me to create a "Partnership" chart, which would show the runs scored by the pair of batsmen for every wicket and the balls faced. I went searching for data on the Cricinfo website , and picked out the 3rd ODI  between the South African and Australian cricket teams which was played out in Kingsmead, Durban on October 28, 2011. The Partnership details were taken from the page titled Partnerships Table and the total balls faced by individual batsmen from the Scorecard. Constructing the graphs was a somewhat tricky affair, with two potential bar charts in two axes representing Balls and Runs, which had me w...