How to Convert GPS Coordinates in Excel

Navigating the world of GPS coordinates can be a complex task, especially when dealing with different formats. Excel, with its powerful data manipulation capabilities, can be an excellent tool for converting latitude and longitude coordinates from decimal to degrees, minutes, and seconds (DMS). In this article, we’ll explore how to make conversions of GPS coordinates in Excel, ensuring you have the precise data needed for your GPS adventures.

Understanding Coordinate Formats

Before diving into conversions, it’s essential to understand the two primary formats for GPS coordinates:

• Decimal Degrees (DD): A simpler format where coordinates are expressed in decimal fractions.
• Degrees, Minutes, and Seconds (DMS): A more complex format, often used in mapping and GPS technologies.

Converting Decimal Degrees to DMS in Excel

To convert coordinates from decimal degrees to DMS, follow these steps:

2. Splitting the Degree: The degree value in DMS is the same as the integer part of the decimal degree value. Use the INT function to extract this.
`=INT(A2)`
This formula will give you the degree component of the decimal degree value in cell A2.
3. Calculating the Minutes: Minutes are derived from the remaining decimal part. Multiply the decimal fraction by 60.
`=INT((A2-INT(A2))*60)`
This gives the minutes by converting the remaining decimal degree value to minutes.
4. Determining the Seconds: To get the seconds, take the remaining fraction from the minutes calculation and multiply by 60.
`=((A2-INT(A2))*60-INT((A2-INT(A2))*60))*60`
This formula provides the seconds component.
5. Concatenating the Results: Use the CONCATENATE function or the ‘&’ operator to combine degrees, minutes, and seconds into a single string.
`=INT(A2) & "° " & INT((A2-INT(A2))*60) & "' " & ROUND(((A2-INT(A2))*60-INT((A2-INT(A2))*60))*60, 2) & "''"`

Using Functions for Conversion of Coordinates in Excel

Excel lacks a direct function for this conversion, but creative use of basic functions like INT, ROUND, and CONCATENATE can make the process efficient.

Let’s use the geographical coordinates of the Eiffel Tower, which according to Wikipedia  are:

• Decimal Degrees: Latitude 48.858222°N and Longitude 2.294500°E
• Degrees, Minutes, and Seconds: Latitude 48°51′29.6″N  and Longitude 2°17′40.2″ E

Google Earth shows the GPS coordinates to be 48 degrees 51 minutes 29 seconds North and 2 degrees 17 minutes 40 seconds East. (Coordinate formatting shown as 48° 51′ 30″ N 2° 17′ 40″ E in Google Earth while right-clicking on the map and selecting Get Info)

1. Input Data:
• Latitude: Enter `48.8584` in cell A2.
• Longitude: Enter `2.2945` in cell B2.
2. Calculate Degrees:
• For Latitude: `=INT(A2)` results in `48` degrees.
• For Longitude: `=INT(B2)` results in `2` degrees.
3. Calculate Minutes:
• Latitude: `=INT((A2-INT(A2))*60)` gives `51` minutes.
• Longitude: `=INT((B2-INT(B2))*60)` gives `17` minutes.
4. Calculate Seconds:
• Latitude:
`=((A2-INT(A2))*60-INT((A2-INT(A2))*60))*60`
results in `30.24` seconds.
• Longitude:
`=((B2-INT(B2))*60-INT((B2-INT(B2))*60))*60`
results in `27` seconds.
5. Combine Degrees, Minutes, and Seconds:
• Latitude:
`=INT(A2) & "° " & INT((A2-INT(A2))*60) & "' " & ROUND(((A2-INT(A2))*60-INT((A2-INT(A2))*60))*60, 2) & "''"`
• This results in `48° 51' 30.24''`.
• Longitude:
`=INT(B2) & "° " & INT((B2-INT(B2))*60) & "' " & ROUND(((B2-INT(B2))*60-INT((B2-INT(B2))*60))*60, 2) & "''"`
• This results in `2° 17' 27''`.