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.
Here is the step by step tutorial of how to convert loyalty free svg files into Microsoft Excel and build your own choropleth map template:
Step 1: Download your map in svg format
Download the map you need. As Dave pointed, a very good source for scalable vector graphic maps is Wikimedia Commons. A search string like “file: US congressional districts” will take you to a map like this one including a link for free download:
Step 2: Convert the map to Enhanced Meta File format
Open the svg file with a vector graphic editor like Inkscape (open source). Click on “File” and “Save as” and choose Enhanced Meta File (emf) as the file format.
Step 3: Insert the enhanced meta file into Microsoft Excel
Open Microsoft Excel, insert the Enhanced Meta File and ungroup the object to freeform shapes. You may have to repeat the ungrouping until you have one freeform for each region. The result will look like this:
Well, we have the shapes in Excel now, but – as mentioned above – the names of the districts that have been assigned to the shapes in the svg file are gone. Excel automatically assigned new names to the shapes like “Freeform 1”, “Freeform 2”, etc. If we want to create a choropleth map, we would have to select a freeform, go to the name box and type in the correct name. And we would have to repeat this for each and every district. This would be a lot of laborious work for 436 districts. Here is an easier way of doing this:
Step 4: Open the svg file in Microsoft Excel
Open a new workbook with Microsoft Excel and browse for the svg file. That’s right. You can directly open a svg file in Microsoft Excel. The svg file format is nothing else than xml based (see further explanations here). And you can open xml files in Microsoft Excel as well. After you opened the file, you will see a worksheet like this (this example is Excel 2007):
A lot of data, but the only column we are interested in is column H. In this example, the header of this column is called “id3”. This column contains all shape names. Copy the data of this column and paste it somewhere into your workbook created in step 3, let’s say in column N.
Step 5: Clean up the list of district names
Now we have to clean up the district names so that they are applicable as freeform names in Microsoft Excel. Select the range with the names and replace characters that are not valid for names in Excel by an underscore. You can simply do this using search and replace (ctrl H).
Step 6: The trick
The trick of this workaround is the fact that when importing the enhanced metafile into Excel and ungrouping it, you lose all the data connected to the shapes, but the order of the shapes is the same as it is in the svg file. All we have to do now is to rename the shapes using two small VBA procedures. Open the VBE (ALT F11) and copy the following VBA code to your workbook:
Option Explicit
Sub GetShapeNames()
Dim shp As Shape
Dim i As Long
i = 1
For Each shp In ActiveSheet.Shapes
ActiveSheet.Range("M1").Offset(i, 0).Value = _
ActiveSheet.Shapes(i).Name
i = i + 1
Next shp
End Sub
Sub SetShapeNames()
Dim shp As Shape
Dim i As Long
i = 1
For Each shp In ActiveSheet.Shapes
ActiveSheet.Shapes(i).Name = _
ActiveSheet.Range("N1").Offset(i, 0).Value
i = i + 1
Next shp
End Sub
Run the macro “GetShapeNames” first to retrieve all freeform shape names Excel assigned to the shapes and write it to column M of your workbook. Do some spot checks to make sure that Excel's freeform shape names in column M correspond correctly to the names of the districts in column N. Call “SetShapeNames” afterwards to rename all shapes with the shape names of column N, i.e. the names of the districts.
Your workbook will look like this now:
The VBA procedures are one off solutions and you can delete them afterwards.
Step 7: Create the choropleth map
I guess I do not have to explain this in detail, so here are the main steps in a nutshell:
- Download any given choropleth map template here on Clearly and Simply
- Open the workbook and delete the existing map
- Insert the map we created in the previous steps by copy and paste
- Delete the data (sheet "data") and insert as many new rows as you need for your map
- Delete all existing data names (“D_XX”)
- Insert the data
- Expand the cell range of the range name “MapNameToShape” on the sheet “Control”
- Insert the list with the shape names we created in step 6 into column D
- Create a list of range names in column C, e.g. using a formula like ="D_"&D63
- Copy the list of created range names and paste it into column H of the sheet “data”
- Select the range G5:H440 and click on Insert, Names, Create and click on “from right column”
- Delete column H
The result
That’s it. Here is a screenshot of the result:
And here is the link to the workbook for free download:
Download Choropleth Map USA by Congressional Districts (Microsoft Excel 2003, zipped 377.3K)
I think with this how-to tutorial, the maps available on Wikimedia Commons and the existing templates here on Clearly and Simply, you should be able to create your own choropleth map templates with Microsoft Excel according to your requirements.
Nevertheless I am planning to write one more post on choropleth maps. For those of you waiting for new ideas: I promise it will be the last one on this topic. It will not be about choropleth maps with Microsoft Excel, though. The next post will discuss choropleth maps with Tableau Software.
As promised, in September we will be back with new ideas and examples of dashboards and visualizations here on Clearly and Simply.
Stay tuned.
Last but not least
Many thanks to Dave for the heads-up, his question and the inspiration to write this post.
Update on August 30, 2009:
I just found another very interesting source for loyalty free maps: D-Maps offers a variety of almost 4,900 maps in different file formats. This might come in handy if you do not find what you are looking for on Wikimedia Commons.
Leave a Reply