# How to Convert Lat Long to UTM in Excel (with Easy Steps)

Excel by Microsoft is, without question, one of the most powerful computer programs available. We can perform an almost infinite computation on any dataset using the features and tools that Excel provides. In this article, we will explore a straightforward method anyone can use in Excel to convert latitude and longitude to UTM. You will be able to convert Lat and Long to UTM Easting, Northing, and Zone. Hence, you must follow these easy steps to Convert Lat Long to UTM in Excel.

Note
I have been writing this article using the Microsoft 365 version and a 64-bit Version of Windows.

## What Is UTM?

The Universal Transverse Mercator (UTM) is a way to project maps and give coordinates to places on Earth’s surface. Like the old way of using latitude and longitude, this is a horizontal way to show where you are. The UTM system divides the Earth into 60 zones, each covering 6Â° longitude. A grid zone comprises both a location and a band of latitude. Sometimes, only N or S is added after the zone number to show the North or South hemisphere.

## Convert Lat Long to UTM in Excel: Step-by-step Procedures

Here, we consider some latitude (Lat) and longitude (Long) values. We will find the UTM Easting, UTM Northing, and UTM Zone from these Lat & Long values. To achieve this, we must develop some Excel VBA user-defined functions.

### Step 1: Implement Excel VBA Functions to Find UTM

#### 1.1 User-defined Function for UTM Easting

We will create a function named CalculateUTMEasting. This function will take latitude and longitude values and return UTM Easting.

First, navigate to Developer >> click on Visual Basic.

Later, choose Insert >> click on Module >> paste the following code >> Save.

``````Function CalculateUTMEasting(latitude As Double, longitude As Double) As Double

Â Â Â  Dim utmZone As Integer
Â Â Â  utmZone = Int((longitude + 180) / 6) + 1

Â Â Â  Dim CentralMeridian As Double
Â Â Â  CentralMeridian = (utmZone - 1) * 6 - 180 + 3

Â Â Â  Dim ScaleFactor As Double
Â Â Â  ScaleFactor = 0.9996

Â Â Â  Dim EquatorialRadius As Double
Â Â Â  EquatorialRadius = 6378137#

Â Â Â  Dim EccentricitySquared As Double
Â Â Â  EccentricitySquared = 0.00669438

Â Â Â  Dim N As Double
Â Â Â  N = EquatorialRadius / Sqr(1 - EccentricitySquared * Sin(latitude * 3.14159265358979 / 180) ^ 2)

Â Â Â  Dim T As Double
Â Â Â  T = Tan(latitude * 3.14159265358979 / 180) ^ 2

Â Â Â  Dim C As Double
Â Â Â  C = EccentricitySquared * Cos(latitude * 3.14159265358979 / 180) ^ 2

Â Â Â  Dim A As Double
Â Â Â  A = Cos(latitude * 3.14159265358979 / 180) * (longitude - CentralMeridian) * 3.14159265358979 / 180

Â Â Â  Dim M As Double
Â Â Â  M = EquatorialRadius * ((1 - EccentricitySquared / 4 - 3 * EccentricitySquared ^ 2 / 64 - 5 * EccentricitySquared ^ 3 / 256) * latitude * 3.14159265358979 / 180 - (3 * EccentricitySquared / 8 + 3 * EccentricitySquared ^ 2 / 32 + 45 * EccentricitySquared ^ 3 / 1024) * Sin(2 * latitude * 3.14159265358979 / 180) + (15 * EccentricitySquared ^ 2 / 256 + 45 * EccentricitySquared ^ 3 / 1024) * Sin(4 * latitude * 3.14159265358979 / 180) - (35 * EccentricitySquared ^ 3 / 3072) * Sin(6 * latitude * 3.14159265358979 / 180))

Â Â Â  Dim UTMEasting As Double
Â Â Â  UTMEasting = ScaleFactor * N * (A + (1 - T + C) * A ^ 3 / 6 + (5 - 18 * T + T ^ 2 + 72 * C - 58 * EccentricitySquared) * A ^ 5 / 120) + 500000

Â Â Â  CalculateUTMEasting = Format(UTMEasting, "0.00")

End Function``````

#### 1.2 User-defined Function for UTM Northing

Here, we will develop another function to calculate UTM Northing. Likewise, the CalculateUTMNorthing function takes Lat and Long and returns UTM Northing.

Likewise, Open VBA Editor >> choose Insert >> click on Module >> paste the following code >> Save.

