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 }
← Previous Comments
Jaws: I think that’s one of the “features” in Excel 2007: it doesn’t record most changes in charts and shapes. I’ll try to create an “Excel 2007 – friendly” version.
yeah.. how does it work with excel 2007?
thanks a lot!
I’m making a 2007 version. It will be available soon.
Wow! Thanks!!! I knew Excel could do this. I have been looking for this for more than ten years now. I will be sure to look at the rest of your site, Jorge.
Never mind.
Took me some time, but this is my solution:
Selection.ShapeRange.Fill.ForeColor.SchemeColor = Range(Range(“actRGScode”).Value).Interior.ColorIndex + 7
Now it works with costum palette
Hi, Very interesting, your mapping tool for excel data. But it seems not so easy for us. But, could you do some work for us and provide us a Chart of Luxemburg with the county boundaries (comunas). We could of course provide your with the official graphs that are available.
Can your make us an offer for such a job?
Muito obrigado pela sua ajuda.
M. SIMONIS
Great map idea!!!
I had it done for Europe and works!!!! My data is in %.. do you know how can show this data onthe righ position in the map with the right format…
Great stuff and thanks for sharing your knowledge
Please is there any software converit PNG picture to re-colour WMF
any1 knows how to make PNG like those fillable pieces in the US map
I’ve successfully duplicated this code and a custom map for my purposes in XL2007. I have need to run this on XL2000 but the code fails with the error:
Run-time error ‘1004′:
The item with the specified name wasn’t found.
One might assume this to mean that I didn’t have a shape named but in fact I just saved the XL2007 worksheet in XL2000 format and immediately ran it. I did check after the error appeared that the shapes were named and they are. Can someone give me some suggestions. Is the code different if using XL2000?
Thanks
Those of you with an interest in creating ‘thematic maps’ within Excel may be interested in the example at: http://www.mapbrix.com\Mapbrix_CrimeMap_Sample.zip
The buttons add / remove the data and can be built up. The data is sample data and the map geometries have been sourced from the recent release by Ordnance Survey in the UK.
As you will see not all the legends have been completed at this time.
It was built on Excel 2000.
Regards
Ian
excel (at) mapbrix.com
Worked Great! thanks
This was fun to do. I have a couple questions. Virginia is spelled “Virgina” on your map and I can’t change the name. Also, when I mouse point to Hawaii it doesn’t display the name like it does for the other states. Any suggestions?
Thanks,
Scott
This is fantastic. I am learning more and more about excel. It is such a powerful resource and its great that there are users out there that are willing to put in the time to help people like me realize its functionality.
I do have a question. When using a map with several hundred picture objects, ie..a state zip code map, is there anyway to name all the picture objects with their corresponding zip codes without having to do each by hand?
Thank You,
← Previous Comments
{ 2 trackbacks }