Pages Menu
TwitterRssFacebook
Categories Menu

Posted by on Nov 20, 2015 in Information Management, Mapping, What's new | 4 comments

Tutorial: Choropleth maps with Excel

Tutorial: Choropleth maps with Excel

Example of Choropleth map (MSF)

Example of Choropleth map (MSF)

Introduction

This tutorial’s aim is to show how to create a Choropleth map with Microsoft Excel. A choropleth map is a thematic map in which areas are shaded or patterned in proportion to the measurement of the statistical variable being displayed on the map, such as population density or per-capita income. “The choropleth map provides an easy way to visualize how a measurement varies across a geographic area or it shows the level of variability within a region” (Wikipedia). Excel choropleth maps can be an easy tool for non-advanced GIS users for regular updates of a specific situation in a given area (evolution of an epidemic for example). This tutorial shows how a GIS specialist can create an Excel mapping tools, that can then be managed independently by any user.

Requirements

The Excel mapping tools work with Macros. To avoid compatibility issues prefer Microsoft Excel version 2007 or newer.

Principle

The principle is simple, we will insert a .emf (enhanced meta file) document in an Excel spreadsheet , separate the image and define a name for each form / label to link information in the Excel worksheet to forms. VBA script present in the template will vary the colors of each shape according to the set values.

Set up

Download the original xlsmaptools_template.xls and make sure to save the original template in the Source folder to retrieve it if necessary. Be careful not to add / remove lines into the document, copy and paste only the values​​. This template is made with an MSF style. To change the header, go to “page layout” and change the logo accordingly.

.emf creation

1. With ArcGIS : Make sure your map display don’t contain any specific symbology (no filling) or any labels, then export your .emf file in your workspace:

excel-choropleth (2)

   2. With QGIS :

Make sure your map display doesn’t contain any specific symbology (no filling) or any labels, then export your map as .svg in your workspace: excel-choropleth (3)

In Inkscape (download link : https://inkscape.org/), open the .svg file then save as .emf in your workspace.

excel-choropleth (4)

Note: If you only have a picture file (.png , .jpg , .bmp, etc.), online converters are also available to convert pictures into .emf.

Choropleth maps

  1. Copy the xlsmaptools_template.xlsxlsmaptools_template.xls template.
  2. Paste it in your workspace.
  3. Rename it as the name of your new document.
  4. Open your document and enable the macros:excel-choropleth (5)
  5. In Dashboard worksheet, delete the map and all the labels. Keep the legend excel-choropleth (6)
  6. Insert your .emf image (Insert/Image/path of the .emf doc) in your Excel doc: excel-choropleth (7)
  7. Delete the non necessary Freeforms then adapt your image to the worksheet to obtain a good visual.
  8. Ungroup the image. Right click on the image Group/ungroup: excel-choropleth (8) Note: In case of important problems of distortion of your image after ungrouping, insert the image into Microsoft PowerPoint, right click on the image/ Group/Ungroup and select all (Ctrl+A) and copy/paste objects in the Excel worksheet.

 

  • Click in one Freeform and select all (Ctrl+A) (Figure 9). In the Menu toolbar/shape fill select no fill. excel-choropleth (9)
  •  On the Excel file, display worksheets Calculation and Vocabulary (right-click a tab > Unhide): .                            excel-choropleth (10)                           excel-choropleth (11) excel-choropleth (12)
  • Retrieve field values ​​and IDs in the attribute table of your initial shapefile ( .dbf which also opened with Excel): excel-choropleth (13) excel-choropleth (14) Copy/paste in a temporary worksheet then sort them alphabetically or by PCODE: excel-choropleth (15) Don’t mix the PCODES by sorting them!
  • Copy/Paste values for zones and IDs in Location and PCODE rows of Data entry, Calculation and Vocabulary worksheets: excel-choropleth (16) excel-choropleth (17) excel-choropleth (18)
  • If the number of rows of your new Location array exceeds the total number of rows of existing array, copy/paste the formula of previous lines on the Dashboard worksheet (Link to Location array in Calculation worksheet). Do the same on the Value column.
  • If the number of lines of your new Location array is less than the total number of rows of existing array, clear the excess cells on the Dashboard worksheet (Link to Location array in Calculation worksheet)) . Do the same on the Value column.
  • Record the PCODES for each Freeform. Define a name for each Freeform (use your initial attribute table) using the following format: PCODE_1. In this example, CMR004002_1 for Logone et Chari. To determine the name of each Freeform , select a shape, go to Format/Selection pane. In the opened window, double click on the name of the Freeform, then rename it and press Enter. Use the hide/Unhide symbol to check then do the same for the next one. excel-choropleth (18) excel-choropleth (19) Note: If you forget to press Enter, your new name will not be recorded!
  • Create a label for each Freeform. Insert/Shape, select a rectangle then in the drawing tools (click on the shape) Format/shape fill/no fill; Shape Outline/No Outline. Right click on the shape then Modify text to rename your text: excel-choropleth (21)
  • Define a name for each label using the following format: lbl_PCODE_1 (using one ID for each Freeform).
  • To determine the name of each label, select a label (or a shape) , go to Format / Selection pane. In the opened window, click the label name, rename it and press Enter: excel-choropleth (22)

