Google Maps is ideal for vehicular navigation, but less so for exploring the great outdoors on foot. It’s justly popular as a Geographical Information System (GIS) with which you can publish maps with places of interest superimposed as pushpins, but the setup isn’t as flexible as it might be.

If you want to display multiple points using geographic data from a file, as opposed to adding each point manually, it’s a tricky programming exercise. And if your locations are National Grid References (NGRs), conversion to latitude and longitude is required. This is either a laborious manual exercise using an online utility or one involving difficult programming. More significantly, the base maps can only be those provided by Google Maps. OpenSpace Mapbuilder instead uses Ordnance Survey maps.

But what if you had a geological project and need a geological survey map, an archaeological project that needs a historical map, or maybe you just prefer Harvey’s Maps? Our solution is to use Microsoft Excel, plotting your data as an XY scatter plot and using any map as the background. You can obtain the maps electronically or scan them in, but consider copyrights if you plan to publish them online.

Here, we’ll use a familiar Ordnance Survey Landranger (1:50,000) map that you can download for free. We’re using only a segment of this map, so use a photo editor to crop in on the most north-westerly 5x5km section and save it as a Jpeg file.

To exactly follow our workshop, you’ll need to use our data for the locations of blackbirds spotted on a hypothetical ornithology field trip. The NGRs are SX831955, SX830960, SX843981, SX845985 and SX839985. Later in our workshop, we also add the locations at which swallows were seen (SX821954, SX820958, SX828959 and SX805993).

You can use any version of Excel; we’re using Excel 2007.

Pinpoint a route

Step 1. Create an Excel workbook and rename two worksheets as ‘Main’ and ‘AtoZ’. Put a title in cell A1, and the headings ‘Description’, ‘Grid Ref’, ‘Easting’ and ‘Northing’ in A3 to D3 on the ‘Main’ worksheet. Enter a text description such as the time below and the NGRs for blackbirds (provided in the introduction).

Ordnance Survey mapping step 1

Step 2. In the ‘AtoZ’ worksheet, type the column headings ‘Letter’, ‘Easting’ and ‘Northing’ in cells A1 to C1. Type the letters A to Z, with I omitted, down column A and starting in A2. Type 0, 1, 2, 3, 4, 0, 1, 2, 3, 4 and so on into B2 to B26, and 4, 4, 4, 4, 4, 3, 3, 3, 3, 3 and so on into C2 to C26, ending with 0 in C26.

Ordnance Survey mapping step 2

Step 3. In the ‘Main’ worksheet, type the first of the following formulae into cell C4 and the second into D4, before copying them down into all rows for which you entered data in Step 1:

=500*LOOKUP(LEFT(B4,1),AtoZ!$A$2:$A$26,
AtoZ!$B$2:$B$26)+100*LOOKUP(MID(B4,2,1),
AtoZ!$A$2:$A$26,AtoZ!$B$2:$B$26)+VALUE
(MID(B4,3,3)/10)

=500*LOOKUP(LEFT(B4,1),AtoZ!$A$2:$A$26,
AtoZ!$C$2:$C$26)+100*LOOKUP(MID(B4,2,1),
AtoZ!$A$2:$A$26,AtoZ!$C$2:$C$26)+VALUE
(RIGHT(B4,3)/10)

Ordnance Survey mapping step 3

Step 4. Now select the block of cells containing the eastings and northings (but not the headings) in columns C and D. In the Charts group of the Insert ribbon, click on the Scatter icon and then, in the drop-down menu, select the top-left option. An XY (scatter) chart will appear but points will be plotted on the default background.

Ordnance Survey mapping step 4

Step 5. Right-click the plot area and select ‘Format Plot Area’ from the drop-down menu. Select ‘Picture or texture fill’ as the Fill option, then click the ‘File’ button and select the Jpeg file containing the Ordnance Survey sample map. Make sure ‘Tile picture as texture’ isn’t selected. Your data points will be overlaid on the map.

Ordnance Survey mapping step 5

Step 6. The chart and the map won’t usually line up. Work out the co-ordinates of the map’s SW and NE corners – here, these are SX800950 and SS850000. Enter the NGRs into spare rows in column B and copy down the formulae in rows C and D. You’ll discover the corner co-ordinates are (1280, 595) and (1285, 600).

Ordnance Survey mapping step 6

Step 7. Right-click the numbers labelling the chart’s horizontal axis and select ‘Format Axis’ from the drop-down menu. Under ‘Axis Options’, select Fixed for both Minimum and Maximum, then enter the map’s minimum and maximum easting (1280 and 1285).

Do the same for the vertical axis, this time using the minimum and maximum northing (595 and 600). The data and the map will now line up but you’ll have unnecessary ticks and numbers labelling the axes.

Right-click the numbers labelling the chart’s horizontal axis and select Format Axis. Under ‘Axis Options’ select None for both ‘Major tick mark type’ and ‘Axis labels’. Repeat for the vertical axis.

Ordnance Survey mapping step 7

Step 8. You’ll also find the chart has major gridlines on the vertical axis. Click on one of the gridlines to select the set, then press Del. The map won’t necessarily have the same scaling horizontally and vertically. To correct for this sort of distortion, select the entire Chart Area and rescale it as necessary.

Ordnance Survey mapping step 8

Step 9. If you’re only plotting one data series, delete the unnecessary legend for Series 1 by clicking on it and pressing Del. We’re going to add a second data series, however, so we’ll give the series a name. Right-click it and choose ‘Select Data’. Click Edit in the first window and enter ‘Blackbirds’ as the Series name in the second.

Ordnance Survey mapping step 9

Step 10. Following the instructions in Step 1, but now in columns F, G, H and I, add column headings for another data series and then enter the information for swallows that we provided in the introduction. Copy cells C4 and D4 to H4 and I4, then copy these down to the other occupied rows in column H and I.

Ordnance Survey mapping step 10

Step 11. Right-click in the Chart area and choose ‘Select Data’. In the first dialog box click Add, and enter ‘Swallows’ as the Series name in the second. Click in ‘Series X values’, then select all the easting values in column H. Click in ‘Series Y values’ and select all the northing values in column I, having first deleted the ‘={1)’.

Ordnance Survey mapping step 11

Step 12. We’d like our data to display independently. Edit the formulae in C4 and D4, adding $B$2*( at the start and ) at the end. In H4 and I4, use $G$2*( at the start and ) at the end. Copy these formulae down as before. A value entered in B2 now controls the display of blackbird data; G2 controls swallow data.

Ordnance Survey mapping step 12

Step 13. The information in the ‘Description’ column hasn’t been used so far, but there are occasions when it would be useful to display it as a label against each point. This can’t be done natively in Excel. Close Excel, then install XY Chart Labeler. Restart Excel, enable macros and open your workbook.

Ordnance Survey mapping step 13

Step 14. Select the map. In the ‘XY Chart Labels’ ribbon, click ‘Add Labels’. In the dialog box, select the data series you want to label. Click in ‘Select a Label Range’, then select all the entries under the ‘Description’ heading on your worksheet for your selected data series. Do the same for any other data series.

Ordnance Survey mapping step 14