Teaser for the weekend: How to get Graphs on Maps in Excel

[Click on the image for a bigger version]

Often business data goes hand in hand with geographical data. Thanks to tools like Google Maps and several associated mash-ups. But often it might be required to show data on a map to create an effect or draw some conclusions.

An example: Lets say you are analyzing annual sales in various States of US. Now usual ways of doing it include tables, bar/pie charts etc. But much more intuitive way would be to use an US outline map with bubbles in each state with their sizes representing segment/product-wise sales.

I had to do a similar analysis sometime back. And I did it in our dear excel itself without much pain (well, it is not so straightforward, but pretty much do-able)

So, here is a teaser for all the R1C1 readers. Try to findout the way for getting the following above graph in excel. No macros used (although, some amount of manual labour was!) The 3 colored bubbles represent 3 different segment/product figures (be it sales, profits, employees or anything you like).

Leave a comment with outline solution if you have one, Or wait till next week to find a simple enough way to do this.


Type: Teasers, Graphs, Data Analytics, Data Representation, Visualization
Applicability: for situations with both {sales, profits, people} and geographical data
Excel Knowledge Required: Moderate to Advanced
Ease of Implementation: Medium


PS: from this post onwards, I will be including the above format table with every post. To make the usage of R1C1 better.