Convert Utm Coordinates To Decimal Degrees In Excel

This Excel template is initially made to convert geographic coordinates in decimal to degrees, minutes and seconds. Just the opposite of the template that we had done before, as seen in this example: In addition: Concatenate them into a string; Turn them to UTM coordinates, with an option to choose a Datum. UTM: Coordinates: E N Datum: NAD27 NAD83 Zone: WV State Plane (feet) Coordinates: E N Datum: NAD27 NAD83 Zone: -street map image topo.

  • The additional column is the UTM coordinates so you can send them to AutoCAD as you would We explain in another article. To send from Excel UTM to Google Earth See this other article. Review it, and report any issues. The template to convert Geographical coordinates to UTM.
  • Decimal degrees = Degrees + Minutes/60 + Seconds/3600. Let's convert the latitude and longitude coordinates of the Empire State Building. The coordinates are 40° 44′ 54.3″ N, 73° 59′ 9″ W. So its latitude in decimal degrees is: DD = 40 + 44/60 + 54.3/3600 = 40.748417. And longitude: DD = 73 + 59/60 + 9/3600 = 73.985833.
-->

Note

Excel convert coordinates to decimal

Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more information about this change, read this blog post.

Summary

Angular measurements are commonly expressed in units of degrees, minutes, and seconds (DMS). 1 degree equals 60 minutes, and one minute equals 60 seconds. To simplify some mathematical calculations, you may want to express angular measurements in degrees and decimal fractions of degrees.

This article contains a sample custom function you can use to convert a degree value stored in decimal format, to DMS stored in text format, and a sample function that converts DMS to a degree value stored in decimal format.

More Information

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs.

If you have limited programming experience, you may want to contact a Microsoft Advisory Services. For more information, visit the Microsoft Web site:

Microsoft Advisory Services - https://support.microsoft.com/gp/advisoryservice

For more information about the support options that are available and about how to contact Microsoft, see https://support.microsoft.com.

Converting Decimal Degrees to Degrees/Minutes/Seconds

The following Microsoft Visual Basic for Applications custom function accepts an angle formatted as a decimal value and converts it to a text value displayed in degrees, minutes, and seconds.

To use this function, create a conversion formula, as in the following example:

  1. Start Excel and press ALT+F11 to start the Visual Basic editor.

  2. On the Insert menu, click Module.

  3. Enter the sample code for the Convert_Degree custom function described above into the module sheet.

  4. Press ALT+F11 to return to excel.

  5. In cell A1 type 10.46.

  6. In cell A2 type the formula: =Convert_Degree(A1)

    The formula returns 10°27'36'

Converting Degrees/Minutes/Seconds to Decimal Degrees

The following Microsoft Visual Basic for Applications custom function accepts a text string of degrees, minutes, and seconds formatted in the exact same format that the Convert_Degree function returns (for example, 10° 27' 36') and converts it to an angle formatted as a decimal value. This is exactly the reverse of the Convert_Degree custom function.

Decimal

Warning

This custom function fails if the Degree_Deg argument is not in the format <degrees>° <minutes>' <seconds>' even if the seconds value is 0.

To use this function, create a conversion formula, as in the following example:

Convert Utm Coordinates To Decimal Degrees In Excel

  1. Start Excel and press ALT+F11 to start the Visual Basic Editor.

  2. On the Insert menu, click Module.

  3. Enter the sample code for the Convert_Decimal custom function described above into the module sheet.

  4. Press ALT+F11 to return to excel.

  5. In cell A1 type the following formula:

    =Convert_Decimal('10° 27' 36'')

    Note

    You are required to type three quotation marks ('') at the end of the argument of this formula to balance the quotation mark for the seconds and the quotation mark for the text string. A cell reference will not require a quotation mark.

  6. The formula returns 10.46

Convert Utm Coordinates To Decimal Degrees In Excel Convert

If you have a long list of geographic coordinates to work with, a Microsoft Excel spreadsheet is sure to be useful. There are three basic Excel tools that can work for you, no matter how you want to manipulate your geographic coordinates. You’ll need to know how to calculate the distance between two latitude and longitude points, how to convert latitude and longitude data to decimal degrees, and finally, how to geocode latitudes and longitudes.

Decimal Degrees To Utm Conversion

How to Calculate the Distance Between Two Coordinates in Excel

