# How to Convert Easting Northing to Lat Long in Excel

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

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

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.

Get FREE Advanced Excel Exercises with Solutions!
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

1. hi
i try this file in riyadh , saudi arabia but it not work correctly
can you help me i need it
thanks
my e-mail : [email protected]

Lutfor Rahman Shimanto May 20, 2024 at 1:02 PM

Hello Mohammed

Thanks for sharing your problem! However, we can not accurately get the corresponding latitude and longitude values using only the UTM Easting and UTM Northing. We also need UTM Zone, Easting, and Northing values to get Lat and Long values.

For your address, the UTM Zone would be 39Z. Let’s say your UTM Easting, UTM Northing, and UTM Zone are 585302.3, 27464684.41, and 39Z. Using this UTM information, you can get the corresponding Latitude and Longitude. To achieve your goal, I have developed some Excel VBA User-defined functions.

1. Press Alt+F11 to open the VBA Editor.
2. Click on Insert followed by Module.
3. 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``````

5. Apply the following formula: `=UTMToLatLong(B6, C6, D6)`
6. Hit Enter.

Hopefully, these user-defined functions will help. I have attached the solution workbook as well.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

• Hello Mohammed,

Kindly share the issues you are facing while using the Excel file in the comment box. In case you need to share images, Excel file, you also can post your problem in ExcelDemy Forum.

Regards
ExcelDemy

2. Hello,

If I’m in zone 903 (Florida state plane west, feet) in NAD83, what do I need to change in the formula?

Thank you,
David

Lutfor Rahman Shimanto May 20, 2024 at 12:46 PM

Hello David

Thanks for visiting our blog! For your address, the UTM Zone would be 32U. Let’s say your UTM Easting, UTM Northing, and UTM Zone are 691609.5, 5334764.67, and 32U. Using this UTM information, you can get the corresponding Latitude and Longitude. To achieve your goal, I have developed some Excel VBA User-defined functions.

1. Press Alt+F11 to open the VBA Editor.
2. Click on Insert followed by Module.
3. 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``````

5. Apply the following formula: `=UTMToLatLong(B6, C6, D6)`
6. Hit Enter.

Hopefully, these user-defined functions will help you reach your goal. I have attached the solution workbook as well. Good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

3. Hello, May i ask how to display second result (longitude). I’m not sure if I’m doing it wrong because when I hit enter it doesn’t display.

Thank you
Enrique.

• Hello Enrique Rafhael

Thanks for visiting our blog and sharing your problem. The existing formula returns an array containing both latitude and longitude. If you are not using Microsoft 365, you need to press Ctrl+Shift+Enter instead of pressing Enter.

However, If you want to avoid returning an array and want to get latitude and longitude individually, follow these steps:

1. Press Alt+F11 to open VBA Editor.
2. Click on Insert, followed by Module.
3. 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 UTMToLatitude(Easting As Double, Northing As Double, Zone As String) As Double

Dim zoneNumber As Integer
Dim zoneLetter As String
Dim latitude As Double

zoneNumber = Val(Left(Zone, Len(Zone) - 1))
zoneLetter = Right(Zone, 1)

Call ConvertUTMToLat(Easting, Northing, zoneNumber, zoneLetter, latitude)

UTMToLatitude = latitude

End Function

Function UTMToLongitude(Easting As Double, Northing As Double, Zone As String) As Double

Dim zoneNumber As Integer
Dim zoneLetter As String
Dim longitude As Double

zoneNumber = Val(Left(Zone, Len(Zone) - 1))
zoneLetter = Right(Zone, 1)

Call ConvertUTMToLon(Easting, Northing, zoneNumber, zoneLetter, longitude)

UTMToLongitude = longitude

End Function

Sub ConvertUTMToLat(Easting As Double, Northing As Double, zoneNumber As Integer, zoneLetter As String, ByRef latitude 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()

End Sub

Sub ConvertUTMToLon(Easting As Double, Northing As Double, zoneNumber As Integer, zoneLetter As String, 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)

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``````

5. Apply the following formula and hit Enter: `=UTMToLatitude(\$B6, \$C6, \$D6)`
6. Now, drag the Fill Handle icon to cell E16.
7. Again, choose cell F6 and apply the formula: `=UTMToLongitude(\$B6, \$C6, \$D6)`
8. Drag the Fill Handle icon to copy the formula down.

Hopefully, following this procedure, you can avoid returning an array. Good luck.

Regards
ExcelDemy

Advanced Excel Exercises with Solutions PDF