Somnath's Excel Charts

While browsing  Flowingdata.com  the other evening, I came across a presently expired visualization challenge on the changing sources of present-day news. It talks of the Internet gaining on Television as the public’s main news source as more young people cite the Internet than TV. You can read about the challenge and the article  here .  Among many who replied was Ole Østring, who submitted  this  unique chart/table in response. Here is my reproduction of it in Excel.

Looking back isn't always a bad thing. Pleasant memories aside, it also allows one to review earlier works in the light and knowledge of current experience and to realize how much path we have journeyed on since. I found a folder in my old computer the other evening and it was password-protected. Utterly curious as to what it might contain, I opened it to find copies of monthly dashboards I made in my earlier organization. Thereafter, it was mostly reliving old memories as I browsed through them all, looking at them and thinking of making them better. One of my favourite dashboards to make then was the Quality Dashboard. 10+ teams, 10+ performance parameters, 100+ associates, it was some ordeal to get all of them in a series of PowerPoint slides. Some dashboard too! Over 15 slides and in excess of 20 charts! I copied the data to a fresh Excel worksheet, altered and changed some of the names, and started a dashboard which in 3 simple tables will allow the use...

As I've raved about in my earlier  post , Annie Lausier' Calendar Approach in response to Flowingdata.com 's " 10 Visualizations for Number of Days to Pay Your Taxes " was so far beyond peoples expectations that it received comments almost bordering on adulation, prompting one to comment, "This was definitely one of my favourites, partially because of the smooth interaction and partially probably because I wasn't expecting it." Her work is available here . It features a very intuitive approach, and a two-way interaction between the dates and the states. In my attempt to see how far I can progress in two hours of working with the data, I came up with the following.  I'm happy to leave it at the stage it is now. Clicked on any of the pink-shaded dates colors the states for whom it is the Tax Freedom Day.

I bumped into yet another old but beautiful visualization challenge this evening.  Flowingdata.com 's " 10 Visualizations for Number of Days to Pay Your Taxes " may have run in 2009, but it still features some wonderful approaches to data visualization and I had a great time exploring them in some detail, but not before I had made my version of the chart as shown in the picture below. While exploring, my attention was immediately drawn towards a very unusual " Calendar Interface " designed by Annie Lausier. It was a standout even among some very unique others. I know it would be mighty difficult to replicate it in Excel. For, even with VBA, it will be a tiresome effort. But, I'm willing to give it a couple of hours and see what I can etch out.

Replicating a newspaper chart is always a lot of fun, and it was to be no different with this one. The  Sep 8, 2011 article  in Guardian includes a  choropleth map to display US unemployment percentage by state. While  data  is available for every month between November '08 - July '11, the Guardian drop-down box only allows us to pick four months out of 33! Choropleth maps are thematic maps in which areas are shaded or patterned in which shows the measurement of the statistical variable being displayed on the map, such as population density or per-capita income, and post-Excel 2002, is mighty hard to create in Excel. I've saved a  Jorge Camoes tutorial  for years on how to make one, and now, in my last week of recuperation, I've finally had the time and the patience to attempt and complete one. The finished map and table looks as below. Moving the scroll bar changes the colours for the states.  The table to the rig...

The other evening I came across the US Census data for 1910-2010. Even though I have worked with population data  before , this one was hard to pass by. The data is available in  Visualizing.org  site under "Eyeo Data Visualization Challenge" and it does include some nice flash charts to review, compare and analyze the Decennial Census Data. I've excluded Puerto Rico from my list of states, regions and outlying territories as it doesn't belong to any of the four census regions of the USA. For Pick-A-State, I've used a data validation list. Just didn't want a combo box sitting amid my nicely formatted dynamic labels. My visualization of the census data looks as shown below.

I get to work with Google docs a lot. While its spreadsheet is relatively rudimentary and only offers a basic level of customization, I've really grown to love the various gadgets which help the user quickly build up Gantt charts, Time series with annotations, Heatmap, Spider charts and Word clouds. I was very charmed by the tabbed feature of the Heatmap and wondered if any way, that feature could be "visually replicated" in an Excel worksheet without using VBA. I still had the data from  The Pac-Man Dashboard  handy, so I started off without any definite schema in mind. Ready to use regular Excel features like conditional formatting, form element and embedded objects to aid me to achieve my goal. Sure enough, in three hours, the following was laid out. Tabbed Business Overview Dashboard (Region) Tabbed Business Overview Dashboard (Sales Person) Tabbed Business Overview Dashboard (Product) And finally, Tabbed Business Overview...

Over this weekend, in a random conversation, A friend asked if I can build a dashboard entirely of pie charts. Now that is a serious challenge! Pie-charts do not rank among my favorites, and to build a dashboard entirely of them is definitely not a fantasy of mine. I looked into my collection of excel spreadsheets for a data table. I found one, but unfortunately do not remember where I've downloaded it from. It contains month-wise performance of 4 sales representatives, for 3 products, across 3 customer types, 4 regions and over 2 years. After adding couple of columns to the original data and creating some much needed tables, and I was ready to set up my pies; - Oops, pie-charts. In about two hours, my dashboard is done. Can't help but feel all the pie charts give it a "Pac Man" look. The top section of my "Pac Man" dashboard, And the lower section of it. Overall, it has been some time well spent and so...

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