Somnath's Excel Charts

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

Today we discuss yet another chart mentioned in Paresh's blog . In his post last Sunday, Nov 13 , Paresh talks at length about small multiples , Otherwise known as Trellis Chart, Lattice chart, Grid Chart, or Panel Charts as a powerful tool of visualization, and asks us ways on how to replicate the chart published in NYT . Soon thereafter, I see Chandoo post  a tutorial on how to create such charts. But, he uses 3 separate ones to achieve the "Panel" effect. Here is my response to the question. Since I couldn't locate the data for the charts, I've used approximate values.

My third pick from the list of abhorrent Wiki charts mentioned by Jorge Camoes is the one from the page of Mauthausen-Gusen , originally a collection of villages of Mauthausen and Gusen in Upper Austria, which became one of the largest Nazi concentration camps by the summer of 1940 and the place of death of several hundred thousand inmates. There are several reasons for finding this serving of pie unpalatable. Firstly because it is a "Pie Chart". This same data could be more easily and effectively displayed using a column or a stacked bar chart. Secondly, because it is an exploded pie chart. I prefer an exploded pie-chart where the exploded slice merits special attention from the rest. To otherwise create this type only succeeds in bringing a ragged look to an already poorly crafted chart. Third, and most importantly, the use of flags as legends and with it, the redundant use of data labels to state the name of the countries that is absolute chart junk! I'd either

After the Throughput Accounting chart in Wiki, my second chart of call is the Blackpool FC pie chart . With their obvious disadvantages, I prefer using them on the rarest of occasions. So let's take a look at the follies in this one. The chart portrays the names and the tenures of all Blackpool FC managers since the club's inception. Ideally, and supposedly, such data is best displayed along a timeline. Instead of picking among the available alternatives, the creator picks a circular shape with no obvious beginning or endpoints. The slices in increasing order of fading shades of the club's color - Tangerine, is vague and whitish towards the ends, leaving the reader with no definite hint on when the managers tenure starts or ends. With the limited data on offer, I consulted the Blackpool FC Wiki page for additional info and created my alternative in the shape of a Gantt Chart. Simple, uncomplicated, chronological and instantly legible.

In one of his earlier posts, Jorge Camoes calls for changing bad charts in Wikipedia . Therein, he embeds several examples of charts, which can be easily considered "atrocious". Here, I'll attempt to correct the one titled " Throughput Accounting ". The embedded Wiki chart is a 3D horizontal cylinder chart and contains a long definition of Throughput accounting (T) and its structural components. The definition also includes two equations, T=Sales less TVC and NP=T less OE. The 3D aspect of the chart serves no special purpose, and the cylindrical shape of the columns with the alternate blue and white bands seems intended for enhanced aesthetics. The columns are arranged without bearing in mind the relationship between the components, and the chart looks somewhat ragged overall. I replaced the look of the chart from the 3D horizontal cylindrical to a "Waterfall" chart, using stacked columns. I also arranged the columns in reverse order to

This is a blog written at the Airport. I had an hour to wait for my flight and could think of no better way to spend the time. I love creating different charts in Excel. It keeps me company in my weekend afternoons and there is the immense benefit of learning something more about Excel. I looked at this chart in Paresh's blog briefly the week he posted it and thought of recreating it in Excel. Then a family tragedy and more urgent and important matters pushed this back in my to-do list. Now that all is cleared, I finished making the chart in the airport lobby! The recreated chart: And my visualization of the data with some noted differences: I've chosen to substitute survival rates with mortality rates, and for a valid reason. Since the chart also displays the number of deaths caused by the individual forms of Cancer, I surmised the display of mortality rates would be more appropriate with that figure.