Guide to adding editable maps to Excel and PowerPoint — written by Helen Campbell
This guide sets out:
1. How to convert a shapefile to an svg file (if the latter does not already exist)
2. How to rename objects in an svg file using Excel so that objects have the correct geographic name, and how to add these as an editable map in PowerPoint
The purpose of this is to offer an alternative to QGIS mapping for those who don’t have or don’t know the software. By offering the individual shapes and names of provinces and/or districts of a certain country, simple maps can be made in any operation.
Glossary
Shapefile = vector data format for Geographic Information System software. Shapefiles consist of a number of inter-related files including:
· .shp = –feature geometry
· .dbf = attributes for each feature
· .shx = shape index format
· .prj = projection description
Scalable Vector Graphics = a vector image format for defining two- dimensional graphics — has a .svg extension
admin levels = as different countries have different ways to describe the way their county is divided up, a universal way of describing it is admin 0, admin 1, admin 2 etc with admin 0 being the country outline, then admin 1 being provinces etc.
Finding and downloading the right files
First thing to do is download the correct files.
The files you will need should be on the OCHA site HDX — data.humdata.org
Search the country you are looking to map and download the .shp zip file for it.
Converting shapefiles to SVG format
1. Go to https://mapshaper.org/
2. Drag and drop the .shp, .dbf & .prj files (for your preferred admin level e.g. all from admin 1) into https://mapshaper.org/ and click ‘import’ (these files must all be added in one go (i.e. select them all in File Explorer — as shown above and then drag them all in at once)).
3. We now need to establish which data field contains the geographic names that we want shown in our maps. To find these, click on the arrow on the right-hand panel and select ‘inspect features’.
Hover over the map sections, looking at the text box in the left corner. We are looking for which line shows the name we are looking for. In this example it is the Province name which is found on the line called ADM1_EN — make a note of this.
4. To add map labels drag and drop the same files (.shp, .dbf & .prj) over the top of the existing map (again do this all in one go as in step 1 — click import).
Click on the dropdown next to the filename in the top menu to check that there are now two layers. Ensure both layers are visible by clicking on the eye icon, so they go green.
5. To convert one of the layers to labels, open the ‘Console’ from the top menu and type the following text into it: mapshaper -points inner -style label-text=ADM1_EN (replacing the text after = with the relevant field name you copied earlier).
Click Enter
You will now see the polygon layer with labels.
6. Click on ‘Export’ in the top panel, select the svg option, and type
id-field=ADM1_EN (replacing the text after = with the relevant field name). Select ‘Export’, navigate to an appropriate file location, name the file and click ‘Save’.
Opening the files in excel
Importing directly into ppt would not work as all objects would be given an automated name rather than the geographical region name. We need to open the file in Excel and use some code to assign the correct region names.
1. Open Excel and go to Insert > Pictures > This Device > Select the relevant SVG from above > Insert
2. The object now needs ungrouping so that each individual object is selectable — Right click > Group > Ungroup. A message will appear — select Yes
3. Go to Home > Find & Select > Selection Pane.
Select the group containing the Freeform Shapes (as opposed to the group containing the Textboxes).
Go to Page Layout > Group > Ungroup
All the objects in your map should now be individually selectable but will all have a name starting with ‘Freeform: xxxx’.
4. We now need to extract the geographical names from the SVG file so that we can replace the ‘Freeform’ names with the correct geographic names. Open a new sheet in Excel and go to File > Open > Browse, and change the file type to ‘All files’. Open the same SVG file — three popup boxes will appear, select Yes, OK and OK
5. Scroll across the table to the id3 column header, scroll down to where the placenames appear, and copy all the placenames.
6. Go back to the Excel Sheet with the map on it and paste them next to the map leaving the top cell blank and leaving one column in-between the map and the placenames. Give the field a title in row 1.
7. Open the Code Window by either:
a. Pressing Alt > F11 and then selecting View > Code from the top menu, or
b. Right-clicking on the relevant worksheet tab at the bottom of the screen and selecting ‘View Code’.
Paste the code below into the window:
Option Explicit
Sub GetShapeNames()
Dim shp As Shape
Dim i As Long
i = 1
For Each shp In ActiveSheet.Shapes
ActiveSheet.Range(“N1”).Offset(i, 0).Value = _
ActiveSheet.Shapes(i).Name
i = i + 1
Next shp
End Sub
Sub SetShapeNames()
Dim shp As Shape
Dim i As Long
i = 1
For Each shp In ActiveSheet.Shapes
ActiveSheet.Shapes(i).Name = _
ActiveSheet.Range(“O1”).Offset(i, 0).Value
i = i + 1
Next shp
End Sub
The first part of the code gets the shape names from the map objects and the second part changes these names to the geographical region names based on the ones that you pasted into the spreadsheet in step 6. Therefore, the first part of the code should reference the column to the left of the pasted placenames (in this case column N), and the second part should reference the column containing the pasted placenames (in this case column O) — change N1 and O1 in the code above to represent the correct columns.
8. Once ypou have changed the column names in the code, make sure the cursor is in the top section of the code that begins with “Sub Get Shape NAmes()” and then click the play button in the top menu bar Then move the cursor to the bottom part of the ode and click the play button again.
If a ‘Runtime error’ appears, just click OK — it should still have worked.
Close the code box
9. Click on one of the polygons — the geographical name should appear in the box on the top left. Do several checks to ensure that the correct geographic names have been assigned to the correct objects/polygons. If there is a mismatch, ensure that there are as many placenames as there are polygons/objects.
Copying the data into PowerPoint
- Select all the polygon and label data from Excel by selecting
Home > Find & Select > Select Objects and then drag the cursor over the entire map — open the Selection Pane (Home > Find & Select > Selection Pane) to ensure that all items have been selected (all should be highlighted as shown on the right). If any are not highlighted, press Ctrl and click on the missing ones.
2. Select Copy, Open PowerPoint and paste the objects into the Powerpoint. If the copied map is too big for the ppt screen, go back to Excel, select all (Find & Select > Select Objects > Drag a polygon around all the objects) > Group, reduce the map size, copy to ppt and then ungroup.
3. Open the Selection Pane in PowerPoint — Home > Select > Selection Pane and change the name of the Labels group from ‘Graphic…’ to ‘Labels’ by clicking twice on the group title and typing in the new name
4. To change the colour of the polygons click & drag the cursor to create a rectangle over the map to select all objects. Unselect the labels by holding down Ctrl and clicking on the ‘Labels’ title in the Selection Pane
5. Click on Home > Shape Fill and select White, click on Shape Outline and select Black
The main source for this guidance was https://www.clearlyandsimply.com/clearly_and_simply/2009/08/build-your-own-choropleth-maps-with-excel.html
