How to Convert Lat Long to UTM in Excel (2 Suitable Ways)

Working with geographic coordinates in Excel often requires converting between Latitude/Longitude (Lat/Long) and the UTM (Universal Transverse Mercator) format. While Excel doesn’t offer built-in support for this conversion, you can achieve accurate results using a custom VBA script.

In this article, you’ll learn how to convert Lat Long to UTM in Excel using reliable VBA code. We’ll guide you through setting up a reusable macro that outputs UTM Zone, Easting, and Northing coordinates which is ideal for mapping and GIS tasks.

Function LatLonToUTM(Lat As Double, Lon As Double) As String
    Dim Zone As Integer
    Dim Hemisphere As String
    Zone = Int((Lon + 180) / 6) + 1
    If Lat >= 0 Then
        Hemisphere = "N"
    Else
        Hemisphere = "S"
    End If
    LatLonToUTM = "Zone " & Zone & Hemisphere
End Function

This method lets you convert latitude and longitude to UTM directly in Excel using a simple VBA script. It’s ideal if you want more control over the conversion process without relying on external tools.

Once the code is added, you’ll be able to use a custom formula just like any built-in Excel function. It’s fast, accurate, and works well for both small and large sets of coordinates.

Steps:

➤ Press  Alt  +  F11  to open the VBA Editor.
➤ Go to Insert >> Module.
➤ Paste the following code into the module:

Function LatLonToUTM(Lat As Double, Lon As Double) As String
    Dim Zone As Integer
    Dim Hemisphere As String
    Zone = Int((Lon + 180) / 6) + 1
    If Lat >= 0 Then
        Hemisphere = "N"
    Else
        Hemisphere = "S"
    End If
    LatLonToUTM = "Zone " & Zone & Hemisphere
End Function

➤ Close the VBA Editor and return to your worksheet.
➤ In Excel, use this formula in C2 cell:

➤ Drag down to apply to the rest of the cells.

This will return the UTM zone and hemisphere (e.g., “Zone 33N”).

If you’re working with geographic coordinates and need precise UTM (Universal Transverse Mercator) values, this method is for you. Instead of relying on manual formulas or external tools, we’ll use a powerful VBA script that converts latitude and longitude into full UTM coordinates, including both Easting and Northing.

This approach is not only accurate but also scalable, making it ideal for large datasets, GIS projects, and mapping applications. Once set up, you can use this macro repeatedly to process thousands of coordinates within seconds directly inside Excel.

Steps:

➤ Press  Alt  +  F11  to open the VBA Editor.
➤ Go to Insert >> Module.

➤ Paste the following code into the module:

Function GetUTMZone(Lat As Double, Lon As Double) As String
    Dim ZoneNumber As Integer
    Dim Hemisphere As String
    ZoneNumber = Int((Lon + 180) / 6) + 1
    If Lat >= 0 Then
        Hemisphere = "N"
    Else
        Hemisphere = "S"
    End If
    GetUTMZone = ZoneNumber & Hemisphere
End Function
Function GetUTMEasting(Lat As Double, Lon As Double) As Double
    Dim a As Double: a = 6378137#
    Dim f As Double: f = 1 / 298.257223563
    Dim k0 As Double: k0 = 0.9996
    Dim e As Double: e = Sqr(2 * f - f ^ 2)
    Dim ZoneNumber As Integer: ZoneNumber = Int((Lon + 180) / 6) + 1
    Dim LongOrigin As Double: LongOrigin = (ZoneNumber - 1) * 6 - 180 + 3
    Dim latRad As Double: latRad = Lat * WorksheetFunction.Pi() / 180
    Dim lonRad As Double: lonRad = Lon * WorksheetFunction.Pi() / 180
    Dim longOriginRad As Double: longOriginRad = LongOrigin * WorksheetFunction.Pi() / 180
    Dim N As Double: N = a / Sqr(1 - e ^ 2 * Sin(latRad) ^ 2)
    Dim T As Double: T = Tan(latRad) ^ 2
    Dim C As Double: C = (e ^ 2 / (1 - e ^ 2)) * Cos(latRad) ^ 2
    Dim Aterm As Double: Aterm = Cos(latRad) * (lonRad - longOriginRad)
    Dim M As Double
    M = a * ((1 - e ^ 2 / 4 - 3 * e ^ 4 / 64 - 5 * e ^ 6 / 256) * latRad _
      - (3 * e ^ 2 / 8 + 3 * e ^ 4 / 32 + 45 * e ^ 6 / 1024) * Sin(2 * latRad) _
      + (15 * e ^ 4 / 256 + 45 * e ^ 6 / 1024) * Sin(4 * latRad) _
      - (35 * e ^ 6 / 3072) * Sin(6 * latRad))
    GetUTMEasting = k0 * N * (Aterm + (1 - T + C) * Aterm ^ 3 / 6 _
      + (5 - 18 * T + T ^ 2 + 72 * C - 58 * e ^ 2) * Aterm ^ 5 / 120) + 500000