``````Function CalculateUTMNorthing(latitude As Double, longitude As Double) As Double
Â Â Â
Â Â Â  Dim utmZone As Integer
Â Â Â  utmZone = Int((longitude + 180) / 6) + 1
Â Â Â
Â Â Â  Dim hemisphere As Integer
Â Â Â  If latitude < 0 Then
Â Â Â Â Â Â Â  hemisphere = -1
Â Â Â  Else
Â Â Â Â Â Â Â  hemisphere = 1
Â Â Â  End If

Â Â Â  Dim CentralMeridian As Double
Â Â Â  CentralMeridian = (utmZone - 1) * 6 - 180 + 3

Â Â Â  Dim ScaleFactor As Double
Â Â Â  ScaleFactor = 0.9996

Â Â Â  Dim EquatorialRadius As Double
Â Â Â  EquatorialRadius = 6378137#

Â Â Â  Dim EccentricitySquared As Double
Â Â Â  EccentricitySquared = 0.00669438

Â Â Â  Dim N As Double
Â Â Â  N = EquatorialRadius / Sqr(1 - EccentricitySquared * Sin(latitude * 3.14159265358979 / 180) ^ 2)

Â Â Â  Dim T As Double
Â Â Â  T = Tan(latitude * 3.14159265358979 / 180) ^ 2

Â Â Â  Dim C As Double
Â Â Â  C = EccentricitySquared * Cos(latitude * 3.14159265358979 / 180) ^ 2

Â Â Â  Dim A As Double
Â Â Â  A = Cos(latitude * 3.14159265358979 / 180) * (longitude - CentralMeridian) * 3.14159265358979 / 180

Â Â Â  Dim M As Double
Â Â Â  M = EquatorialRadius * ((1 - EccentricitySquared / 4 - 3 * EccentricitySquared ^ 2 / 64 - 5 * EccentricitySquared ^ 3 / 256) * latitude * 3.14159265358979 / 180 - (3 * EccentricitySquared / 8 + 3 * EccentricitySquared ^ 2 / 32 + 45 * EccentricitySquared ^ 3 / 1024) * Sin(2 * latitude * 3.14159265358979 / 180) + (15 * EccentricitySquared ^ 2 / 256 + 45 * EccentricitySquared ^ 3 / 1024) * Sin(4 * latitude * 3.14159265358979 / 180) - (35 * EccentricitySquared ^ 3 / 3072) * Sin(6 * latitude * 3.14159265358979 / 180))

Â Â Â  Dim UTMScaleFactor As Double
Â Â Â  UTMScaleFactor = 0.9996
Â Â Â
Â Â Â  Dim UTMFalseNorthing As Double
Â Â Â  If hemisphere = 1 Then
Â Â Â Â Â Â Â  UTMFalseNorthing = 0
Â Â Â  Else
Â Â Â Â Â Â Â  UTMFalseNorthing = 10000000
Â Â Â  End If
Â Â Â
Â Â Â  Dim UTMNorthing As Double
Â Â Â  UTMNorthing = UTMScaleFactor * (M + N * Tan(latitude * 3.14159265358979 / 180) * (A ^ 2 / 2 + (5 - T + 9 * C + 4 * C ^ 2) * A ^ 4 / 24 + (61 - 58 * T + T ^ 2 + 600 * C - 330 * EccentricitySquared) * A ^ 6 / 720)) + UTMFalseNorthing

Â Â Â  CalculateUTMNorthing = Format(UTMNorthing, "0.00")

End Function``````

#### 1.3 User-defined Function for UTM Zone

In this context, we will implement a function named CalculateUTMZone. We must create another function to find the Latitude Band. However, the function will be called within CalculateUTMZone.

Likewise, Open VBA Editor >> choose Insert >> click on Module >> paste the below code >> Save.

``````Function CalculateUTMZone(latitude As Double, longitude As Double) As String

Â Â Â  Dim utmZone As Integer
Â Â Â  Dim LatitudeBand As String
Â Â Â
Â Â Â  utmZone = Int((longitude + 180) / 6) + 1
Â Â Â
Â Â Â  LatitudeBand = GetLatitudeBand(latitude)

Â Â Â  CalculateUTMZone = utmZone & LatitudeBand

End Function

Function GetLatitudeBand(latitude As Double) As String

