From spreadsheet to map: how to create your first postcode point map

So you have a spreadsheet full of postcodes that you’d like to turn into a visual data map. We’ve spent more than 25 years mapping data and creating mapping tools, so we like to think we know a little about postcode maps.

The challenge of “simply” plotting postcodes on a map

Using postcodes as a geographical key provides its own challenges. The UK postcode system is completely unique, its alphanumeric format has developed organically over more than 100 years. It is specifically developed around our town and county system and is also constantly changing. Some nations use a fully numeric system, others have no nationwide postcode system at all.

This means that postcode mapping requires highly specialist tools and accurate local data, making it unattractive to global, mass-market software providers. For example, neither Microsoft Excel nor Google Sheets will convert a postcode list into a map for you with some kind of nifty inclusive wizard. In fact, both companies have discontinued their dedicated low cost or free mapping tools (MapPoint and Fusion Tables).

The good news is that any programme that can export data into common spreadsheet (such as .xls) or text format (for example .csv) files allows you to go on and map that data. Even better, with the latest low-cost web-based mapping tools you no longer need a powerful, complex or expensive GIS (geographic information system).

An example postcode point map

Above: An example of a typical postcode pin or point map.

Avoiding the pitfalls of postcode mapping

Scaling your data and choosing your map type

Just changing list data into a visual map helps to make it more understandable at-a-glance. However, think of the questions you want to answer before you import your data. A handful of postcodes plotted on a map can be informative and help you to analyse your data and make business decisions effectively. But, if you intend to try and plot any more than a few hundred postcodes you might want to consider breaking the project down or using less granular detail - even a point map will be overwhelmed by this level of data and cease to be an effective visual aid!

For larger datasets:

  • Create a heat map rather than a postcode point map: Heat maps - also known as hot/cold, catchment, territory, or choropleth maps - aggregate individual points of data into a localised trend. They use a colour code to show comparative values and are a great way to make really dense datasets understandable at-a-glance.

  • Break your mapping project down into regional sub-projects: a UK-wide pin map of thousands of customers will overwhelm a viewer, but a “postcode area” map of a couple of hundred will be easier to comprehend.

  • Use a less granular level of detail on your map: On a point map, you could move up the postcode hierarchy to map the “centroid” of the postcode sector, rather than whole postcodes – something commonly done in geographic data analysis to avoid long processing times and difficulties in comprehension of data.

Data quality for better maps

Whatever level of detail you aim for, make sure that your data is consistent and well formatted. The UK postcode system is structured and makes sense but it is not logical as a computer sees it. Computers struggle with something as esoteric as the alphanumeric postcode system so won’t correct you if you have a zero instead of the letter “o” for example.

If you find that your data is missing postcodes or you have suspect postcodes that you’d like to check are correct, there are data providers out there who provide the latest postcode lists. The two we work with are Royal Mail with their PAF product, and the Office for National Statistics with their Office for National Statistics Postcode Directory (ONSPD) dataset.

See how we can help you to supplement your postcode data

A postcode point map contrasted with a postcode heat map.

Above: Less is more. It is easy to overwhelm a postcode point map, whereas a heat map can usefully aggregate data for easier understanding at-a-glance.

Geocoding postcodes for a basic map

A good postcode mapping tool will have a simple import function. MapVision for example is a 3-step import.

  • Select your data: Simply point the programme to the spreadsheet and data column you want to map or “geocode”.

  • Choose your map type: Select either a point map or a catchment/territory map and off you go.

  • Review the process: The third step is a report of any import failures, such as incorrect postcodes. If you’ve prepared your data carefully, you can ignore this step.

Refining your map to get the data you want

Hooray, you have successfully geocoded your data! This means you now have some dots or coloured areas on a map. This first pass often raises more questions than it answers and the move from spreadsheet to visual data map is a difficult one to predict. It could instantly answer the question you originally asked but seeing your data in a geographical context will trigger other questions. On the other hand, you may look at the data and see that your map is overwhelmed or notice sparse areas or data you failed to include. Few people create a single data map and stop there. Geographic analysis is normally an iterative process and there is a very good reason we make it so easy to import and geocode data into a map.

An example import tool from our own mapping program - MapVision

Above: An example import process from our own online mapping tool, MapVision.

Make your postcode map easy for others to understand

Once all of the data you want is included you will need to think about formatting. Odds are, you won’t be the only person who needs convincing of your decision. The default format of any mapping programme is designed to be neutral and inoffensive.

Any mapping programme worth its salt will allow you to theme your maps however you wish:

  • Theme the maps colours on the data subject itself.
  • Match your organisation’s brand colours to present it to the board.
  • Use your own favourite colour scheme.
  • Choose colours for deliberate contrast.

Be aware though, if you are working on a web programme like MapVision, you’ll need to save these themes if you want to quickly and easily apply them to future maps – and we’d recommend consistency between maps once you decide on a scheme.

Visual data analysis needs to be clear and accessible

The whole purpose of visual data analysis is to make the data clear to a viewer. So make sure that any map that will be viewed by others is accessible and easy to comprehend. Mapping comes with a lot of preconceptions that are terrible for those with even minor visual handicaps. For example, the red-amber-green (RAG) scheme so common in heat maps, is awful for a colour-blind person to try and comprehend. In fact, the bold colour themes that worked so well in your spreadsheet to differentiate data are unlikely to work so well on a map. Move away from bold colour blocks and towards softer – but distinct – shades. Using patterns to supplement your colour scheme will help less visual-able viewers comprehend the data and a large key is a must!

A traditional red-green heat map and how someone with deuteranopia (red-green colour blindness) might see it.

Above: A traditional red-amber-green heat map and how a person with deuteranopia (red-green colour blindness) might see it.

Read more about mapping and colour-blindness

