Convert Utm Coordinates To Decimal Degrees In Excel

Steven Dutch, Professor Emeritus, Natural and Applied Sciences, Universityof Wisconsin - Green Bay

• Convert Coordinate Degrees To Decimal
• Excel Convert Coordinates To Decimal
• Convert Utm Coordinates To Decimal Degrees In Excel Convert

Information on the UTM system
New! Javascript-Convert Between Geographic and UTM Coordinates
Spreadsheet For UTM Conversion
Help! My Data Doesn't Look Like A UTM Grid!

HI I have an excel spreadsheet that shows the coordinates in lat long decimal degrees. I need to show the coordinates as utm x y. To be more specific I need to have one column that shows the UTM on showing the x and one showing the y. Is there an easier way than manually converting each decimal degree to UTM and and entering them manually. To convert into various coordinate formats, we freely offer our Excel template. Download the free Excel template to perform the following operations: – Conversion of UTM Coordinates to Decimal Degrees. – Conversion of Coordinates in Decimal Degrees to UTM. – Conversion of Coordinates in Degrees, Minutes and Seconds to Decimal Degrees.

I get enough inquiries on this subject that I decided to create a page forit.

1. Converting coordinate DD to DMS in Excel (increase the decimal number for the seconds) Please community and friends. When I use the function to convert degree in DMS in the excel, as the follow below, in the visual basic, someone knows how can I increase the number of decimal places for the seconds?
2. In the previous post we had shown an Excel sheet to convert coordinates Geographic to UTM From a sheet that had popularized Gabriel Ortiz. Let's see now this tool that does the same process in reverse, that is, having coordinates in UTM format (Universal Traverso de Mercator) and knowing the area, transform them to latitudes and geographical longitudes.

Caution! Unlike latitude and longitude, there is no physical frame ofreference for UTM grids. Latitude is determined by the earth's polar axis.Longitude is determined by the earth's rotation. If you can see the stars andhave a sextant and a good clock set to Greenwich time, you can find yourlatitude and longitude. But there is no way to determine your UTM coordinatesexcept by calculation.

UTM grids, on the other hand, are created by laying a square grid on theearth. This means that different maps will have different grids depending on thedatum used (model of the shape of the earth). I saw US military maps of Germanyshift their UTM grids by about 300 meters when a more modern datum was used forthe maps. Also, old World War II era maps of Europe apparently used a single gridfor all of Europe and grids in some areas are wildly tilted with respect tolatitude and longitude.

Previously, the two basic references for converting UTM and geographic coordinates wereU.S. GeologicalSurvey Professional Paper 1395 and U. S. ArmyTechnical Manual TM 5-241-8 (complete citations below). Each has advantages anddisadvantages. These have been entirely superseded by Charles F. F. Karney (2010)Transverse Mercator with an accuracy of a few nanometers. (arXiv:1002.1417)

Converting Between Decimal Degrees, Degrees, Minutes and Seconds, andRadians

(dd + mm/60 +ss/3600) to Decimal degrees (dd.ff)

dd = whole degrees, mm = minutes, ss = seconds

dd.ff = dd + mm/60 + ss/3600

Example: 30 degrees 15 minutes 22 seconds = 30 + 15/60 + 22/3600 = 30.2561

Decimal degrees (dd.ff) to (dd + mm/60 +ss/3600)

For the reverse conversion, we want to convert dd.ff to dd mm ss. Here ff =the fractional part of a decimal degree.

mm = 60*ff

ss = 60*(fractional part of mm)

Use only the whole number part of mm in the final result.

HI I have an excel spreadsheet that shows the coordinates in lat long decimal degrees. I need to show the coordinates as utm x y. To be more specific I need to have one column that shows the UTM on showing the x and one showing the y. Is there an easier way than manually converting each decimal degree to UTM and and entering them manually. To convert into various coordinate formats, we freely offer our Excel template. Download the free Excel template to perform the following operations: – Conversion of UTM Coordinates to Decimal Degrees. – Conversion of Coordinates in Decimal Degrees to UTM. – Conversion of Coordinates in Degrees, Minutes and Seconds to Decimal Degrees.

I get enough inquiries on this subject that I decided to create a page forit.

