A while ago I wrote a post on some waterpoint monitoring software we’ve been using to assist district governments in Malawi with water supply information management. The software lets us do a few things:
-
help district government staff quickly visualize data about water coverage in their district, without relying on expensive software or extensive training
-
build enthusiasm for data management and evidenced-based decision making so we can segway into more serious training on data analysis and planning
-
motivate district staff to fund/organize their own data collection surveys
The goal of all of this is to help improve planning of water infrastructure, making sure that new wells go to the communities with the greatest need for clean water, and that aid money is used as efficiently as possible. Right now there are serious inefficiencies in the way money spent on clean water supply is used, and better management of information is one way of addressing this. In effect this is all about aid effectiveness.
My initial post on the software generated a lot of interest. This afternoon I have been working on some user manuals for potential international sharing of the software, and so given the interest people have already expressed, I figured I’d share one of them on this blog. The following set of instructions show how the software can be easily customized for setting up maps in new districts (or even countries).
(As always, acknowledgement to Edward Ng, from the University of Waterloo, as the software’s original author.)
Getting a Base Map
In order to set up a new map in [the software] you will need to get a base map from somewhere, as a .jpg or other image file. For instance, you can obtain a base map by taking a screen capture from GIS software, by finding an existing image file of a map, or even by taking a photo of a hard-copy map using a digital camera.

A screencapture of Karonga District, Malawi, taken using ArcView GIS
Setting Up the Map in Excel
Step 1: Right-Click on the “Map” tab and choose “Unprotect Sheet…”

Step 2: Insert your .jpg base map and resize it to fit nicely into the white rectangle.

Step 3: Click on the “Water Coverage” button and re-locate the legend as necessary by dragging and dropping it. Click on the “Waterpoint Functionality” button and do the same.

Step 4: Click on the “Water Coverage” button and change the words “[District Name]” on the title to the appropriate name. Click on the “Waterpoint Functionality” button and do the same thing.

Step 5: Click on the “Insert” tab, choose “Shapes” and then select the “Freeform” shape.

Step 6: Use the “Freeform” shape to trace a neat outline of each subdivision of the district.

When completed you should have a map of the district with all subdivisions outlined.

Step 7: Select all of the district shapes you’ve just drawn, right-click on them, and select “Send Backwards”. Continue to do this until all shapes are behind your original base map.

Step 8: Trace all of the roads on the map with red lines. Group the lines together and name the shape “Roads”. (This allows the “Roads” checkbox to work.)

Step 9: Trace all of the rivers on the map, group them together, and name the shape “Rivers”.

Step 10: Use the “Freeform” tool to draw outlines of all protected areas, national parks, and lakes. Colour them in accordingly.
`
Step 11: Delete your original .jpg base map. You should be left with a full outline map of the district.

Step 12: Select each district subdivision shape, and change its default name to its real name. (This will allow the software to assign the shape its proper colour based on that subdivision’s water coverage and waterpoint functionality rates.) Note: It can help to turn off the “Roads” and “Rivers” checkboxes first.

Step 13: Once each of the subdivisions has been renamed, select all of the “freeform” shapes on the map and group them together. Name the group “AllMap”. (This allows the “Copy Map” button to work.)

Step 14: From the “Insert” tab choose “Shapes” and then “Textbox”. Use “Textboxes” to label each subdivision in the district, and any other relevant features.

Step 15: Group all of the Textboxes together and name the shape “Labels”

Step 16: Right-click on the “Map” tab and choose “Protect Sheet”.

Step 17: Click on the “Legend” tab. Choose a colour scheme for the map. Click “Update”. The legend on the Map should now have changed. (Note: you can also change the values associated with your colour scheme.)

Step 18: Click on the “Database” tab. Enter some sample data into the Database in order to test the map.

Step 19: Click on the “Pivot Table” tab. Click the “Update” button. A summary of the data should appear on the Pivot Table.

Step 20: Click on the “Map” tab. The Map should now be coloured in based on the data you entered and the colour scheme you selected.


And that’s all there is too it. Generally we start by setting up a map for district government staff, and focus training more on basic data management skills and evidence-based decision making. Once staff become more proficient at managing/analyzing data, we can also train them to set up/modify the map as well. So far this software has been really well received by district government staff, as it works completely offline (e.g. requires no internet connection and all the data is stored on their computers) while also allowing staff to create maps with a fraction of the training required for conventional software.