Use the same way to rename all the shapes: excel-choropleth (23)

  • Fill your map information in Data entry worksheet.
  • Hide Calculation and Vocabulary worksheets.
  • Save the document.
  • The VBA script, present in the template will assign the color which corresponds to the defined values: excel-choropleth (24)

Your choropleth map is now ready! MSF Headers and logos are saved in the Excel workbook, you just need to print your map.

If you need to change the values intervals

  1. Display the Calculation work sheet.
  2. Modify the values in Bin column of the intervals array and modify the Legend from Legend column.
  3. Hide Calculation worksheet.
  4. Save the document.

Be sure that the Values ​​Inputs in the legend match with the desired values!

To go forward…

This method can be binding on a map with a large number of entities due to manual recording shapes and labels. However, you can visit the following links for more information: http://www.hypergeo.eu/spip.php?article274 http://robbiecwilson.hubpages.com/hub/How-to-create-a-thematic-or-Choropleth-map-in-Excel-2007-and-Excel-2010 http://www.clearlyandsimply.com/clearly_and_simply/2009/08/build-your-own-choropleth-maps-with-excel.html http://www.tushar-mehta.com/publish_train/xl_vba_cases/0301-dashboard-conditional_shape_colors.htm

4 Comments

  1. Very useful tool, thank you so much!
    I´ve got a problem though: I´ve relabelled everything as instructed, kept the original formulae in the “Calculation” worksheet, and ran the macros, and yet nothing happens to the map.
    There is an error in the following macro’s line:
    Set shp = wks.Shapes(shpname)

    Any idea how to fix this?

    Thanks again!

  2. hi Diego,
    For this kind of error you must have a label name or a shape which doesn’t match. You can click on debugging and in the vb script, in the highlighted code you can rover with your mouse it will give you the name for which one you have an error. Otherwise, just check again all your names, if there is one missing it will not work. I hope my reply will help… just contact me again if not or you can send me your file to have a look on it.
    Enjoy!

  3. This was incredibly helpful, thank you! You might want to include in your tutorial that the shapes have to be named with _1 at the end.

  4. Hi,

    There seems to be a limit of 104 lines for the ‘DataEntry’ sheet. What if my map contains more that 104 shapes for locations? Can I increase the limit in VBA (yet to try) or is 104 the maximum limit?

Trackbacks/Pingbacks

  1. Cartographers for social equality: more on maps | cadacuanto - […] – lots of tutorials, such as a Tutorial on Choropleth maps with excel, Tutorial to collect for ArcGIS offline, …

Post a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

A propos de l'auteur :

Raphaël a rejoint la sphère CartONG en octobre 2014, en étant déployé sur le terrain pendant la crise Ebola. Fort de 10 ans d'expérience humanitaire chez ACF notamment, il s'est reconverti dans la cartographie pour les humanitaires et a fait ses premières armes avec MSF.