Â Â Â  Dim LatitudeBand As String
Â Â Â
Â Â Â  If latitude >= 0 Then
Â Â Â Â Â Â Â  Select Case latitude
Â Â Â Â Â Â Â Â Â Â Â  Case 0 To 8: LatitudeBand = "N"
Â Â Â Â Â Â Â Â Â Â Â  Case 8 To 16: LatitudeBand = "P"
Â Â Â Â Â Â Â Â Â Â Â  Case 16 To 24: LatitudeBand = "Q"
Â Â Â Â Â Â Â Â Â Â Â  Case 24 To 32: LatitudeBand = "R"
Â Â Â Â Â Â Â Â Â Â Â  Case 32 To 40: LatitudeBand = "S"
Â Â Â Â Â Â Â Â Â Â Â  Case 40 To 48: LatitudeBand = "T"
Â Â Â Â Â Â Â Â Â Â Â  Case 48 To 56: LatitudeBand = "U"
Â Â Â Â Â Â Â Â Â Â Â  Case 56 To 64: LatitudeBand = "V"
Â Â Â Â Â Â Â Â Â Â Â  Case 64 To 72: LatitudeBand = "W"
Â Â Â Â Â Â Â Â Â Â Â  Case Else: LatitudeBand = "X"
Â Â Â Â Â Â Â  End Select
Â Â Â  Else
Â Â Â Â Â Â Â  Select Case -latitude
Â Â Â Â Â Â Â Â Â Â Â  Case 0 To 8: LatitudeBand = "M"
Â Â Â Â Â Â Â Â Â Â Â  Case 8 To 16: LatitudeBand = "L"
Â Â Â Â Â Â Â Â Â Â Â  Case 16 To 24: LatitudeBand = "K"
Â Â Â Â Â Â Â Â Â Â Â  Case 24 To 32: LatitudeBand = "J"
Â Â Â Â Â Â Â Â Â Â Â  Case 32 To 40: LatitudeBand = "H"
Â Â Â Â Â Â Â Â Â Â Â  Case 40 To 48: LatitudeBand = "G"
Â Â Â Â Â Â Â Â Â Â Â  Case 48 To 56: LatitudeBand = "F"
Â Â Â Â Â Â Â Â Â Â Â  Case 56 To 64: LatitudeBand = "E"
Â Â Â Â Â Â Â Â Â Â Â  Case 64 To 72: LatitudeBand = "D"
Â Â Â Â Â Â Â Â Â Â Â  Case Else: LatitudeBand = "C"
Â Â Â Â Â Â Â  End Select
Â Â Â  End If
Â Â Â
Â Â Â  GetLatitudeBand = LatitudeBand
Â Â Â
End Function``````

### Step 2: Convert Lat Long to UTM Easting

First, choose cell D5 >> insert the following formula >> drag the Fill Handle icon to D13.

`=CalculateUTMEasting(B6,C6)`

### Step 3: Convert Lat Long to UTM Northing

First, select cell E5 >> insert the following formula >> drag the Fill Handle icon to E13.

`=CalculateUTMNorthing(B6,C6)`

### Step 4: Convert Lat Long to UTM Zone

To begin, select cell F5 >> insert the below formula >> drag the Fill Handle icon to F13.

`=CalculateUTMZone(B6,C6)`

## Convert Lat Long to UTM Zone in Excel: 2 Simple Methods

For illustration, the following data set has both latitude and longitude values. Using Google Earth, we get these values from some states in America and Africa. We’ll convert latitude and longitude to UTM in Excel using these two methods.

### 1. Combine IF, CONCATENATE, and INT Functions to Convert Lat Long to UTM in Excel

Here are the IF, CONCATENATE, and INT functions that we’ll apply in this method. The IF function lets you compare a value with what you expect logically. The CONCATENATE function enables us to merge the contents of many cells into one. Excel’s INT function retrieves the integer component of a decimal value by rounding it down. We can use these three functions to Convert Lat Long to UTM in Excel. Therefore, follow the steps below to perform the task.

STEPS:

• Firstly, I start by entering the following Excel formula into the cell D5 >>Â hit Enter.
=IF(B5>=0,CONCATENATE(INT(31+C5/6),” N”),CONCATENATE(INT(31+C5/6),” S”))

Now, use the formula used in the D5 cell to get the UTM of the other Lat and Long values. To achieve this, click on the Fill Handle icon.

• Subsequently, hold it down as well as drag it to cell D10.
• As a result, it will return the desired output, as shown below.

