How to create a thematic map in Excel

by

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.

create_map_excel_06

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.

[fusion_builder_container hundred_percent=”yes” overflow=”visible”][fusion_builder_row][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”][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.][/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]

93 thoughts on “How to create a thematic map in Excel”

  6. 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?

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

  8. 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?

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

  12. 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!!!!

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

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

  18. 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 🙂

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

  20. 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!”

  24. Hitesh: When I created the file I had no access to Excel 2007. Let me check. [update: it seems to work as expected.]

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

  26. 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!

  28. Thanks I appreciate it! Is there a way to do the motion chart with out having to upload to google?

  30. Great guide, thanks! I was just wondering if anyone happened to have a world map that can be used?

    Thanks in advance!

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

  33. @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).

  34. Thanks, this is great. Can I please have a copy of the excel spreadsheet?
    Thanks in advance.

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

  38. 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?

  39. 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!

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

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

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

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

  45. 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!

  46. @Paul: usually that means that the shape was not found. Make sure each shape has a proper name (not “Shape 35”).

  48. Thanks for the great procedure. Would you be able to email me the WMF that you used for this example? Thanks.

  49. 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!

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

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

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

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

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

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

  61. Those of you with an interest in creating ‘thematic maps’ within Excel may be interested in the example at: http://www.mapbrix.comMapbrix_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

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

  64. 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,

  65. Very basic question from an ignoramus looking for instruction. I want to generate a map for each of nnnn plants growing in an country divided into nn districts.

    1) Have drawn a basemap of the country in Adobe !llustrator. Each district is (currently) a separate layer of the map.

    2) And compiled a spreadsheet (Excel 2003) where each district a column and each plant a row. Where they intersect the cell has a number, say 1-6, indicating that plant’s growth density in that district.

    Please, how do I generate a map for each plant? With each map-file the plant’s name read from Excel row name, and each par (curretly layer) of the map filled with the Excel cell’s colour-number?

    It sounds something simple for eg a VBA program (which I don’t know – only ancient Pascal, & at 76 am probably too ancient myself to learn new programs.) Or something Exvel can do? Or what.

    Any suggestions or offers to write for reasonable fee? Illr and Excel files of course available.

    Wd be very grateful any answers

    Michael Walter
    mswalter32@aol.com

  66. CORRECTED copy of that posted 5 mins ago
    Very basic question from an ignoramus looking for instruction. I want to generate a map for each of nnnn plants growing in an country divided into nn districts.

    1) Have drawn a basemap of the country in Adobe !llustrator. Each district is (currently) a separate layer of the map.

    2) And compiled a spreadsheet (Excel 2003) where each district a column and each plant a row. Where they intersect the cell has a number, say 1-6, indicating that plant’s growth density in that district.

    Please, how do I generate a map for each plant? With each map-file named with the plant’s name read from Excel row name (in column A), and each district (currently layer) of it filled with the Excel cell’s colour-number for that plant-district?

    It sounds something simple for eg a VBA program (which I don’t know – only ancient Pascal, & at 76 am probably too ancient myself to learn new programs.) Or something Exvel can do? Or what.

    Any suggestions or offers to write for reasonable fee? Illr and Excel files of course available.

    Wd be very grateful any answers

    Michael Walter
    mswalter32@aol.com

  67. Bill
    You can write a bit of VBA to do this, but the objects need to be in order (ie. sorted) in such a way that each object gets the correct name as it runs through your macro. It doesn’t take a lot of work to test this – you can start with a ‘follow me’ macro and name a few objects manually to learn and then create a loop in code when you see the logic.

    I think you saw my ward map of London – which I individually typed in – all 624! I then experimented and realised how to do it with code.

    If you are struggling, drop me a line – I only occasionally look here.

    Regards

    Ian
    excel (at) mapbrix.com

  71. Jorge Thanks a ton..
    However I have three queries..
    1) I am not being notified of your comments in my mail.
    2) How can I learn VBA for further enhancement of my VBA skills.I have no clue about them..I try all NON VBA stuff out
    3) I am a construction engineer and i think to visually record progress thematic charts might be a good idea. The monitoring part includes major activities like excavation concreting etc and I want to show them on the map + carry out some insightful informations.Is it possible in excel or should I use some other software.http://www.begraphic.com/create-custom-map-add-in-excel-and-powerpoint is an addin…

  72. Jorge Muchas, Gracias, realmente super util,
    Que bueno que haya personas como usted que les guste compartir su conocimiento,
    Un saludo desde Bolivia

  73. Found my solution for excel 2007 and coloring of the shapes.
    Here is my script:

    Sub MapColorChange()

    For i = 2 To 52
    Range(“actReg”).Value = Range(“ColorSelector!B” & i).Value
    Sheets(“MapSheet”).Shapes(Range(“actReg”).Value).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = Range(Range(“actRegCode”).Value).Interior.Color
    Next i

    Range(“A1”).Select

    End Sub

  74. This web page is so inspiring and educative.Currently, I am a beginner in the Geospatial Science field however, by following Comments,Questions and Answers from practitioners posted here in fact, I have learnt a lot from this page.
    Thank you Jorge and all the participants.

  75. This is great. I made a chart based on Pennsylvania Counties. To be honest, it’s not as useful as many of the dashboards I have been working on, except for one all-important thing – it has a high coolness factor and my bosses love it, and as a result they won’t feel the need to replace Excel with some highly expensive, 3D pie-chart-and-gauge-generating software that also creates colored maps! It was only last year that all our statistics were reported in PDF format. I like to think I’ve had a positive affect, largely due to all the amazing free information available on sites like this one.

  76. Hey there, I hope this great thread is still active….

    I stumbled upon this great piece of excel wizardry in my search for a quick way to setup a color coded map for a presentation. I’m working on OS X (snow leopard) using Excel 2011 for Mac. Everything worked according to what is described here but at running the final macro I get a runtime error (‘-2147483645 (800000003)’). Debug leads me to the line:
    ActiveSheet.Shapes(Range(“actVerk”).Value).Select 

    (I’ve adapted actReg to actVerk for my own convenience because I’m working with shapes based on a historical map of West German Verkehrsbezirke or traffic regions) 

    Paul seemed to have had a similar issue, to which Jorge suggested that the shapes need to be given proper names instead of excel standard assigned ones (Freeform 1 to n, in my case). 

    I’m wondering if this could be my problem as well, since excel 2011 for mac refuses to let me rename the shapes using the Name Box, no matter what name I choose, it refuses to accept. In fact it doesn’t say anything, it just jumps back to the original name after an enter, tab or click. 

    Does anyone have an idea how to solve this or how to work around it? 

  77. Marten: It’s about time for me to update this content, and trying to manage theses massages with error-handling code is one of my priorities. Unfortunately, I have no access to Excel for Mac, so I can’t test the macros. Could you try your file in the Windows version? You can make a simple model with 2-3 shapes and send it to me.

  78. I have the same problem with the same ActiveSheet line of code, only mine is Run-time error ‘-2147024809 (80070057)’: I am using windows though with office 2007. Did you figure out what was wrong with yours?

  79. I am looking for a way to select US state/states by clicking on a map –  and to process the selected states in my VBA code.

    Anyone has any sample for this ?

    Many thanks 

    gonen52@gmail.com

  81. I’m also getting this same error code when using my own data, it breaks on,
     
    ActiveSheet.Shapes(Range(“actReg”).Value).Select giving me “The index into the specified collection is out of bounds”
    If i try to duplicate the 4 box example in the early part of the presentation, it gives
    An error on the same line of code, but says run-time error ‘1004’, range of object global failed.
    Using Excel 2007

  84. hello, great article, may i ask if i can have a file copy? perhaps excel 2007 if you have it? thanks , walt.

  86. Great job!!!

    This was really useful to me. It’s amazing what can be done with Excel.

    However, I’m facing some problems ungrouping my map. Could you please send me your to me?

    Thank you very much!!!

  88. Muito obrigado! Esperam impacientemente para o mapa. Gostaria empresas que oferecem cursos com especialistas como você.
    Saudações do outro canto da Península!

  89. It is a great article, and very very useful for me. I am trying to use your method to make a map for Thailand with illustrator map. I also have a problem with Excel 2007, JAY’s solution solved my problem (thanks JAY). My map is still along way to go, as this is the first time that I try to write a macro (copying yours). I will send my file to you when I finish. Many thanks to you and all the contributors.

  90. Hi – really great solution for this task! Works fine also with EXCEL 2010. Seems that the easiest way to create new maps is a one-layer map in ArcGIS and *.emf-export. After import into EXCEL, and when ungrouping the map, EXCEL requests for reformatting as a drawing object. Useful to test options of the graph being depending on cell size and position (graph PROPERTIES) in order to keep the shape. Next step then is to eliminate unnecessary elements (useful to use parallel the EXCEL Search&Select button), like frames and line elements. An idea is immediately to name and color the polygons which should remain in the application.

  91. This was just great, I have enjoyed making my own map. To make it easier to add extra areas I added some code to count the range, cell c1 in the data worksheet contains the formula =counta(A:A). This returns the number of cells that have text (country names) in it, the macro then uses this as its upper range.

    Sub Macro3()
    ‘
    ‘ Macro3 Macro
    ‘
    Dim intX As Integer
    intX = Range(“data!c1”)
    For i = 1 To intX
    Range(“actReg”).Value = Range(“data!a” & i).Value
    ActiveSheet.Shapes(Range(“actReg”).Value).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = Range(Range(“actRegCode”).Value).Interior.Color

    Next i
    Range(“b17”).Select

    End Sub

    Have fun
    Rob

  92. I got the first part, but I am having trouble getting the macro to run on the map after importing the data. I followed the video step by step and it says “The item with the specified name wasn’t found.” Do I have to define the name of each state on the map for this to work?

Comments are closed.