How do you create a thematic map like the one above for your next presentation if you don’t have a mapping tool? Simple, create it in Excel. Easier said than done, right? Well, not really…
You know that you can add shapes to your worksheet, don’t you? So, why not to add shapes that mirror geographic borders (states, counties…)? That’s the rational behind the idea of creating thematic maps (or “choropleth maps”) in Excel.
Unlike the previous poor man’s GIS that uses a “geo-scatterplot” to display data points, this technique do not rely on a more or less creative use of the chart library.
In this screencast tutorial I’ll show you how to create a thematic map and color-code it, based on your own data. You don’t need add-ins or additional software, just a little time to set it up. Please note that this is not intended to replace even the simplest mapping tool, but it’s a simple and fast solution to geo-reference your data and add it to a presentation slide.
To start the tutorial just click the link below:
Screencast: How to create a thematic map in Excel.
Note that you need a map. You can draw it yourself or you can import it. Drawing a States map is simple:
- Import an image to the Excel file (you can use this one, for example);
- Draw the shapes using the map as a reference.
The other option is to obtain a file. You can get an ESRI Shape file from the National Atlas but you’ll have to convert it to Windows Metafile (WMF) or similar format.
Hope you’ll find this technique useful and feel free to suggest any improvements.
If you liked this tutorial you may be interested in How to create population pyramids and the “How-to Edition” of the Excel Dashboard tutorial . And you may consider subscribing to get updated news and tutorials.
[Update: The Clear and Simple blog has several posts about thematic maps. You should check it. Tushar Mehta has a nice tutorial also. His VBA code is more robust but also more complex.]
That was very nice. Will dialog with you more on excel features sometime.
Thanks. Ok.
Nice,
Have a look at “IDD Intégration données Dessin” on the site of DeiXsyS
Jorge,
Thanks for this. How do you recommend converting from ESRI shapefiles to A format that is usable in excel for this kind of activity. I only know of WMF/EMF, but maybe there’s another format I could use? Nothing on the seems to convert from .SHP to .WMF as far as I can see.
pretty cool, and excel is a powerful thing.
It is great this movie to be interactive as well. I like these kind of user interactive educational movies. Thanks for preparing..
By the way, i have a question: i use colors to show the sales quantities over the regions that’s ok. But i want to use patterns to fill the regions in order to show the population density with the sales quantities at the same time. (with patterns i mean diagonal stripes, horizontal crosshatch vs.) How can i do that? vba code doesnt seem to be “Interior.Color” in this case, coz i tried to replace the colors with patterns in the legend, however map displayed only white regions instead of patterns.
Any suggestions?
Someka: in a new sheet, add a shape and start recording a macro. Edit the properties of that shape and under Fill Color select Fill Effects. Then select a pattern. Stop recording the macro and take a look at the code. Copy the relevant lines to your project.
Hope this helps.
My boss wants me to do a “patterned thematic map” instead of a “colored thematic map”. So i am asking these pattern things..
I recorded a macro as you said but i couldn’t find out how to modify it according to my needs. Because pattern property is not defined as color as far as i see. It doesn’t have an equality as in the color line. it is something like:
“Selection.ShapeRange.Fill.Patterned msoPattern5Percent”
How will i be able to give that pattern name (mso..blabla) as a parameter, which can be modified from the excel sheet?
What should i?
Someka: In macro DefColorCodes() find the line:
Selection.ShapeRange.Fill.ForeColor.RGB = Range(Range(“actRegCode”).Value).Interior.Color
and add bellow:
Selection.ShapeRange.Fill.Patterned Range(Range(“actRegCode”).Value).Interior.Pattern
This will create a “patterned thematic map” that uses a the patterns from the legend. When creating a colored thematic map just comment out this line.
Hope this helps.
Great! I like this method.
Thanks for your sharing!
Thanks your sharing, I have made out my chinese data map in excel,by using your method.thanks!
Hi – I am having a hard time importing the my own map. I cannot ungroup it (those selections are greyed out) do I need a certain type of picture? thanks!!!!
I figured it out…. need a wmf file – thanks for a great tutorial!!!
Fred: Yes, I was going to tell you that that was the probable reason…
Thanks!! One more question for you….is there a way to show values on the map? Thanks so much!!!
Fred, if you right-click a polygon you’ll see the option to add text. Here is how this action looks when recorded:
ActiveSheet.Shapes(“Rectangle 1″).Select
Selection.Characters.Text = “abc”
and you can also change font size, color, etc.
It should be easy to add this code to the existing macros.
Hope this helps.
Hi – I imported a map that I am using…. unfortunately, when I right click, there is no option to add text. I guess it is because of the map that I am using. When I tried your suggestion, I got an error that “unable to set the text property of the characters class”
Thanks again for all of your help!!!
Fred
Anyone know where you can get a “free” .wmf or other usable format US State Map… I’ll give a quarter to anyone that can trace a decent looking state map for thematic purposes cause it is certainly not simple… Much thanks
When I wrote this post I thought it would be easier to find those wmf files or some free application to convert from other formats. I was wrong, unfortunately. But for a simple US State map you can easily create it using the method discussed above.
The other option is to ask a friend to past a free shape (ESRI) or TAB (Mapinfo) map into Excel.
Thanks for sharing your knowledge and experience! I learned so much more than what your results from watching your interactive training show. I also learned some techniques that I can use over and over again in other applications. You are a champion on The Web!
I wonder if you can help me ‘discover’ the answer to a challenge I have: how to draw a polygon connecting the vertices at (x,y) coordinates on a scatterchart. I can draw a very nice field of x and y coordinates and plot points on the field. Now, I’d like to draw arrows on the field connecting two or more points, as needed. And, can I allow the user(me) select which plot points I want to connect — interactively?
I believe you just might be able to help me. I can send you a sample of my sheet, if you like, to save you some initial work in building the chart?
“Nothing grows in the soil of despair. Look for the spark of life. It’s in there!”
Hi!
You have done gr8 job. Can we do the same thing with Excel 2007?
Jorge,
Can you please send me copy of the excel file for the excel map.
thank you
Wayne: I’ll send if over the weekend.
Hitesh: When I created the file I had no access to Excel 2007. Let me check. [update: it seems to work as expected.]
My first problem is to get Map Image file as Shape. Please help me out to create the same things with Excel 2007.
I am working for Re-Insurance Company and wana make report for geographically risk accumulation analysis report. I am using OLAP.
Can I possibly get a copy of the excel file for the map you used? I can’t get a wmf map. I would really appreciate it. I am trying to learn how to do the motion charts as well. Great website!
@Jonny: Sure. I’ll e-mail it later today.
Thanks I appreciate it! Is there a way to do the motion chart with out having to upload to google?
I don’t think so. The data source must be in Google’s spreadsheet.
Great guide, thanks! I was just wondering if anyone happened to have a world map that can be used?
Thanks in advance!