1. Converting coordinate DD to DMS in Excel (increase the decimal number for the seconds) Please community and friends. When I use the function to convert degree in DMS in the excel, as the follow below, in the visual basic, someone knows how can I increase the number of decimal places for the seconds?
2. In the previous post we had shown an Excel sheet to convert coordinates Geographic to UTM From a sheet that had popularized Gabriel Ortiz. Let's see now this tool that does the same process in reverse, that is, having coordinates in UTM format (Universal Traverso de Mercator) and knowing the area, transform them to latitudes and geographical longitudes.

Caution! Unlike latitude and longitude, there is no physical frame ofreference for UTM grids. Latitude is determined by the earth's polar axis.Longitude is determined by the earth's rotation. If you can see the stars andhave a sextant and a good clock set to Greenwich time, you can find yourlatitude and longitude. But there is no way to determine your UTM coordinatesexcept by calculation.

UTM grids, on the other hand, are created by laying a square grid on theearth. This means that different maps will have different grids depending on thedatum used (model of the shape of the earth). I saw US military maps of Germanyshift their UTM grids by about 300 meters when a more modern datum was used forthe maps. Also, old World War II era maps of Europe apparently used a single gridfor all of Europe and grids in some areas are wildly tilted with respect tolatitude and longitude.

Previously, the two basic references for converting UTM and geographic coordinates wereU.S. GeologicalSurvey Professional Paper 1395 and U. S. ArmyTechnical Manual TM 5-241-8 (complete citations below). Each has advantages anddisadvantages. These have been entirely superseded by Charles F. F. Karney (2010)Transverse Mercator with an accuracy of a few nanometers. (arXiv:1002.1417)

Converting Between Decimal Degrees, Degrees, Minutes and Seconds, andRadians

(dd + mm/60 +ss/3600) to Decimal degrees (dd.ff)

dd = whole degrees, mm = minutes, ss = seconds

dd.ff = dd + mm/60 + ss/3600

Example: 30 degrees 15 minutes 22 seconds = 30 + 15/60 + 22/3600 = 30.2561

Decimal degrees (dd.ff) to (dd + mm/60 +ss/3600)

For the reverse conversion, we want to convert dd.ff to dd mm ss. Here ff =the fractional part of a decimal degree.

mm = 60*ff

ss = 60*(fractional part of mm)

Use only the whole number part of mm in the final result.

30.2561 degrees = 30 degrees

.2561*60 = 15.366 minutes

.366 minutes = 22 seconds, so the final result is 30 degrees 15 minutes 22seconds

Decimal degrees (dd.ff) to Radians

Radians = (dd.ff)*pi/180

Radians to Decimal degrees (dd.ff)

(dd.ff) = Radians*180/pi

Degrees, Minutes and Seconds to Distance

A degree of longitude at the equator is 111.2 kilometers. A minute is 1853meters. A second is 30.9 meters. For other latitudes multiply by cos(lat).Distances for degrees, minutes and secondsin latitude are very similarand differ very slightly with latitude. (Before satellites, observing thosedifferences was a principal method for determining the exact shape of theearth.)

Converting Latitude and Longitude to UTM

Okay, take a deep breath. This will get very complicated, but themath, although tedious, is only algebra and trigonometry. It would sure benice if someone wrote a spreadsheet to do this. Or better yet, a Javascript page.

Another thing you need to know is the datum being used:

Don't interpret the chart to mean there is radical disagreement about theshape of the earth. The earth isn't perfectly round, it isn't even a perfect ellipsoid, and slightly differentshapes work better for some regions than for the earth as a whole. The top threeare based on worldwide data and are truly global. Also, you are very unlikely tofind UTM grids based on any of the earlier projections.

The most modern datums (jars my Latinist sensibilities since the plural of datumin Latin is data, but that has a different meaning to us) are NAD83 andWGS84. These are based in turn on GRS80. Differences between the three systemsderive mostly from redetermination of station locations rather than differences in the datum. Unless you are locating a first-order station to sub-millimeter accuracy (in which case you are way beyond the scope of this page) you can probably regard them as essentially identical.

The NAD83 and WGS84 datums

NIMA Technical Report 8350.2 states:

The WGS 84 Ellipsoid is for all practical purposes identical to the GRS 80 ellipsoid. They use the same value for the semi-major axis and have the same orientation with respect to the center of mass and the coordinate system origin. .. The difference between the GRS 80 and WGS 84 values for f creates a difference of 0.1 mm in the derived semi-minor [polar] axes of the two ellipsoids.