End Function
Function GetUTMNorthing(Lat As Double, Lon As Double) As Double
    Dim a As Double: a = 6378137#
    Dim f As Double: f = 1 / 298.257223563
    Dim k0 As Double: k0 = 0.9996
    Dim e As Double: e = Sqr(2 * f - f ^ 2)
    Dim ZoneNumber As Integer: ZoneNumber = Int((Lon + 180) / 6) + 1
    Dim LongOrigin As Double: LongOrigin = (ZoneNumber - 1) * 6 - 180 + 3
    Dim latRad As Double: latRad = Lat * WorksheetFunction.Pi() / 180
    Dim lonRad As Double: lonRad = Lon * WorksheetFunction.Pi() / 180
    Dim longOriginRad As Double: longOriginRad = LongOrigin * WorksheetFunction.Pi() / 180
    Dim N As Double: N = a / Sqr(1 - e ^ 2 * Sin(latRad) ^ 2)
    Dim T As Double: T = Tan(latRad) ^ 2
    Dim C As Double: C = (e ^ 2 / (1 - e ^ 2)) * Cos(latRad) ^ 2
    Dim Aterm As Double: Aterm = Cos(latRad) * (lonRad - longOriginRad)
    Dim M As Double
    M = a * ((1 - e ^ 2 / 4 - 3 * e ^ 4 / 64 - 5 * e ^ 6 / 256) * latRad _
      - (3 * e ^ 2 / 8 + 3 * e ^ 4 / 32 + 45 * e ^ 6 / 1024) * Sin(2 * latRad) _
      + (15 * e ^ 4 / 256 + 45 * e ^ 6 / 1024) * Sin(4 * latRad) _
      - (35 * e ^ 6 / 3072) * Sin(6 * latRad))
    GetUTMNorthing = k0 * (M + N * Tan(latRad) * (Aterm ^ 2 / 2 + (5 - T + 9 * C + 4 * C ^ 2) * Aterm ^ 4 / 24 _
      + (61 - 58 * T + T ^ 2 + 600 * C - 330 * e ^ 2) * Aterm ^ 6 / 720))
    If Lat < 0 Then
        GetUTMNorthing = GetUTMNorthing + 10000000
    End If
End Function

➤Close the VBA editor.
➤ Add three new columns called Easting, Northing and Zone.

➤ For UTM Easting, Enter this formula in C2:

➤ For UTM Northing, Enter this formula in D2:

➤ For UTM Zone, Enter this formula in E2:

➤ Drag down the formulas through each column using the Auto-fill handle.

This will return a collection containing the Easting, Northing, and UTM Zone.

Frequently Asked Questions

Can Excel convert lat long to UTM natively?

No, Excel lacks a native function for converting latitude and longitude to UTM. You need to use VBA scripts, add-ins, or external tools to perform this conversion accurately.

What’s the easiest method for beginners?

Beginners should use an online converter to quickly convert lat long to UTM. For frequent tasks, learning VBA or using an add-in automates the process and saves time.

What UTM zone should I use?

UTM zones depend on longitude; each zone covers 6 degrees. Identify your longitude to select the correct zone to ensure accurate Easting and Northing coordinates for your location.

What does ‘Zone 33N’ mean?

Zone 33 means the coordinate lies within a 6-degree longitudinal zone numbered 33. The “N” indicates the Northern Hemisphere, distinguishing it from the Southern Hemisphere zones.

Can I get full Easting and Northing from VBA?

Basic VBA scripts don’t always provide precise Easting and Northing values. For high accuracy, use advanced geospatial libraries or GIS software like QGIS for complete coordinate conversion.

Wrapping Up

In this tutorial, we learned how to convert Lat Long to UTM in Excel using two custom VBA methods one for simple zone labeling and another for full Easting and Northing coordinate output. These approaches are ideal for users who need to handle geographic data without relying on external software. Feel free to download the practice file and share your thoughts and suggestions.