Clearly and Simply

  • Build your own Choropleth Maps with Excel

    Amendment #6 to Choropleth Maps with Excel: a workaround to transfer the names of regions from a svg file into xls

    Choropleth Map USA by Congressional Districts - click to enlarge

    In a comment on Multicolored Choropleth Maps with Excel Dave Hammer pointed to a couple of excellent maps on Wikimedia Commons in svg file format (scalable vector format). Dave wanted to use these maps to create choropleth maps with Microsoft Excel, but he hit a roadblock with regards to the texts (names of counties or districts) associated to the shapes in the svg file: after ungrouping the map in Excel, the shapes were available, but the associated names were gone. In his example (counties or congressional districts), it would be a lot of laborious work to assign the names to the shapes manually. And it would be error-prone as well.

    Actually there is a workaround to let Excel do most of the work. This post describes this workaround and provides a template of a choropleth map of the United States by congressional districts for free download.

    (more…)

  • Choropleth Map Template USA by Counties

    Amendment #5 to Choropleth Maps with Excel: a Map Template for the United States by Counties

    Pop Soda Coke by US Counties - click to enlargeIn a comment on Multicolored Choropleth Maps with Excel Dave Hammer wrote

    “… I'm doing some mapping right now by County and Congressional District. Some folks at Wikipedia have supplied some excellent .svg maps with proper labels […] but I'm hitting a roadblock with Excel. […] They're easily imported into Excel as an enhanced metafile and then ‘ungrouped’ to create an excellent map – but all the data associated with the shapes is lost during this process. […]”

    Actually there is a way of converting .svg files (scalable vector graphics) into Microsoft Excel without losing the data associated to the shapes. The next post will describe a detailed how-to tutorial, but I thought you might be interested to see the results in a sneak preview. This post offers free downloads of choropleth maps of the United States by county, in the classic and the multicolored version.

    (more…)

  • Choropleth Map Templates elsewhere

    Amendment #4 to Choropleth Maps with Excel: Map Templates for Romania and England

    Choropleth Map Romania - click to enlargeIt turned out to be choropleth map summer here on Clearly and Simply. Originally I planned to have only two posts on choropleth maps with Microsoft Excel here and here.

    Surprisingly the templates were very well received by the readers: I have noticed an unusual high amount of page views and downloads. Some readers obviously took the templates like a duck to water, produced their own maps and were kind enough to allow me to share the workbooks here (see Paulo’s map of Portugal and Gabriel’s map of Brazil).

    Choropleth Map England - click to enlarge

    Jeff Weir, member of the month in July 2009 over at Chandoo's blog, wrote a thorough, inspiring and funny guest post called We can’t cure cancer, but we can cure this medicare chart [Chart Busters]. Jeff obviously doesn’t like Choropleth Maps very much (and mentions a couple of good reasons in his text), but he was kind enough to include a link to my article Choropleth Maps with Excel. Many thanks, Jeff.

    Furthermore, other bloggers recently published new templates:

    Fabrice Rimlinger (of Sparklines for XL fame) posted a map template of Romania and Phil Quinn offered a map of England by ceremonial counties for free download.

    Many thanks to Fabrice and Phil.

    Many thanks again go also to Tushar Mehta who made all these choropleth map templates possible by sharing his brilliant idea here.

    I have to admit that I am not using choropleth maps very often for my own work. However, because of the great response I received from all of you, I decided to stick to this topic a little longer: I am planning to publish another 2 or 3 posts on choropleth maps during the next weeks, before we will continue with new ideas, dashboards and visualizations here on Clearly and Simply in September.

    Stay tuned.

  • Choropleth Map Template France

    Amendment #3 to Choropleth Maps with Excel: a Map Template for France

    Choropleth Map France - click to enlarge At the risk of boring you: here is another choropleth map template, this time France by departments (without départements d’outre-mer and territoires d’outre-mer) :

    Download Choropleth Map France (Microsoft Excel 2003 zipped, 113.4K)

    This one is for you, Fabrice. Consider it as my humble acknowledgement for your brilliant work on Sparklines for XL. I know you are working in Romania for the time being, but I thought having a choropleth map of your own country might come in handy some later day.

  • Choropleth Map Template Brazil

    Amendment #2 to Choropleth Maps with Excel: a Map Template for Brazil including an alternative approach

    Choropleth Map Brazil - click to enlargeOnce more a Choropleth Map Template arrived (see also Paulo’s Portugal Map).

    This time Gabriel Eiras Villa kindly contributed a choropleth map of Brazil:

     Download Choropleth Map Brazil (Microsoft Excel 2003, 129.5K)

    But Gabriel has even much more to offer and share. He had a brilliant idea of how to simplify Tushar Mehta's approach:

    Gabriel suggested to use one color (e.g. black) and simply change the fill color transparencies of the shapes according to the values of the regions instead of defining a color gradient table and assigning different shades of grey.

    (more…)