Based on these definitions, geodetic positions determined with respect to NAD 83 or WGS 84 have uncertainties of about one meter in each component. For mapping, charting and navigation, the two systems are indistinguishable at scales of 1:5,000 or smaller and with accuracies of about 2 m.

Formulas For Converting Latitude and Longitude to UTM

Earlier versions of this page were written based on Army and USGS references. It has been updated according to the formulas of Karney (2010), which are accurate to within nanometers. The Army and USGS references are now obsolete. Refer to the Appendix for the Army and USGS formulas.

First step, project the ellipsoid onto a sphere and compute the conformal latitude (lat'). In the formulas below, sinh and tanh are hyperbolic functions, which behave much like regular trigonometric functions. They are pretty docile and do not bite if not provoked. 'Argsinh,' etc. are analogous to arcsin, etc and are inverse hyperbolic functions. The hyperbolic and inverse hyperbolic functions are standard in pretty much all programming languages. The quantity e = SQRT(f(2-f), where f is the flattening of the ellipsoid.

argsinh tan lat′ = argsinh tan lat − e argtanh(e sin lat).

We can solve for tan lat': tan lat' = sinh(argsinh tan lat − e argtanh(e sin lat))

It's convenient to work with tangents, so define tau = tan lat and tau' = tan lat'

Define sigma = sinh(e argtanh(e tau/SQRT(1 + tau^2)))

tau' = tau SQRT(1 + sigma^2) - sigma SQRT(1 + tau^2)

Now we can define xi' = arctan(tau'/cos long) and eta' = argsinh(sin (long/SQRT(tau'^2 + (cos long)^2)))

Somewhat confusingly, xi refers to the north south direction

The next step requires some series computations. We need to define:

A = a/( 1 + n ) (1 + (¼) n^2 + (1/64)n^4 + (1/256)n^6 + (25/16384)n^8 + (49/65536)n^10 + ..), defines the scale of the ellipse. 2piA= circumference of meridian.

α1 = (1/2)n − (2/3)n^2 + (5/16)n^3 + (41/180)n^4 - (127/288)n^5 + (7891/37800)n^6 + (72161/387072)n^7 - (18975107/50803200)n^8 + (60193001/290304000)n^9 + (134592031/1026432000)n^10 + ..

α2 = (13/48)n^2 - (3/5)n^3 + (557/1440)n^4 + (281/630)n^5 - (1983433/1935360)n^6 + (13769/28800)n^7 + (148003883/174182400)n^8 - (705286231/465696000)n^9 + (1703267974087/3218890752000)n^10 + ..

α3 = (61/240)n^3 - (103/140)n^4 + (15061/26880)n^5 + (167603/181440)n^6 - (67102379/29030400)n^7 + (79682431/79833600)n^8 + (6304945039/2128896000)n^9 - (6601904925257/1307674368000)n^10 + ..

505 games terraria 1.3 android. α4 = (49561/161280)n^4 - (179/168)n^5 + (6601661/7257600)n^6 + (97445/49896)n^7 - (40176129013/7664025600)n^8 + (138471097/66528000)n^9 + (48087451385201/5230697472000)n^10 + ..

α5 = (34729/80640)n^5 - (3418889/1995840)n^6 + (14644087/9123840)n^7 + (2605413599/622702080)n^8 - (31015475399/2583060480)n^9 + (5820486440369/1307674368000)n^10 + ..

α6 = (212378941/319334400)n^6 - (30705481/10378368)n^7 + (175214326799/58118860800)n^8 + (870492877/96096000)n^9 - (1328004581729009/47823519744000)n^10 + ..

α7 = (1522256789/1383782400)n^7 - (16759934899/3113510400)n^8 + (1315149374443/221405184000)n^9 + (71809987837451/3629463552000)n^10 + ..

α8 = (1424729850961/743921418240)n^8 - (256783708069/25204608000)n^9 + (2468749292989891/203249958912000)n^10 + ..

α9 = (21091646195357/6080126976000)n^9 - (67196182138355857/3379030566912000)n^10 + ..

α10 = (77911515623232821/12014330904576000)n^10 + ..(24)/a)n^d/

Now we calculate:

