Most mapping problems are spreadsheet problems wearing a disguise. You load a CSV into your mapping tool, the basemap appears, and then the trouble starts: a cluster of pins sitting in the Irish Sea, three copies of the same café, and a town that refuses to match anything because it is spelled two different ways in the same column. None of it is the mapper's fault. It is the table's.

Cleaning geodata is unglamorous, but it takes minutes rather than hours once you know what to look for. Here is a routine that works on almost any messy export.

Work on a copy and audit before you edit

Before a single cell changes, save the original file somewhere you will not overwrite it. Exports from databases, CRM systems and council open data portals are rarely tidy, and you will want something to compare against. Do your edits in a copy, and add a second tab called changes where you note what you altered: which rows you deleted, which coordinate column you converted. In a fortnight, that note will be the only thing explaining why the map looks the way it does.

Then spend five minutes on an audit. Freeze the header row so you can scroll without losing the column names. Check the far edges of the sheet for trailing blank columns, a totals row at the bottom, or two header rows stacked on top of each other. Merged cells cause havoc on import, so unmerge them and fill the values down. Aim for one clean table per sheet: one row per feature, one column per attribute.

Remove exact duplicates first

In Excel, select the table and use Data > Remove Duplicates. In Google Sheets, try Data > Data cleanup > Remove duplicates. In LibreOffice, Data > More Filters > Standard Filter with "No duplications". Whichever you use, sort by the column you expect to be unique — a reference number, a postcode plus name, an asset ID — and scan the top and bottom of the sheet first. Sorting puts identical rows next to each other, and problems are much easier to spot when you can see them.

One warning: duplicates are not always errors. Two pharmacies in the same building, three bus stops on one street, a venue with two entrances. If your data has a unique identifier, deduplicate on that. If it does not, deduplicate on a combination of fields that should genuinely be unique together, such as name plus full address.

Track down the near-duplicates

The harder problem is the row that is almost identical: "Kings Head" and "The King's Head", or "12 Mill Lane" and "12 Mill Lane, ". Normalise first, then compare.

Add a helper column and strip out the noise:

=TRIM(PROPER(A2))&"|"&ROUND(B2,5)&"|"&ROUND(C2,5)

That trims spaces, tidies capitalisation and rounds coordinates to five decimal places, then joins them into a single key. Duplicates in the key column point to rows worth a second look. Do not delete automatically — review each group and decide which record to keep.

Fix the coordinates

Coordinates break more maps than any other field. Three faults account for most of it.

Latitude and longitude swapped

Some exports put latitude first, others longitude, and nothing complains because both are numbers. The giveaway is magnitude: UK latitudes sit roughly between 49 and 61, while longitudes run from about -8 to 2. If your first column reads -1.5 and your second reads 53.4, the pair is the wrong way round. Swap the columns and the pins land where they belong.

Degrees, minutes and seconds mixed with decimal degrees

Most mapping tools want decimal degrees. Convert with a simple sum: degrees plus minutes divided by 60, plus seconds divided by 3600. So 51°30'26"N becomes 51 + 30/60 + 26/3600 = 51.5072. Watch the compass letters — north and east are positive, south and west negative. If a cell holds text like 51°30'26"N, split the parts with text-to-columns before you calculate.

Coordinates in the wrong system

Six-figure pairs such as 530000, 180000 are not latitude and longitude at all; they are Ordnance Survey eastings and northings, or some other projected grid. They need converting to WGS84 before web maps will accept them, and the maths is not worth doing by hand. Use a proper converter and record which system the original data came from.

Finally, scan for blanks and values outside the plausible range. A single missing minus sign can move a point from Cornwall to the North Sea.

Standardise place names and addresses

Names arrive with inconsistent spelling, punctuation and abbreviation. A few passes will fix most of it.

• Remove leading, trailing and double spaces with TRIM, then deal with non-breaking spaces using SUBSTITUTE(A2,CHAR(160)," "). These are invisible and will silently break matching.
• Strip stray apostrophes at the start of cells, which spreadsheets add when they treat a number as text.
• Harmonise abbreviations in a find-and-replace pass: St to Street, Rd to Road, Mt to Mount. Check first, though, because "St Albans" is not "Street Albans".
• Use PROPER sparingly. It turns Bourton-on-the-Water into Bourton-On-The-Water and mangles names such as McDonald. A replace list is safer.
• Format postcodes consistently: upper case, single space before the final three characters.

Where you have a canonical list — a local gazetteer, or a lookup tab of approved names — compare against it and correct in bulk rather than one cell at a time.

Watch the formatting traps

Three quiet corruptions are worth checking before export. Long numbers may display in scientific notation (5.3E+05), so set coordinate columns to plain numbers with at least six decimal places. Identifiers with leading zeros lose them unless the column is formatted as text. And if your place names include Welsh or Gaelic characters, save the CSV as UTF-8 so nothing turns into question marks.

A pre-mapping checklist

1. Original file backed up and unchanged.
2. One table per sheet, no merged cells, no totals rows.
3. Exact duplicates removed on a sensible key.
4. Near-duplicates normalised and reviewed.
5. Coordinates in decimal degrees, correct order, plausible range.
6. Place names trimmed, abbreviations harmonised, postcodes consistent.
7. Changes documented on a notes tab.

Run that list and your import will behave. If something still lands in the wrong place, the fault is usually a single column you forgot to check — so sort by longitude and look at the extremes before you blame the software.

Keep the cleaning with the data

Save your cleaned file with the date in the name, and keep the notes tab attached. The next person to open it — possibly you, six months from now — will want to know why "The King's Head" became "Kings Head" and which coordinate system the original used. A tidy spreadsheet is not just easier to map; it is evidence of decisions you will otherwise forget you made.

Photo: Yan Krukau / Pexels