🔎 How Does the Formula Work?

For this formula to make sense, you need to know how to use the following Excel functions:

INT, IF, and CONCATENATE

Now, observe these formulas.

• INT(31+C5/6)

The mathematical expression gives the values – {13.36236052;11.56792704;11.18443451;14.66315629;11.98617117;34.99888883}

Then, by involving the INT function we get – {13;11;11;14;11;34}

• CONCATENATE(INT(31+C5/6),” N”) or CONCATENATE(INT(31+C5/6),” S”)

CONCATENATE(INT(31+C5/6),” N”) joins the output value of INT(31+C5/6) with N. And CONCATENATE(INT(31+C5/6),” S”) merges the output value of INT(31+C5/6) with S. Here, CONCATENATE function concatenates N for true and S for false.

• IF(B5>=0,CONCATENATE(INT(31+C5/6),” N”),CONCATENATE(INT(31+C5/6),” S”))

Lastly, the IF function evaluates cells B5 to B10 of our specified dataset – {True;True;True;True;True;False}

Therefore, we get – {13 N;11 N;11 N;14 N;11 N;34 S}

### 2. Transform Lat Long to UTM Through Excel VBA

Microsoft has created the Visual Basic for Application (VBA) programming language. VBA is used to execute various operations, and different user types operate the programming language for multiple purposes. Here we will Convert Lat Long to UTM using some functions of the VBA language. Hence, study the procedure detailed below.

STEPS:

• First, choose the active sheet >> Secondly, go to Developer >> Then, click on Visual Basic.

• Next, select Insert and later click Module.

• After that, put the following code in the Module box.
``````Sub SOFTEKOlatLongToUtm()
Dim i As Integer
Dim lat As Double
Dim lng As Double
Dim UTM
Dim UTMint
For i = 5 To 10
lat = Cells(i, 2).Value
lng = Cells(i, 3).Value
UTM = 31 + lng / 6
UTMint = CInt(UTM)
If lat >= 0 Then
UTMint = UTMint & " N"
Else
UTMint = UTMint & " S"
End If
Cells(i, 4).Value = UTMint
Next
End Sub``````

• Lastly, press F5 or click the Run button.
• Thus, this is where the output will be directed.

You are welcome to get a free copy of the example workbook just below, and we used that all during the session.

## Conclusion

From now on, you can convert Lat Long to UTM in Excel using the steps we just discussed.Â Keep using those, and let us know if you have any other ways to get the work done or if you have any new ideas. Remember to post questions, comments, or suggestions in the section below.

<< Go Back to Geocoding in Excel | Learn Excel

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. Thank you but this doesn’t convert lat long to utm. Just telling which zone it is.

Lutfor Rahman Shimanto Aug 13, 2023 at 12:37 PM

Dear Amir

Sorry for the late reply. Thanks a lot for bringing the issue to us. I have developed some user-defined functions using Excel VBA. These functions take Lat and Long values and return UTM Easting, Northing and Zone. I also have modified the article accordingly. So, I recommend you to go through the article again. You will not be disappointed, I promise.

Regards
Lutfor Rahman Shimanto

• Hello, AMIR!

Yes! The code you provided is a simple implementation for converting latitude and longitude to UTM in Excel VBA. However, it is not the most accurate way to convert coordinates, as it uses a simplified formula for converting latitude and longitude to UTM coordinates.

The formula used in this code only accounts for the UTM zone and hemisphere based on the latitude and longitude values. It does not take into account the curvature of the Earth’s surface, which can lead to inaccuracies in the UTM coordinates. If you need this, then you can use the following code:

``````Function LatLonToUTM(Lat As Double, Lon As Double) As String
'Declare variables for UTM coordinates
Dim Zone As Integer, Easting As Double, Northing As Double

'Convert Lat/Lon to UTM using Proj4 library
Dim Proj As Object
Set Proj = CreateObject("OSGeo.OSR.SpatialReference")
Proj.ImportFromEPSG 4326 ' WGS84
Proj.SetUTM Zone(Lon), (Lat >= 0)

Dim LatLon(0 To 1) As Double
LatLon(0) = Lon
LatLon(1) = Lat

Dim xy(0 To 1) As Double
Proj.TransformPoint xy(0), xy(1), 0, LatLon(0), LatLon(1), 0