xi = xi' + SUM(αj sin 2j xi' cosh 2j eta') and eta = eta' + SUM(αj cos 2j xi' sinh 2j eta'), or: Poly universe download free.

xi = xi' + α1 sin 2xi' cosh 2eta' + α2 sin 4xi' cosh 4eta' + α3 sin 6xi' cosh 6eta' + α4 sin8xi' cosh 8eta' etc.

xi = xi' + α1 sin 2xi' cosh 2eta' + α2 sin 4xi' cosh 4eta' + α3 sin 6xi' cosh 6eta' + α4 sin8xi' cosh 8eta' etc.

finally scale xi and eta to obtain easting = k0Aeta and northing = k0Axi.

Since these formulas are derived from elliptic functions, they are both more rigorous and easier to troubleshoot than the Army and USGS references.

Converting UTM to Latitude and Longitude

Convert Coordinate Degrees To Decimal

Earlier versions of this page were written based on Army and USGS references. It has been updated according to the formulas of Karney (2010), which are accurate to within nanometers. The Army and USGS references are obsolete. Refer to the Appendix for the Army and USGS formulas.

The process involves inverting the procedure above.

xi' = xi + SUM(βj sin 2j xi cosh 2j eta) and eta' = eta + SUM(βj cos 2j xi sinh 2j eta).

Since the sums involve xi and eta instead of x' and eta', the coefficients are not the same:

β1 = (1/2)n - (2/3)n^2 + (37/96)n^3 - (1/360)n^4 - (81/512)n^5 + (96199/604800)n^6 - (5406467/38707200)n^7 + (7944359/67737600)n^8 - (7378753979/97542144000)n^9 + (25123531261/804722688000)n^10 + ..

β2 = (1/48)n^2 + (1/15)n^3 - (437/1440)n^4 + (46/105)n^5 - (1118711/3870720)n^6 + (51841/1209600)n^7 + (24749483/348364800)n^8 - (115295683/1397088000)n^9 + (5487737251099/51502252032000)n^10 + ..

β3 = (17/480)n^3 - (37/840)n^4 - (209/4480)n^5 + (5569/90720)n^6 + (9261899/58060800)n^7 - (6457463/17740800)n^8 + (2473691167/9289728000)n^9 - (852549456029/20922789888000)n^10 + ..

β4 = (4397/161280)n^4 - (11/504)n^5 - (830251/7257600)n^6 + (466511/2494800)n^7 + (324154477/7664025600)n^8 - (937932223/3891888000)n^9 - (89112264211/5230697472000)n^10 + ..

β5 = (4583/161280)n^5 - (108847/3991680)n^6 - (8005831/63866880)n^7 + (22894433/124540416)n^8 + (112731569449/557941063680)n^9 - (5391039814733/10461394944000)n^10 + ..

β6 = (20648693/638668800)n^6 - (16363163/518918400)n^7 - (2204645983/12915302400)n^8 + (4543317553/18162144000)n^9 + (54894890298749/167382319104000)n^10 + ..

β7 = (219941297/5535129600)n^7 - (497323811/12454041600)n^8 - (79431132943/332107776000)n^9 + (4346429528407/12703122432000)n^10 + ..

β8 = (191773887257/3719607091200)n^8 - (17822319343/336825216000)n^9 - (497155444501631/1422749712384000)n^10 + ..

β9 = (11025641854267/158083301376000)n^9 - (492293158444691/6758061133824000)n^10 + ..

β10 = (7028504530429621/72085985427456000)n^10 + ..

We can then obtain tau' = sin xi'/SQRT(sinh(eta')^2 + cos(xi')^2) and long = arctan(sinh eta'/cos xi')

We can invert the forward formulas to obtain: tau SQRT(1 + sigma^2) - sigma SQRT(1 + tau^2) - tau' = 0.

There are two problems: tau is the unknown and sigma is a function of tau. So we have to resort to approximation methods. Newton's Method works nicely.

f = tau SQRT(1 + sigma^2) - sigma SQRT(1 + tau^2) - tau' = 0.

Start with tau' as the initial guess for tau. Compute sigma = sinh(e argtanh(e tau/SQRT(1 + tau^2)))

Now we need the defivative of f.
df/dtau =(SQRT((1 + sigma^2)(1 + tau^2)) - sigma tau)(1 - e^2)SQRT(1+tau^2)/(1 + (1 - e^2)tau^2)

