Convert Easting Northing to Lat Long in Excel (2 Easy Methods)

In this article, we will demonstrate how to convert Easting Northing coordinates to Lat Long using Excel. We will provide step-by-step instructions, formulas, and even a VBA code for automating the process.

By converting Easting Northing coordinates to Latitude Longitude in Excel, one can convert coordinates from a Cartesian system to the more widely used Latitude Longitude system, making it simpler to integrate with mapping and navigational software.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

What Is Coordinate (Northing, 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. Depending on the coordinate system used, these coordinates are often given in meters or feet.

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.

How to Convert Easting Northing to Lat Long in Excel: 2 Methods

1. Using Formula to Convert Easting Northing to Latitude Longitude in Excel

1.1 Latitude Longitude in Decimal Degree Format

In this section, we will use different functions to convert easting northing to latitude longitude in Excel. Here we will use ATANH, ATAN2, SIN, and COS functions to do our task.

The ATANH function is used to return the inverse hyperbolic tangent of a number.

Syntax of ATANH function is:

• Write down the following formula to convert easting northing to latitude (decimal).

Additionally, the ATAN2 function returns the  inverse tangent of the specified x- and y-coordinates.

Syntax of ATAN2 function is:

• Write down the following formula to convert easting northing to longitude (decimal).

1.2 Latitude Longitude in Degree, Minute and Second Format

Here, we will use CONCATENATE, TEXT and INT Functions to convert latitude longitude in degree, minute, and second format.

• Write down the following formula to convert easting northing to latitude ( degree, minute & second)

• Write down the following formula to convert easting northing to longitude (degree, minute & second).

2. Using VBA Code to Convert Easting Northing to Latitude Longitude

Here, we will demonstrate how to convert easting northing to latitude longitude in Excel.

• Go to the Developer tab on your ribbon to open the VBA window. Afterward, select Visual Basic from the Code group.

• To insert a module for the code, go to the Insert tab on the VBA editor. Afterward, click on Module from the drop-down.

write down the following code in it.

Sub ConvertCoordinates()
    Dim lng As Double
    Dim lat As Double
    Dim eastingRange As Range
    Set eastingRange = Range("B5:B11") 
    Dim northingRange As Range
    Set northingRange = Range("C5:C11") 
    Dim i As Long
    For i = 1 To eastingRange.Rows.Count
        ' Get the Easting and Northing values for the current row
        Dim easting As Double
        Dim northing As Double
        easting = eastingRange.Cells(i).Value
        northing = northingRange.Cells(i).Value
        lng = WorksheetFunction.Atan2(Sin(easting / 6378137) / Cos(northing / 6378137), Cos(easting / 6378137)) * (180 / WorksheetFunction.Pi())
        lat = WorksheetFunction.Atanh(Sin(northing / 6378137) / Cos(easting / 6378137)) * (180 / WorksheetFunction.Pi())
        eastingRange.Cells(i).Offset(0, 2).Value = lat
        northingRange.Cells(i).Offset(0, 2).Value = lng
    Next i
End Sub

• Afterward, close the Visual Basic window. After that, press Alt+F8.
• When the Macro dialogue box opens, select the below Macro name. Click on Run.

• As a result, you will get the converted data from easting northing to latitude longitude.

Frequently Asked Questions

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

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

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

Conclusion

In conclusion, the process of converting Easting and Northing coordinates to latitude and longitude in Excel allows for the transformation of location data from a projected coordinate system to a geographic coordinate system.

Using the aforementioned appropriate formulas, the Easting and Northing values can be converted to decimal degrees or degree, minute, and second formats representing latitude and longitude.