'Convert UTM coordinates to string format
LatLonToUTM = Chr(Zone + 64) & " " & Format(Easting, "0.00") & " " & Format(Northing, "0.00")
End Function``````
``````Function Zone(Lon As Double) As Integer
Zone = Int((Lon + 180) / 6) + 1
End Function``````

To use this code, open a new Excel workbook, press ALT+F11 to open the VBA editor, and insert a new module. Copy and paste the code into the module, and save the module.
Then, in your Excel sheet, you can use the formula =LatLonToUTM(lat, lon) where lat and lon are the latitude and longitude coordinates you want to convert to UTM format.
This code uses the Proj4 library to perform the coordinate transformation. You may need to install this library on your computer if it is not already installed.
And if you don’t want to use this library. you can use the following code instead.

``````Function LatLongToUTM(ByVal latitude As Double, ByVal longitude As Double) As String

'Define constants for the conversion
Const PI As Double = 3.14159265358979
Const a As Double = 6378137
Const b As Double = 6356752.3142
Const k0 As Double = 0.9996

'Convert latitude and longitude to radians
lat_rad = latitude * PI / 180
long_rad = longitude * PI / 180

'Define constants for the conversion
Dim e As Double
e = Sqr(1 - (b / a) ^ 2)
Dim e1sq As Double
e1sq = (e ^ 2) / (1 - e ^ 2)
Dim n As Double
n = (a - b) / (a + b)
Dim rho As Double
rho = a * (1 - e ^ 2) / (1 - (e ^ 2) * (Sin(lat_rad) ^ 2)) ^ 1.5
Dim nu As Double
nu = a / Sqr(1 - (e ^ 2) * (Sin(lat_rad) ^ 2))
Dim psi As Double
psi = nu / rho
Dim v As Double
v = (k0 * nu) / psi
Dim p As Double
p = long_rad - (2 * PI * Int((long_rad + PI) / (2 * PI)))
Dim l As Double
l = (p - 0.2617993877991495) * 180 / PI

'Calculate UTM zone number
Dim zone As Integer
zone = Int((long_rad + PI) / (2 * PI)) + 1
If zone > 60 Then
zone = zone - 60
End If

'Calculate UTM x and y coordinates
Dim x As Double
Dim y As Double
x = v * Sinh(l / (2 * 1.005037))
y = v * (Cosh(l / (2 * 1.005037)) * Tanh(Asinh(Sin(lat_rad) / Sqrt(1 + e1sq * (Cos(lat_rad) ^ 2))) - (e * Atanh(e * Sinh(Asinh(Sin(lat_rad) / Sqrt(1 + e1sq * (Cos(lat_rad) ^ 2))))))))

'Convert x and y to UTM coordinates
x = x + 500000
If y  60 Then
zone = zone - 60
End If

'Calculate UTM x and y coordinates
Dim x As Double
Dim y As Double
x = v * Sinh(l / (2 * 1.005037))
y = v * (Cosh(l / (2 * 1.005037)) * Tanh(Asinh(Sin(lat_rad) / Sqrt(1 + e1sq * (Cos(lat_rad) ^ 2))) - (e * Atanh(e * Sinh(Asinh(Sin(lat_rad) / Sqrt(1 + e1sq * (Cos(lat_rad) ^ 2))))))))

'Convert x and y to UTM coordinates
x = x + 500000
If y < 0 Then
y = y + 10000000
End If

' Define UTM zone letter
Dim letters As String
letters = "CDEFGHJKLMNPQRSTUVWXX"
Dim zone_letter As String
zone_letter = Mid(letters, zone, 1)

' Return UTM coordinates as string
LatLongToUTM = zone & zone_letter & " " & Format(x, "0.00") & " " & Format(y, "0.00")

End Function``````

This code converts latitude and longitude coordinates to UTM coordinates and returns the result as a string in the format "Zone Letter X Y". You can call this function by passing the latitude and longitude values as parameters, like this:

``````Sub ConvertLatLongToUTM()
'Define latitude and longitude values
Dim latitude As Double
Dim longitude As Double
latitude = 37.7749
longitude = -122.4194

'Call LatLongToUTM function
Dim utm_coordinates As String
utm_coordinates = LatLongToUTM(latitude, longitude)

'Display UTM coordinates in message box
MsgBox utm_coordinates

End Sub``````

Make sure you have the LatLongToUTM function defined in your VBA code module before running the ConvertLatLongToUTM sub.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy.

2. Nothing on Eastings and Northing, its all about zoning only.

