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

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.

Choropleth Map Template USA by Counties

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

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

Choropleth Map Templates elsewhere

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

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

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.