From postcode map to full geographical analysis

So now you have your beautiful, clear postcode map, what next? Geographic and visual data analysis is incredibly open ended. There is always something you can add in. An effective mapping system lets you overlay multiple data layers onto your map, so that you can change the map to analyse the most relevant topic at any time. Once you have plotted some data on a map, you will think of a lot of other data that you would like to include in your visual analysis. For example, the locations of customers, sales reps, stores, competitors, distributors, wholesalers, retailers, transport hubs, similar businesses, local facilities, population centres, traffic black spots, demographic trends. The real challenge in data mapping is knowing when to stop and actually make those hard decisions that triggered the process in the first place.

Back to the blog index

We've spent more than 25 years visualising complex data and making it easy to understand.
If you'd like to see if we can help you please get in touch.

online or call us on:

Our other blogs

Living Costs and Food Survey

The Living Costs and Food survey (LCF) is compiled every year and is used by the UK and European governments, Department for Transport (DfT), and Her Majesty’s Revenue and the Customs (HMRC). But what is it, and why should we care?

Cycle to work day

Each year for #cycletoworkday we take a look at cycling statistics across the country and try to map that data and find interesting trends. This is mainly because we at Beacon Dodsworth are either a little bit obsessed about cycling, or we tend to worry about the environment.

Mapping for local projects

Recently, we were contacted by a company responsible for organising charity door knockers. They needed more than 9,000 postcode sectors mapped at A4 size to use at a local level to plan fundraising routes and clearly define territories for each agent.

Social change over 10 years

With the next census due to take place this year, we thought it was a good time to take stock of some of the changes and trends we noticed between the 2001 and the 2011 census. What difference does 10 years make to our society and the people that live within it?

Beacon Dodsworth New Office

Now we are back in the office, we continue to support hybrid working. So, we’ve taken the opportunity to downsize our office to make us more resilient to future lockdowns, staff self-isolation, and any other uncertainty the modern world might throw our way.

Postcode to postcode drive time and distance

What happens if we want a postcode to postcode drive time lookup table?

How to create a postcode map

Turning a list or spreadsheet of postcode data into a series of points on a map isn't as simple as using an Excel wizard to do it for you, but it isn't rocket science. We look at the best way to create a postcode point/pin map.

Administrative Geography

Administrative geography is a way of dividing the country into smaller sub-divisions or areas that correspond with the area of responsibility of local authorities and government bodies. We take a look at administrative geography, what it is and how to use it.

What is geodemographic profiling?

More than 64 million people live in the UK, each with their own outlook, priorities, needs and way of life. Geodemographic profiling offers a way to group these individuals to try and identify the right audience for your product or service.

Who spends most on Fruit and Veg

National Vegetarian Week (#NationalVegetarianWeek) this year ran from 10th to 16th May. It gave us the opportunity to highlight how GIS mapping can be used to create marketing campaigns and raise awareness of the benefits of eating more fruit and veg.

Using geographic intelligence to grow the UK’s broadband network

Using geographic intelligence to sustainably grow the UK’s broadband network.

Data visualisation and colour blindness

John, our director talks about living and working with colour blindness in the mapping industry where colours are pivotal in adding dimensions to people's understanding.

How far is it to the beach

We use Beacon Dodsworth's scripting technology to answer that most important of questions when the sun finally does threaten an appearance.

All you need to know about postcodes but were afraid to ask

The humble postcode has been around for years. We look at how postcodes are used and what led to their introduction.

TimeTravel: the latest update

We look at the latest update to TimeTravel, our dataset of drive times and distances between any postcode sector or district. What has changed in the UK road and geographic network, plus new features to make it even more accurate.

British Population Survey (BPS)

The British Population Survey (BPS) is a survey of household income and shopping habits collected by face-to-face interviews. We take a look at the BPS in detail, what exactly it is made from and how its data can be usefully applied by businesses and public organisations.

Yorkshire Day

As a Yorkshire-based company, we wanted to help celebrate Yorkshire Day, which takes place on 1st August. Naturally, we wanted to put a geographic spin on the celebration, so we took a look at drinking preferences within God’s own county.

The foundations of geographical analysis

Displaying data on maps makes it easier to understand as well as giving a new perspective on a problem. Using a GIS to prepare and present data has become increasingly popular over the last 20 years, but graphical displays of data on maps were around long before computers came along.

How to back up your Prospex data

Keep your GIS projects safe by using the in-built Prospex back up process. Here is how.

The power of postcode sectors

Postcode sectors are aggregations of individual postcodes and they provide meaningful geographical reporting areas in any GIS. However, they aren't as easy to map as you might think. Here is how we do it.

The new normal for the GIS world

Toby, our Account Manager, looks at the changes to working style and client needs in the geodata industry following the COVID-19 outbreak.

Where is the North

We've used the territory manager tool in Prospex GIS to split the UK into north, south and east and west with equal population counts.

What is GIS software?

A Geographical Information System (GIS), is a tool for analysing, visualising, managing and presenting data that is related to a physical, geographical location. That link to geography is the key difference between GIS and other data visualisation techniques.

Mapping GP prescription data

An article by Allan Brimicombe (Head of Centre for Geo-Information Studies at the University of East London) & Pat Mungroo on using GP prescription data to understand health needs.

Geodemographics and the University of East London

The University of East London explain how they have been using our P² People & Places geodemographic classification.

The census helps you to understand your customers

The UK Census 2021: what it is, how is it made, and how can it be used to help your organisation with demographic analysis.

Google Fusion Tables

After almost 10 years of service, Google retired their Fusion Tables product at the end of 2019. This tool was very useful at visualising and sharing large amounts of tabular data - particularly amongst small and mid-sized businesses. So what can we do to fill the gap left by this tool?

Get in touch
Call  01904 701020, complete   or