Somnath's Excel Charts

I came across  this data-set  while reading about different visualization challenges in  Visualization.org . An intriguing table where 194 countries are ranked and rated in diverse parameters to reveal the best place to live. Strangely, there is no data for Sweden under the heading Cost of Living. Certainly, the data was either left out or not available for some obscure reason. It just seems to defy the belief that Sweden will score a "duck" in that category. That none of the Scandinavian countries made it to the top list is yet another surprise for me. Overall, the "Top 10" places to live, in descending order, are: France, Australia, Switzerland, Germany, New Zealand, Luxembourg, USA, Belgium, Canada and Italy. Another idle afternoon spent in sweet excel company to create the following. Some parting words, The scroll-bar and spin-button controls are active-X control elements, not form control elements. Given the inabil...

Delighted to be back after three weeks of treatment, care and medication. I rarely fall ill, so this afforded a nice break from my regular activities and also the chance to browse several websites which deal with data analysis and presentation. My data-set for this week is from the  Guardian Datablog, dated Aug 17th, titled "End of the USSR" . The piece is accompanied by an image that uses a map to rate the former soviet provinces on their performance since the collapse. The data includes yearly figures of GDP, GDP per Capita, Population, Life expectancy and Mortality among other interesting indicators like Intentional homicides, Prison population rate, Prevalence of HIV by gender, Unemployment, Happiness index score, Global peace index and Number of McDonald's! Given the large difference in figures between Russia and other republics, I was curious to see how the presenter would design the population and the GDP charts for comparison. I ...

I do habitually trawl a lot of websites. Partly because of my interests, partly due to my commitments, and some because I'm concerned. One among the last is  The American Cancer Society Inc.  A comprehensive website that incorporates A-Z of cancer. From learning about the disease to its treatment and from all relevant data to a chance to volunteer and make a difference, it includes all. The data is from the section  Cancer Facts and Figures 2010 . I know a similar page exists for the year 2011, but I'd rather wait for the year to be over and the numbers updated, so I can then not only look at the data, but also perform a meaningful comparison against the previous year. The dataset is taken from page 3 of the PDF file, titled "Estimated new cancer cases and deaths by sex for all sites, US, 2010". Sadly, there can be no correlation between new cases and deaths, so, my main focus was to find the best way possible to represent this data in a dashboard. To ma...

This data was published in the  Guardian issue of December 7th, 2010 . It mentions the  Organisation for Economic Co-operation and Development's comprehensive world education ranking report, ranking each country in three disciplines of Reading, Maths and Science. The  OCED's idea of using a Bump Chart (learn how to make one  here ) didn't quite appeal to me for two reasons. First, given the length of the chart, it not only required scrolling, it demanded it! Secondly, it was a visual overload of colours that scarcely provided any comparative assistance. Since I gathered this data from a UK newspaper, it only seemed judicious to keep the UK as the standard country to compare others against. I decided to use a Box-and-Whiskers Panel Chart to display this data, plus a couple of dynamic boxes to include relevant data for the comparing countries. After about an hour of work, I was happy to achieve the following.

Everyone in India is crazy about cricket. International cricket to be exact. This passion was triggered by a freak World Cup Championship win in 1983, and its dominance in India has continued unabated since. Of late, successful triumphs in T20 World Championship, The ICC Test Rankings, IPL and most recently, the ICC World Cup 2011 has taken support for the game in India through the roof. However, this post is not about the ascendancy of cricket in India. It takes to the various types of graphs we see in the telly to help us further analyze the game. Commentators have given them colourful names. The two most notable and common are "The Manhattan", which is the column graph, and "Worm" - which is the line chart. I didn't have the time to look up official India and Australian sides, nor the details of an official match between them. I knew a 50 over per-side match will be too bulky to create as a dashboard chart, so I zeroed in on a 20 ov...

I've had plenty to read these last couple of weeks. I use Google Reader to receive my daily/weekly dose of reading and I found this fascinating (presently expired) challenge in  Chandoo's Blog . Chandoo has generously provided us with a well-laid out table which I wanted to keep in the dashboard. Option buttons at the end of the rows will allow the user their pick of the product and once clicked, it will shade the row selected and update two charts with the necessary values. The first chart will plot quantity sold and per-unit revenue for each month, while the second will portray the total revenue earned each month for the product and the percentage change in revenue from the previous month. The file contains no macro. After some playing around, the dashboard is prepared as shown in the picture below.

In the Guardian datablog of 23rd Nov, 2010 , a report was published predicting the population of Africa's cities is to rise rapidly. I sat down to see how the historically established African metropolis will expand over time and whether any relatively smaller city or a yet unknown settlement will become the focal point of African settlement in the years to come. The Guardian data can be viewed and downloaded from  here . In my excel workbook, I've added additional columns to help me with the location of these cities, and to abbreviate their names as some, though exotic and wonderful, are too expansive to fit it my chart. Every dataset can be analyzed and represented in several ways using tables, charts or a combination of them. Here the cities can either be represented singularly showing the increase/decrease in population over the years, or be compared against each other. I tried to be innovative and combine my two distinct thoughts into one masterful combi...