How to Use the Spreadsheet for Converting UTM to Latitude and Longitude (Or Vice Versa)

Steven Dutch, Natural and Applied Sciences, University of Wisconsin - Green Bay
First-time Visitors: Please visit Site Map and Disclaimer. Use "Back" to return here

Information on the UTM system
Formulas for UTM Conversion
Spreadsheet For UTM Conversion

General

After fairly extensive testing using a mapping program that reads latitude, longitude and UTM coordinates, I find that errors using this spreadsheet are typically a few meters, rarely 10 or more. If you find significant errors, check to see whether you transposed digits, used the proper sign for latitude and longitude, etc. Make sure you are using the right datum, too.

Also, consider the accuracy of your data. A degree is about 111,000 meters. Thus, to achieve roughly one-meter accuracy you need coordinates accurate to five decimal places. Four places will give you 10 meters, three will give you 100 meter accuracy.

This is a spreadsheet, not an applet or a program. Unlike a text box in Visual Basic, I do not know of any way to make a cell capable of accepting input and at the same time change its contents in response to another cell. Anything you input into a cell will overwrite any existing formulas. Therefore, if you input decimal latitude and longitude, the dd.mm.ss input boxes will not change. That can't be done in a spreadsheet, so don't expect to see it. There is a display area below that shows the conversions.

In response to one frustrated user: there is no way to get latitude and longitude from UTM coordinates if you do not know your longitude zone and hemisphere. There are four places on earth that have the same latitude and longitude if you omit N/S and E/W. There are 120 places that have the same northing and easting. If you have simply been given a bunch of numbers to convert, you need to go back to whomever collected the data and get the complete information. This spreadsheet cannot do it for you.

On the other hand, if you know about where the data comes from, the spreadsheet will tell you. Enter the approximate latitude and longitude into the spreadsheet. Ignore the northing and easting values and simply read off the zone.

The spreadsheet consists of six sheets, three of which are hidden:

Normally you will not need to access the hidden pages. If for some reason you do, select Format/Sheet/Unhide

If you examine the calculation formulas you will find they include some downright funky numbers like the sine of one second of arc and large powers of ten. These formulas are out of the 1973 Army reference and are pre-electronic calculator, from back in the days of manual adding machines. They were written in those formats to allow the formulas to generate numbers within the range that existing machines could handle. Some day I may revise the formulas to modernize them. In the meantime, they work, and considering how easy it can be to introduce new errors when you revise code, my philosophy is, if it ain't broke, don't fix it.

Main Page

Pretty self explanatory. Select the datum used on your map in the upper left corner.

To convert latitude and longitude to UTM, you can either use decimal format or dd.mm.ss format. The default is decimal. To enter dd.mm.ss format, blank out the decimal entry cells (select the cell and hit delete). You must select north/south and east/west using the N/S - E/W cells; the degree cells will not accept negative values.

As noted above, do not expect to see conversions in the data entry cells. Geographic coordinates in both decimal and dd.mm.ss format will appear in the colord result area beneath.

To convert UTM to latitude and longitude, you must enter northing, easting, the longitude zone and whether your latitude is north or south. If you don't enter a zone, the default is zone 31 (0-6 degrees east).

If you don't know the zone, but do have a rough idea of the location, enter an approximate latitude and longitude into the section for converting to UTM. Ignore the northing and easting values and read the zones. Then enter the zone into the section for converting UTM to geographic coordinates.

Batch Convert Lat.-Long to UTM

To use this page, you need to know how to copy formulas on a spreadsheet. If your data is already in decimal format, copy it into columns L and M

First, prepare your data. The sheet uses coordinates in decimal format, latitude first. If you have dd.mm.ss data, you will need to convert it. Columns D-K (hidden) are provided for that purpose. If your data is already on another spreadsheet, arrange it in the order Lat-deg, Lat-min, Lat-sec, Long-deg, Long-Min, Long-Sec, unhide columns D-K, and copy your coordinates into columns D-I. Copy the formulas in columns J and K as far down as you need to to convert your data to dd.mm.ssss format.

Now copy your data into columns L and M. Select the data from columns J and K, select Edit/Copy (or Ctrl-C) then select Edit/Paste Special/Values. This will copy the data alone without formulas. The reason you need to copy columns J and K into L and M is because of the mechanics of spreadsheets. If we were to use columns J and K both for display and data entry, we would overwrite the formulas used for converting dd.mm.ss data to decimal format.

Copy the formulas in columns N-AG down to the bottom of your data. The UTM coordinates and longitude zone are in columns AE-AG. You can then copy the results back to your own data processing applications.

Batch Convert UTM to Lat.-Long

To use this page, you need to know how to copy formulas on a spreadsheet.

Enter your hemisphere, longitude zone, northing and easting in columns D-G. Copy the formulas in columns H-AA down as far as necessary to do the conversions. Latitude and Longitude are given in dd.dddd format in columns AB and AC, and in dd.mm.ss format in columns AD-AI

References

Snyder, J. P., 1987; Map Projections - A Working Manual. U.S. Geological Survey Professional Paper 1395, 383 p. If you are at all serious about maps you need this book.

Army, Department of, 1973; Universal Transverse Mercator Grid, U. S. Army Technical Manual TM 5-241-8, 64 p.

NIMA Technical Report 8350.2, "Department of Defense World Geodetic System 1984, Its Definition and Relationships with Local Geodetic Systems," Second Edition, 1 September 1991 and its supplements. The report is available from the NIMA Combat Support Center and its stock number is DMATR83502WGS84. Non-DoD requesters may obtain the report as a public sale item from the U.S. Geological Survey, Box 25286, Denver Federal Center, Denver, Colorado 80225 or by phone at 1-800-USA-MAPS.


Return to Professor Dutch's Home Page
Created 18 April 2005, Last Update 14 December 2009
Not an official UW Green Bay site