Somnath's Excel Charts

My last project involved using a multitude of regions for drawing analysis, parallels and comparison. Not wanting to use yet another Choropleth graph, I decided to look up alternatives that were easier to create and preferably required no VBA. Soon I stumbled upon "The Dorling Cartogram", defined in the UCSB site as, "This type of cartogram was named after its inventor, Danny Dorling of the University of Leeds. A Dorling cartogram maintains neither shape, topology nor object centroids, though it has proven to be a very effective cartogram method. To create a Dorling cartogram, instead of enlarging or shrinking the objects themselves, the cartographer will replace the objects with a uniform shape, usually a circle, of the appropriate size." I had the data for Obesity in the United States handy, so I decided to give it a try before using it in my project. I opted to use Bubble charts because data points within a series may need to be of varied shapes based on...

I stumbled into this graph some years ago, while looking up data for a project on US Auto Sales, through a post in Neoformix - Discovering and Illustrating Patterns in Data. So, when I started making the list of Pie-Doughnut combination charts, I decided to include this variety and use it to display survivors and victims of the Titanic tragedy in its centenary year.  It is not a particularly difficult chart to create, especially if one sets up the table properly. My first attempt brought me this: I thought a lot over whether to keep the statistics as labels for the outermost doughnut that displayed the survivor/victim proportions. They get a bit muddled up around the first-class women and children's section and the inability to add leader lines to the doughnut labels meant they couldn't be dragged away and the lines used as pointers. Here is the chart with the stats on display:

Happy to be back after a pretty busy beginning to the new year. I had this completed almost immediately following the preceding post but was otherwise hard-pressed to find a suitable time to post it. Soon after writing the Florence Nightingale Circumplex Chart post, I started searching for more varieties of charts that can be created using combinations of Pie-Doughnut charts. Soon enough, I found one through Naomi B. Robbins' comment in a Jorge Camoes' post . The referenced PDF article attempts to use Fan Plot to display relative quantities and differences using the R statistical language as is shown in the image below. Impassive of its benefits and/or disadvantages, I created it in Excel.

I was taken to the  Florence Nightingale's Wiki page   during a recent research, and one of the interesting things I noted was her contribution to statistics. It came to me as a pleasant surprise that she is credited with inventing the polar area diagram , or occasionally the Nightingale rose diagram, which is equivalent to a modern circular histogram. Following the completion of my project and in my weekend to spare, I devoted time to recreating the chart in Excel. It took a combination of Doughnut-Pie-and XY charts and close to four hours to finish it. The colours are a bit darker, the values are approximate and the labels differently oriented, yet the chart looks fairly close to the original as is shown by the picture below.

Soon after finishing the second Playfair chart, the one on Wheat Price and Wages, I searched the internet for additional charts made by him. I found a Bar chart circa 1786, which showed the Scottish export and import volumes with other countries. For me, the real thrill was to scroll by the list of the name of places long consigned to history books - Jersey Is, Greenland, Prussia, Denmark and Norway (together) and Flanders.  First, the original Playfair Barchart from Wikipedia,  Then, my version of it in Excel.  A couple of parting words:  Excel 2007 no longer support dots and lines as fillers for charts. Hence, the ribbed import chart is given a different color, Gold.  Normally, I'd use data point labels to construct the chart legends and other declarations given at the bottom of the chart. However, given Excel 2007' inability to automatically re-size labels to fit texts, I was forced to use text-boxes instead. 

After successfully recreating the Playfair trade-balance time-series chart , I took up the second chart shown in Jorge's post which is fancifully titled, "Chart showing at one view the price of the quarter of wheat and Wages of Labour by the week from The year 1565 to 1821." I left out the top arches. Comparatively, this was the easier chart. Staying true to Jorge' rules, I didn't use any shape or clipart objects in this chart. The big oval shaped object in the middle of the chart, which contains the title is actually a marker for a data point. The original Playfair Wheat price and Wages chart from Wikipedia, and underneath, is my version in Excel.

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

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.

The name  Herman Cain came up a few times in my reading materials recently. A former business executive, he is in the news as the leading Republican presidential candidate in the primaries, and for his 9-9-9 Tax Plan . Kaiser, of Junk Charts has made a couple of attempts to visualize the 9-9-9 Tax Plan and its possible impact on people belonging to different cash income quintiles. One can read and learn about his first attempt here . In all fairness, the chart appears somewhat mangled and hence lacks clarity and the utility for the purpose. At first glance, the lines looked far too thick and the labels were wrongly positioned. But the most critical omission is missing out on 6.2% of the lowest quintile population who remains undisturbed in this tax shake-up. The data table is available here . I decided to keep the vertical and horizontal axes similar. The horizontal axis labels are brought down below and major gridlines are formatted to create quintile panels every...

For those of us into performance consulting, Box plot (Know more about them here ), is a wonderfully simple and efficient tool to help us get about our job. As every coin has two faces, every data-set has two aspects. One, is the "feel-good" part of it, the part which tells us about the successes and the top performers. Second, is the "ouch" bit of it, that gets us to notice the outliers. Usually, the Box-and-Whisker diagram is used to plot and separate data as the smallest and largest values, the median, and the lower and the upper quartiles. But, the shape and the style of it can be applied to depict other sets of data as well. Here, I've used the B&W template to depict the monthly temperatures of my home city - Calcutta. The necessary data was gathered from here and there . It included the data for record monthly highs and lows, which were depicted in the chart by the whiskers, and the average monthly range of normal temperature, which is represented b...