How to Convert Easting Northing to Lat Long in Excel

Here’s an overview of converting coordinates into latitude and longitude.

Download the Practice Workbook

Types of Coordinates (Easting Northing and Latitude-Longitude)?

Easting and Northing: The Easting coordinate indicates the distance measured eastward from a reference point, whereas the Northing coordinate represents the distance measured northward from the same reference point. These coordinates are often given in meters or feet, depending on the coordinate system used.

Latitude and Longitude: Latitude is the angular distance of a location north or south of the Earth’s equator, whereas Longitude is the angular distance east or west of the Prime Meridian. Longitude and latitude are both measured in degrees, minutes, and seconds.

Convert Easting-Northing to Lat-Long in Excel: 3 Easy Steps

Step 1 – Preparing the Dataset of UTM Easting, Northing, and Zone

• Choose column B starting from B6 for UTM Easting.
• For UTM Northing, we chose column C starting from C6.
• Use column D for the UTM Zone.
• Insert all the corresponding (Easting, Northing, and Zone) entries like the following image.
  

Step 2 – Developing the VBA User-defined Functions

• Go to the Developer tab and click on Visual Basic.

• The VBA Editor window opens
• In the VBA Editor window, click on Insert and select Module: 

• Paste the following code in the module and save it:

Option Explicit

Private Const WGS84_A As Double = 6378137#
Private Const WGS84_E As Double = 0.081819190842622

Function UTMToLatLong(Easting As Double, Northing As Double, Zone As String) As Variant
    
    Dim zoneNumber As Integer
    Dim zoneLetter As String
    Dim latitude As Double
    Dim longitude As Double
    Dim result(1 To 2) As Double
    
    zoneNumber = Val(Left(Zone, Len(Zone) - 1))
    zoneLetter = Right(Zone, 1)
    
    Call ConvertUTMToLatLon(Easting, Northing, zoneNumber, zoneLetter, latitude, longitude)
    
    result(1) = latitude
    result(2) = longitude
    
    UTMToLatLong = result

End Function

Sub ConvertUTMToLatLon(Easting As Double, Northing As Double, zoneNumber As Integer, zoneLetter As String, ByRef latitude As Double, ByRef longitude As Double)
    
    Dim k0 As Double
    k0 = 0.9996
    
    Dim E As Double, N As Double
    Dim A As Double, eccSquared As Double, eccPrimeSquared As Double
    Dim M As Double, mu As Double
    Dim e1 As Double, J1 As Double, J2 As Double, J3 As Double, J4 As Double, J5 As Double
    Dim FPhi1 As Double, C1 As Double, T1 As Double, R1 As Double, N1 As Double, D As Double
    
    E = Easting - 500000#
    If UCase(zoneLetter) < "N" Then
        N = Northing - 10000000#
    Else
        N = Northing
    End If
    
    A = WGS84_A
    eccSquared = WGS84_E ^ 2
    eccPrimeSquared = eccSquared / (1 - eccSquared)
    
    M = N / k0
    mu = M / (A * (1 - eccSquared / 4 - 3 * eccSquared ^ 2 / 64 - 5 * eccSquared ^ 3 / 256))
    
    e1 = (1 - Sqr(1 - eccSquared)) / (1 + Sqr(1 - eccSquared))
    
    J1 = 3 * e1 / 2 - 27 * e1 ^ 3 / 32
    J2 = 21 * e1 ^ 2 / 16 - 55 * e1 ^ 4 / 32
    J3 = 151 * e1 ^ 3 / 96
    J4 = 1097 * e1 ^ 4 / 512
    
    FPhi1 = mu + J1 * Sin(2 * mu) + J2 * Sin(4 * mu) + J3 * Sin(6 * mu) + J4 * Sin(8 * mu)
    
    C1 = eccPrimeSquared * Cos(FPhi1) ^ 2
    T1 = Tan(FPhi1) ^ 2
    R1 = A * (1 - eccSquared) / (1 - eccSquared * Sin(FPhi1) ^ 2) ^ 1.5
    N1 = A / Sqr(1 - eccSquared * Sin(FPhi1) ^ 2)
    D = E / (N1 * k0)
    
    latitude = FPhi1 - (N1 * Tan(FPhi1) / R1) * (D ^ 2 / 2 - (5 + 3 * T1 + 10 * C1 - 4 * C1 ^ 2 - 9 * eccPrimeSquared) * D ^ 4 / 24 + (61 + 90 * T1 + 298 * C1 + 45 * T1 ^ 2 - 252 * eccPrimeSquared - 3 * C1 ^ 2) * D ^ 6 / 720)
    latitude = latitude * 180 / Application.WorksheetFunction.Pi()
    
    longitude = (D - (1 + 2 * T1 + C1) * D ^ 3 / 6 + (5 - 2 * C1 + 28 * T1 - 3 * C1 ^ 2 + 8 * eccPrimeSquared + 24 * T1 ^ 2) * D ^ 5 / 120) / Cos(FPhi1)
    longitude = zoneNumber * 6 - 183 + longitude * 180 / Application.WorksheetFunction.Pi()

End Sub

Step 3 – Converting Easting, Northing, and Zone Into Lat-Long

• Select cell E6.
• Insert the following formula:

=UTMToLatLong($B6, $C6, $D6)

• Hit Enter to get Latitude in E6 and Longitude in F6.

• Drag the Fill Handle icon to copy the formula down to get the final result.

Latitude-Longitude in Degree, Minute and Second Format

• Select cell G6.
• Apply the following formula:

=CONCATENATE(TEXT(INT(E6),"0"),"° ", TEXT(INT(MOD(E6*60,60)),"00"),"' ", TEXT(MOD(E6*3600,60),"00.00"), """")

• Drag the Fill Handle icon to copy the formula down for the Latitude values.

• Select cell H6.
• Apply the following formula:

=CONCATENATE(TEXT(INT(F6),"0"),"° ", TEXT(INT(MOD(F6*60,60)),"00"),"' ", TEXT(MOD(F6*3600,60),"00.00"), """")

• Drag the Fill Handle icon to copy the formula down for the Longitude values.

Frequently Asked Questions

Are there any limitations or potential errors to be aware of during the conversion process?

During the conversion process, it’s important to consider potential limitations or errors. These may include using the correct formulas for the specific coordinate system and ellipsoid, ensuring the accuracy of the input data, and validating the results against reliable sources. It’s also crucial to double-check the units and adjust the formulas if necessary.

Are there any specific adjustments needed for different ellipsoids or units?

Yes, specific adjustments may be necessary for different ellipsoids or units. The formulas provided assume the use of the WGS84 ellipsoid and meters as the unit of measurement. If you are working with a different ellipsoid or using different units, you may need to modify the formulas to account for these variations.

Can I automate the conversion process in Excel using VBA?

Yes, you can automate the conversion process in Excel using VBA (Visual Basic for Applications). By writing VBA code, you can create a macro that performs the conversion automatically. The code can iterate through a range of cells, apply the conversion formulas, and populate the Latitude Longitude values.