Lutfor Rahman Shimanto Aug 13, 2023 at 12:34 PM

Hello MOSES

Thanks for reaching out and posting your comment. You are absolutely right. However, I am delighted to inform you that I have modified the article. After going through the article, you must be able to convert Lat Long to UTM Easting, Northing and Zone. I have developed some user-defined functions using Excel VBA to achieve the goal.

Regards
Lutfor Rahman Shimanto

3. Hi

This article has been extremely helpful, The codes above worked for Easting and Zone but not the Northing UTM…. I can’t seem to find a fix that works at the moment, even with your numbers it wouldn’t work. Any tips?

Lutfor Rahman Shimanto Aug 16, 2023 at 3:22 PM

Dear Faisal

Thanks a ton. Your appreciations mean a lot to us. I am glad you have found the article very helpful.

You mentioned the user-defined functions only work for UTM Zone and UTM Easting on your end. The user-defined function for calculating UTM Northing is not working.

However, the issue you are addressing is unclear to us. On our end, it is working perfectly. I am using Microsoft 365. However, It should not make any difference. The code should work for all versions of Excel.

Please share your workbook containing a dataset with us. You can also post your query and attach the dataset in ExcelDemy Forum. We need more information about your problem, such as if it returns any error or wrong values.

The functions often may return values using scientific notation like 2.34344E+13. To avoid this type of notation, you may use an Event procedure.

Excel VBA Event Procedure:

``````Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
Dim funcName As String

funcName = "CalculateUTM"

For Each cell In Target
If InStr(1, cell.Formula, funcName) > 0 Then
cell.NumberFormat = "0.00"
End If
Next cell

End Sub``````

I am looking forward to receiving your reply. Stay blessed and Good luck.

Regards
Lutfor Rahman Shimanto

4. hi ,sir
you table northing cannot use ,it doesnot working ,would you please update it again ,we use the office 2016 or 2019,thanks .

Lutfor Rahman Shimanto Nov 20, 2023 at 3:11 PM

Hello XU

Thanks for reaching out. All the User-Defined functions mentioned in this article calculate UTM Easting, UTM Northing and UTM Zone accurately.

I am comparing the result with an Online UTM Calculator website (LatLong.net) like the image below for demonstration.

If you still have doubts, you can share your dataset in our ExcelDemy Forum. Good luck!

Regards
Lutfor Rahman Shimanto

5. hi sir ,would u please write UTM convert to Lat long we want to this ,thanks so much.

Lutfor Rahman Shimanto Nov 20, 2023 at 3:16 PM

Hello XU

Thanks for thanking me. Your appreciation means a lot to us. You wanted to convert UTM (UTM Easting, UTM Northing and UTM Zone) to Latitude and Longitude values.

I am presenting some Excel VBA User-Defined functions, and these functions will achieve your goal.

Step 1: Open the VBA Editor window => Paste the following code in a module => Save.

``````
Function CalculateLatitude(UTMNorthing As Double, UTMZone As Integer) As Double

Dim EccentricitySquared As Double
EccentricitySquared = 0.00669438

Dim UTMScaleFactor As Double
UTMScaleFactor = 0.9996

Dim UTMFalseNorthing As Double
If UTMNorthing < 0 Then
UTMFalseNorthing = 10000000
Else
UTMFalseNorthing = 0
End If

Dim M As Double
M = UTMNorthing / UTMScaleFactor - UTMFalseNorthing

Dim Mu As Double
Mu = M / (EquatorialRadius * (1 - EccentricitySquared / 4 - 3 * EccentricitySquared ^ 2 / 64 - 5 * EccentricitySquared ^ 3 / 256))

Phi1Rad = Mu + (3 * EccentricitySquared / 2 - 27 * EccentricitySquared ^ 3 / 32) * Sin(2 * Mu) _
+ (21 * EccentricitySquared ^ 2 / 16 - 55 * EccentricitySquared ^ 4 / 32) * Sin(4 * Mu) _
+ (151 * EccentricitySquared ^ 3 / 96) * Sin(6 * Mu) _
+ (1097 * EccentricitySquared ^ 4 / 512) * Sin(8 * Mu)

Dim phi1 As Double
phi1 = Phi1Rad * 180 / 3.14159265358979

Dim N1 As Double

Dim T1 As Double

Dim C1 As Double
C1 = EccentricitySquared * Cos(Phi1Rad) ^ 2

