Blurring and plotting OS Grid References

This is a post about a quick and easy method for turning a list of GB Ordnance Survey grid references into "eastings & northings", so you can plot them on a map. If you want, just skip the details and download my Excel Conversion Utility spreadsheet that can:

• convert your grid references into Eastings and Northings
• tell you the distance of all your records from another grid reference (i.e. a site centroid)
• dumb down your grid reference to 1km, 2km, 10km or 100km resolution.

Eastings and Northings

Most British people interested in nature and the outdoors will be very familiar with the concept of the OS grid reference. What I didn't realise at first is that the grid references have been designed to turn Britain (Ireland have a separate system) into a nice flat plane, and any grid reference tells you how far east and north you go on that plane in metres from a mythical origin off the coast of the Scilly Isles. This is a bit disguised, because the leading digits of the grid reference are converted into letters instead of numbers. But thanks to the excellent information available on the internet, and plenty of people doing this before me in different ways, I created a handy spreadsheet that converts the grid references commonly used by wildlife recorders into Eastings and Northings... or (x,y) cartesian coordinates as I think of them as a mathematician rather than a geographer.

Now it is really easy to map your grid references. In fact you don't even need to use GIS software, you can just plot the easting against the northing in Excel as a scatter graph, but it is a bit tricky to make something meaningful. More usefully, you can import the spreadsheet results into a GIS as point XY data.

Common Hawker (c) Cumbria Biodiversity Data Centre. Left - to prove you can plot in Excel, although using a GIS is better! Right - Contains Ordnance Survey data © Crown copyright and database right 2011

In my conversion spreadsheet you'll notice there are two eastings and two northings given for each grid reference. The centre of the square denoted by the grid reference and the SW corner of the square are both useful at times. The SW corner is what the grid reference itself gives you - e.g. NY4959 is (349000,549000) but more often you'll want to plot the centre of this square so the centre of the square is given first.

Distance from a site centroid

I added this because often you are interested in how far records are away from you, but don't want all the bother of getting the data into GIS to answer such a simple question. The spreadsheet works out how far the centres of the two grid references are from each other, but also the minimum and maximus distances based on the grid references. For example say you have a record of an orchid at 8 figure grid reference, and your site is a 1km square (4 figure grid reference). If the orchid is located in the 1km square then the distance from the centre might be 400m but the minimum distance would be be given as 0m and the maximum as slightly over 1000m.

Dumbing Down Grid References

At LRCs we generally try and persuade people to be as precise as they can about the grid reference for where they saw something, within reason. If you are doing an atlas survey it may well only be practical to record a list of species for each tetrad. (Don't know what a DINTY tetrad is? Click here.) But otherwise just because you want to get a tetrad list is no reason to record that way - you can easily derive that from a six figure grid reference. How? I hear you say... easy just put your list of grid references into the second worksheet of my spreadsheet and it will do it for you!

Resources

• My spreadsheet which works as described above. Grid Reference Conversion to Eastings and Northings Utility It contains macros with the code in so if asked you will need to see Yes to enabling macros when opening. Please let me know if you notice it doing anything incorrect - I've done my best to remove bugs but can't be sure I have uncovered them all - I'm not a proper programmer. Please don't sell it but feel free to use and adapt the VBA code etc.
• A more sophisticated Excel add-in by Phil Brady will do most of this plus a whole lot more, such as fancy conversions such as to and from grid references and longitude/latitude - very useful in these days of GPSes. It isn't aimed at recorders so you may find it a bit less user-friendly and also it doesn't know about DINTY tetrads. Take a look at the rest of the website for more information on OS grid references and GPS.
• According to the NBN forum apparently there is a JNCC excel add-in which will do much the same, which you can get hold of by contacting the NBN.