How to Convert GPS Coordinates in Excel

You can convert GPS coordinates in Excel by splitting decimal degrees into three parts: whole-number degrees, whole-number minutes, and leftover seconds. Excel does not have a dedicated latitude and longitude conversion function, but standard formulas with INT, subtraction, and ROUND handle the job reliably.

Quick Answer: Convert Decimal GPS Coordinates to DMS in Excel

If your decimal coordinate is in A2, use these formulas:

• Degrees: =INT(A2)
• Minutes: =INT((A2-INT(A2))*60)
• Seconds: =ROUND(((A2-INT(A2))*60-INT((A2-INT(A2))*60))*60,2)

To combine everything into one readable result:

=INT(A2) & "° " & INT((A2-INT(A2))*60) & "' " & ROUND(((A2-INT(A2))*60-INT((A2-INT(A2))*60))*60,2) & "''"

This works well when you need to turn decimal latitude or longitude values into degrees, minutes, and seconds (DMS) for mapping, data cleanup, or exports.

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:

1. Prepare Your Data: Input your decimal degree coordinates into an Excel spreadsheet.

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)

So in your Excel Spreadsheet enter:

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''.

Conclusion

Converting GPS coordinates in Excel may seem daunting, but it’s quite manageable with the right formulas. Whether you're a geocaching enthusiast, a traveler, or someone who loves mapping, these Excel tricks will enhance your navigational toolkit.

Related Articles

• Excel Fractional Part: Extracting Integers and Fractions in Excel
• How to Convert Decimal Hours to Time in Excel
• TRUNC Function in Excel: A Comprehensive Guide
• How to Round Numbers in Excel: What You Need to Know
• The MATCH Function in Excel
• Converting Pace in Minutes per Mile to MPH and Back Again in Excel