In an example of how to calculate the distance between two coordinates in Excel, we’ll seek to measure the great circle distance. We’ll note that latitude and longitude are denoted in degrees, minutes and seconds.

Convert Latitude and Longitude to Decimal Degrees in Excel

Everything, including something as complicated as degrees, minutes, and seconds, can be converted into decimals. The degrees part remains the same, but minutes and seconds need to be converted into their percentage of a degree and combined. There are 60 minutes in a degree and 60 seconds in a minute (which means 3,600 seconds in a degree). Therefore, divide minutes by 60 and seconds by 3,600.

The overall formula:

Total Degrees (in the decimal form) = Deg + [Mins / 60] + [Seconds / 3600]

…So About That Distance

Now that you have the latitude and longitude values in decimals, you just face the equation for the nautical miles between Timbuktu and Casablanca, noted underneath.

Nautical Miles= ACOS [(sin(Lat_place_1*PI()/180)*sin(Lat_place_2*PI()/180)+

cos(Lat_place_1*PI()/180)*cos(Lat_place_2*PI()/180)*

cos(Lon_place_2*PI()/180-Lon_place_1*PI()/180))] *3443.8985

That three thousand number, in the end, is the radius of Earth, in, Nautical Miles. Even if you were to substitute it with the radius of a sphere, assuming the Earth is spherical, at 3437.7468 NM, you will not be near the real, accurate distance.

Geocode Latitude and Longitude in Excel

Geocoding is the conversion of street addresses to latitude and longitude data that can be mapped. Now, there are two ways to geocode latitude and longitude from an Excel spreadsheet: the hard way and the easy way.

We’ll cover the hard way first, which is what you’ll need if you require the coordinates to live in your Excel document. You’ll need to write the Visual Basic (VBA) script to make a call to an external geocoder, or find the code elsewhere, such as this GitHub repo. There are a number of geocoder APIs and some geocoders are faster than others.

The Google Maps Geocoding API is a common choice and this is the API we’ll call in the easy option. However, it also can work directly within Excel. Armed with the code you’ve written or discovered, here are the steps to deploy geocoding within Excel.

Step 1: Find the Developer tab in your version of Excel.

Step 2: Look for the “Visual Basic” tab. This opens the development window.

Step 3: Insert —> Module. The new module will hold your function.

Step 4:Here is where you copy and paste your code.

Step 5: We need to add references now. So, Tools —> References. Make sure to check the Microsoft XML v6.0. CLICK OK.

Step 6: Press CTRL / CMD + S. Save as type —> Excel Add-In. Now you get to name your function. The extension has to be .xlam. Time to close all Excel files.

Step 7: Open a new file. File —> Options —> Add-Inns. In the Manage box, choose Excel-Add Ins.

Step 8: Press Go. Choose your function in the window that appears by ticking in the box. Press OK.

Step 9: VOILA. Now you could just use the new function you created, like any other function.

After you are done retrieving the values, copy and paste them elsewhere, off of the sheet with the calculations. Then delete the function calls because otherwise when you reopen this file, the Google API will start to be pinged again. That will eat up several minutes before the file is open. To optimize Google resource usage, take a 10-second break between addresses.

Convert Utm Coordinates To Decimal Degrees In Excel Pdf

That’s a lot of steps, and assumes you’ve found some code to try out. Next, we’ll see the easier way to geocode your Excel data. It’s fast and reliable, but it won’t import the coordinates into your Excel file. On the other hand, the excel geocoding tool is copy-paste simple and gets you an interactive map.

Convert Coordinate Degrees To Decimal

Create a Map with Excel

There are a number of ways to create maps with Excel data. Perhaps the easiest is to just copy and paste your spreadsheet data into our map-making tool. Doing so turns your Excel document into a beautiful, interactive map like the one below.

View Example Latitude/Longitude Map in a full screen map

To make your own map based on latitude and longitude coordinates, separate the coordinates into their own columns within your spreadsheet.

Then, select and copy the rows and columns of your spreadsheet (Ctrl+C command or Cmd+C on Mac) and navigate to batchgeo.com. Paste (Ctrl+V or Cmd+V on Mac) in your data, and you’re done!

Convert Utm Coordinates To Decimal Degrees In Excel Spreadsheet

Whether you want to make a latitude and longitude map or create your own virtual wedding guide for your guests, BatchGeo provides a free and easy way to make beautiful web maps that are ready to be saved and shared.