How do you create a 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…
Following the “geo-scatterplot”, 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.
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 “Howto Edition” fo the Demographic Dashboard. And you may consider subscribing…
{ 64 comments… read them below or add one }
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!
Well, that can be arranged… I may have something in the near future.
Great tool, worked very fine with me. Made it for the Thailand provinces. I found a Powerpoint compatible map with Google by adding format PPT. Especially in the health sector there are quite a few PPTs with maps in the right foramt for this great Excel tool.
In the case of Thailand I still had to “name” the 76 provinces, but the result was very satisfying.
@askan: Great tip! I was so focused on converting maps from Mapinfo or ESRI that never thought of checking PowerPoint presentations. It was very easy to find a US states map by filtering for PPT (here is one).
Thanks, this is great. Can I please have a copy of the excel spreadsheet?
Thanks in advance.
Dan, just send me a message and I’ll email it back to you.
Hi Jorge,
I’ve sent a message via email.
Greetings,
Danieel
Hi Jorge
The following website has free outlines for almost every country incl. provinces/states in a variety of formats incl. wmf which is extremly helpful. It is from a French guy, but the site is also in English http://bit.ly/NuEey.
So your tool is becoming even more perfect/tremendously useful.
Thanks again.
Askan.
Thanks for the tool. I can not even get the vlookup to work. The version of Excel2007 that we use is not allowing the vlookup to work. I was able to follow the video and the first vlookup worked fine. The second gave me an “#N/A”. Can you please help?
Great code but for me it didn´t work in XL2000. Eventually I put in the line
Selection.ShapeRange.Fill.Visible = msoTrue
and voila – it worked!
I considered the approach outlined here but decided to use a Google Spreadsheet gadget. Just upload the data to google spreadsheets, insert a gadget, and several map gadgets are available. Much simpler option.
EddieO: Sure, that’s a good option. You should always use the best tool available. I see people using Excel as a word processor, and that’s absurd. But often you want to use a map that is not available or is expensive. In this case, creating a map in Excel is the best option. (As you know, the list of available maps in the Google gadget is very limited.)
Hi Jorge!
When I grab the macros In Excel 2007 (select a the square, change color, and deselect), nothing happend. Don grab that action.
Could you help me?
Thanks
@Sebastian: You must enable macros when opening a xls file.
Hi Jorge
When I test the Macro the code fails on he line:
ActiveSheet.Shapes(Range(“actReg”).Value).Select
Whit the error:
Run Time Error ‘-2147024809 (80070057)’:
The item with the specified name was not found
Any ideas? I named cell i10 as “actReg”
Thanks
Paul
@Paul: usually that means that the shape was not found. Make sure each shape has a proper name (not “Shape 35″).
Thanks Geoerge
Bare with me. It now fails on the next line:
Selection.ShapeRange.Fill.ForeColor.RGB = Range(Range(“actRegCode”).Value).Interior.Color
with the following error:
Run-time error ’1004′:
Method ‘Range’ of object’_Global_failed
Thanks for your help!
Search for custom USA heat maps on eBay & you can save a lot of time !
D
Thanks for the great procedure. Would you be able to email me the WMF that you used for this example? Thanks.
Hi Jorge, I can’t get it to work in 2007. I insert the autoshape then record the macro when I change its color. When I go back to edit the macro, its empty…no code.
Help!
Ohhh….and All macros are enabled (in Excel 2007) but still nothing.
{ 2 trackbacks }