Dim R1 As Double
R1 = EquatorialRadius * (1 - EccentricitySquared) / ((1 - EccentricitySquared * Sin(Phi1Rad) ^ 2) ^ 1.5)

Dim D As Double
D = (UTMNorthing / (N1 * UTMScaleFactor))

Dim Latitude As Double
Latitude = Phi1Rad - (N1 * Tan(Phi1Rad) / R1) * (D ^ 2 / 2 - (5 + 3 * T1 + 10 * C1 - 4 * C1 ^ 2 - 9 * EccentricitySquared) * D ^ 4 / 24 + (61 + 90 * T1 + 298 * C1 + 45 * T1 ^ 2 - 3 * C1 ^ 2 - 252 * EccentricitySquared) * D ^ 6 / 720)

CalculateLatitude = Latitude * 180 / 3.14159265358979

End Function

Function CalculateLongitude(UTMEasting As Double, UTMZone As Integer) As Double

Dim CentralMeridian As Double
CentralMeridian = (UTMZone - 1) * 6 - 180 + 3

Dim ScaleFactor As Double
ScaleFactor = 0.9996

Dim EccentricitySquared As Double
EccentricitySquared = 0.00669438

Dim UTMEastingShifted As Double
UTMEastingShifted = UTMEasting - 500000

Dim A1 As Double
A1 = Cos(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor)

Dim A2 As Double
A2 = -Sin(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor)

Dim A3 As Double
A3 = Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor)

Dim A4 As Double
A4 = -Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor) ^ 3 * (1 + Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) ^ 2) / 2

Dim A5 As Double
A5 = Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor) ^ 5 * (5 + 3 * Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) ^ 2 + 6 * Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) ^ 4) / 24

Dim A6 As Double
A6 = -Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor) ^ 7 * (61 + 90 * Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) ^ 2 + 45 * Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) ^ 4) / 720

Dim Longitude As Double
Longitude = CentralMeridian + (A1 + A2 + A3 + A4 + A5 + A6) * 180 / 3.14159265358979

CalculateLongitude = Longitude

End Function

Function GetUTMZoneNumber(UTMZone As String) As Integer
GetUTMZoneNumber = Val(Left(UTMZone, Len(UTMZone) - 1))
End Function
``````

Step 2: Return to the sheet => Choose cell E4 => Insert the following formula => And drag the Fill Handle icon to cell E11.

=CalculateLatitude(C4,GetUTMZoneNumber(D4))

Step 3: Choose cell F4 => Insert the following formula => And drag the Fill Handle icon to cell F11.

=CalculateLongitude(B4,GetUTMZoneNumber(D4))

Things to Keep in Mind:
As we cannot use all decimal points for some values (UTM Northing), we may not get the same value (Latitude) like previous one.

Regards
Lutfor Rahman Shimanto

6. The CalculateUTMNorthing function is simply giving a #VALUE! error on Excel. Not really sure how to debug this because it is very generic

Lutfor Rahman Shimanto Feb 7, 2024 at 4:48 PM

Hello CHRISTOF

Thanks for sharing your queries. Depending on your OS Version, the User-defined functions may give this type of error. Ensure you run a 64-bit Windows, and let us know if you still face the error.

Regards
Lutfor Rahman Shimanto
ExcelDemy

7. Hello, thank you for the codes. Extremely helpful. However, when I entered the codes and then calculate the Northing, I got “#value” as an output. Wonder if you can help.

Lutfor Rahman Shimanto Mar 7, 2024 at 6:05 PM

Hello! DIANA. Thanks for sharing your queries. Depending on your OS version, the user-defined functions may cause this error. Ensure you run 64-bit Windows, and let us know if you are still facing the error.

Regards
Lutfor Rahman Shimanto
ExcelDemy

Alan Jorge Innes Mar 5, 2024 at 3:17 AM

Hi I have a question. I am trying to use your code and it worked for UTM Easting and UTM zone but I am getting a “#VALUE!” error for northing. Please contact me and I can send you my dataset if you are able to help. Thank you.

Lutfor Rahman Shimanto Mar 7, 2024 at 6:10 PM

Hello ALAN

Thank you for reaching out. Please ensure that you’re using a 64-bit Windows system. Let us know if the issue persists.

You can share your dataset by creating a Conversation with my forum account in the ExcelDemy Forum.

Best regards
Lutfor Rahman Shimanto
ExcelDemy

Advanced Excel Exercises with Solutions PDF