Estimate f using tau' as the initial guess, calculate sigma and df/dtau. Estimate a new value for tau
tau = tau - (df/dtau)/tau, compute a new value for f and keep going until f converges to zero and tau stops changing. Three iterations were sufficient for me.

Just to check your formulas, Karney generated a huge number of test values. Here are the first ten

Note that all longitudes and eastings are with respect to a central meridian of zero degrees and zero easting. Yes, these formulas are accurate to more than 75 degrees from the central meridian. However, beyond 80 degrees, and at low latitudes, they can blow up in Excel.

In response to innumerable e-mails, you cannot use UTM grid coordinates without knowing your zone. There are sixty points on the earth's surface that have the same numerical UTM coordinates, 120 if you consider that northing is duplicated in both hemispheres.

A Spreadsheet Program

This program was recently updated using the formulas of Charles Karney (2010). His paper claims accuracy of a few nanometers. Excel has 15-digit accuracy, so the spreadsheet doesn't achieve that level, but is accurate to about 0.01mm. Most importantly, Karney's paper provides links to a site that has a huge set of test data. The spreadsheet has been tested against the data in both directions. Before linking to the program, note (especially the last item):

Excel Convert Coordinates To Decimal

• It is an Excel spreadsheet. If you have Excel on your computer, it will (or should) open when you click the link. Most major spreadsheet programs can read spreadsheets in other formats.
• A spreadsheet is not an applet or program. In particular, you can't manually enter data into a cell and preserve any formulas that are there. That's why some aspects of data entry are clunkier than they might otherwise be with, say, a Visual Basic program.
• There are three computation pages, one for single conversions, the other two for batch conversions. Other pages contain information on datums, test data and the specific conversion formulas. To use the batch conversions you need to be somewhat proficient in spreadsheets as you will have to copy data and cell formulas.
• For our mutual peace of mind, run a virus check. The spreadsheet has a couple of simple macros.
• You may copy the program for your own non-commercial use and for non-commercial distribution to others, but not for commercial use. Please give appropriate credit when citing your calculations. You may also modify it as needed for your personal use. In Internet Explorer, right click on the link and select Save Target As.. to save the spreadsheet to your own disk.
• There is now a Javascript Page which I hope is free of bugs. Since it's a program, it should handle complex what-ifs a lot better than a spreadsheet can. All the permissions for the spreadsheet apply here, too. Persistent bugs caused me to delete links to the older spreadsheet.
• These pages are somewhat OBE (overtaken by events). http://www.earthpoint.us/convert.aspx can convert between a variety of coordinate systems and has many links to other useful pages. The National Geodetic Survey has a site at http://www.ngs.noaa.gov/TOOLS/spc.shtml with that converts latitude and longitude to State Plane Coordinates.

References

Yay! Most of these are on-line now.

Snyder, J. P., 1987; Map Projections - A Working Manual. U.S. GeologicalSurvey Professional Paper 1395, 383 p. If you are at all serious about mapsyou need this book. On-line athttp://pubs.er.usgs.gov/publication/pp1395

Army, Department of, 1973; Universal Transverse Mercator Grid, U. S. ArmyTechnical Manual TM 5-241-8, 64 p. Superseded by DMATM 8358.2 The Universal Grids: Universal Transverse Mercator (UTM) and Universal Polar Stereographic (UPS).
On-line athttp://earth-info.nga.mil/gandG/publications/tm8358.2/TM8358_2.pdf

NIMA Technical Report 8350.2, 'Department of Defense World GeodeticSystem 1984, Its Definition and Relationships with Local Geodetic Systems,'Second Edition, 1 September 1991 and its supplements. The report is availablefrom 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 orby phone at 1-800-USA-MAPS. On-line athttp://earth-info.nga.mil/gandG/publications/tr8350.2/wgs84fin.pdf

Karney, Charles F.F., 2010, Transverse Mercator with an accuracy of a few nanometers, http://arxiv.org/abs/1002.1417v3. Provides accuracy to within nanometers (a few atoms). Supersedes Snyder and Army, above. His test data and numerous other programs are at http://geographiclib.sourceforge.net/.

Convert Utm Coordinates To Decimal Degrees In Excel Convert

Return to Professor Dutch's Home Page
Created 12 September 2003, Last Update 15 January 2020