Tutorial: Choropleth maps with Excel
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.
The Excel mapping tools work with Macros. To avoid compatibility issues prefer Microsoft Excel version 2007 or newer.
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.
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.
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:
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:
In Inkscape (download link : https://inkscape.org/), open the .svg file then save as .emf in your workspace.
Note: If you only have a picture file (.png , .jpg , .bmp, etc.), online converters are also available to convert pictures into .emf.
- Copy the xlsmaptools_template.xlsxlsmaptools_template.xls template.
- Paste it in your workspace.
- Rename it as the name of your new document.
- Open your document and enable the macros:
- In Dashboard worksheet, delete the map and all the labels. Keep the legend
- Insert your .emf image (Insert/Image/path of the .emf doc) in your Excel doc:
- Delete the non necessary Freeforms then adapt your image to the worksheet to obtain a good visual.
- Ungroup the image. Right click on the image Group/ungroup: 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.
- On the Excel file, display worksheets Calculation and Vocabulary (right-click a tab > Unhide): .
- Retrieve field values and IDs in the attribute table of your initial shapefile ( .dbf which also opened with Excel): Copy/paste in a temporary worksheet then sort them alphabetically or by PCODE: 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:
- 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. 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:
- 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:
Use the same way to rename all the shapes:
- 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:
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
- Display the Calculation work sheet.
- Modify the values in Bin column of the intervals array and modify the Legend from Legend column.
- Hide Calculation worksheet.
- 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