Excel to Map: Plot Spreadsheet Coordinates

Heading to the field? Install this site from your browser menu and this tool works with no signal. See how

How to use this tool

Drop an .xlsx file above. The first sheet is read, the coordinate columns are found automatically, and every row becomes a point on the map. The line under the status message names which two columns were used, so you can check it picked the ones you meant.

The spreadsheet is read in your browser and nothing is uploaded. That matters more here than with most formats, because the spreadsheets people want to map are client lists, site registers, asset inventories and survey returns, and none of that should be handed to a website in order to look at it.

If your file is a .csv rather than a .xlsx, the CSV to KML Converter takes the same input and produces a Google Earth file at the end.

What your spreadsheet needs

Two things, and nothing else about the file matters. There can be as many other columns as you like, in any order, with any formatting.

First, two columns it can recognise as coordinates. Any of latitude, lat, y or northing will be taken as the latitude, and any of longitude, lon, lng, long, x or easting as the longitude. Case and spacing are ignored, so Decimal Latitude works as well as lat.

If none of the names match, it falls back to looking at the values: the first column whose numbers all fall within plus or minus 90 becomes the latitude, and the first other column within plus or minus 180 becomes the longitude. That rescues files with columns called something unhelpful, and it is a guess, which is why the tool tells you which columns it chose.

Second, the values have to be degrees. Every row is checked before anything is drawn.

A worked check you can repeat on paper

The range check is worth understanding because it catches the two failures that account for nearly every mapping problem, and you can do it yourself before you even open the file.

Take a coordinate pair from your spreadsheet and ask two questions.

• Is the latitude between minus 90 and 90? Latitude runs from pole to pole and stops. A value of 4096742 is not a latitude in any coordinate system on Earth.
• Is the longitude between minus 180 and 180? Longitude wraps all the way round and stops there too.

Now apply it to a real failure. Suppose a column reads 4096742 and another reads 673513. Neither is a degree of anything. Those are metres on a projected grid, and this tool refuses them rather than plotting them, because it cannot know which grid without a projection definition, and there are hundreds of them. Guessing would put your sites in the wrong country.

The second failure is subtler. Suppose latitude reads minus 109.05 and longitude reads 37.0. Both look like plausible numbers. But minus 109.05 is not a valid latitude, so the columns have been swapped somewhere, probably when somebody pasted a coordinate pair written the way people say it out loud. The check catches it, and without the check your sites would appear in the Southern Ocean south of Africa.

That is the whole of it. Two comparisons, and they catch the two things that actually go wrong.

What happens to your other columns

Every column that is not a coordinate becomes an attribute on the point. They appear in the table under the map, they travel with the data if you export it, and if you go on to convert to KML they become the content of the balloon when somebody clicks a point in Google Earth.

Numbers are kept as numbers and text as text, which matters if you later want to sort or total anything. A column of depths stays arithmetic rather than becoming a column of strings that happen to look like numbers.

How the spreadsheet is actually read

An .xlsx is a zip archive full of XML. Inside it there is a worksheet describing rows and cells, and a separate shared string table holding every distinct piece of text once, with the cells referring to it by index. That indirection is why a naive reader shows you numbers where the text should be.

This tool unzips the file in your browser using the native decompression built into the browser, with no library, reads the worksheet and resolves the shared strings properly. It is verified against spreadsheets produced by a widely used third party spreadsheet library, including text containing commas, which is the case that breaks readers that take shortcuts.

Only the first worksheet is read. If your data is on a later tab, move it or save that tab separately.

Mistakes that catch people out

Coordinates stored as text with a degree symbol or a direction letter. A cell reading 37.0 N is text, not a number, and the row will be skipped. Strip it to a plain signed number, with south and west negative.

Degrees, minutes and seconds in one cell. This tool reads decimal degrees. Convert first, and the Coordinate Converter, latitude and longitude to UTM will do it.

A header row that is not the first row. Title rows, logos and merged cells above the data are common in spreadsheets that people actually use, and they push the header down. Delete everything above the header before saving.

Data on the second tab. Only the first worksheet is read, which is worth knowing before you conclude the file is broken.

Expecting a projected grid to work. Eastings and northings are refused deliberately. Convert them to latitude and longitude first, because a wrong map is worse than no map.

Related Calculators and Reference

Getting the spreadsheet onto a map is usually the first step, not the last.

• CSV to KML Converter for turning the same table into a Google Earth file you can send to somebody.
• KML to CSV Converter for the return journey, taking a map back into a spreadsheet you can total up.
• Coordinate Converter, latitude and longitude to UTM for converting eastings and northings, or degrees and minutes, into the decimal degrees this tool needs.
• CSV Cleaner for XYZ data for tidying a messy table before you try to map it.
• GPS Waypoint Logger for collecting fresh coordinates in the field straight into a spreadsheet.
• The RFC 4180, the CSV specification is the authority on how a comma separated file is supposed to be quoted and escaped.