How to Convert Northing Easting to Lat Long in Excel

Location mapping in land surveys, GIS work is usually done using Easting Northing coordinates, while Google Maps uses Latitude Longitude format. So, converting Easting Northing coordinates to Latitude Longitude format becomes a vital task.

Excel is a perfect tool that can be used for this conversion. Since there are no built-in functions, you can easily create a custom VBA function to handle these conversions easily.

Key Takeaways:

• Northing Easting, and Latitude Longitude use different coordinate systems.
• Conversion of Northing Easting is essential for location mapping on different platforms.
• Create custom VBA functions to convert between these two formats.
• Make sure that the data is correct and is in consistent units.
• Proper preparation enables easy management of huge datasets using the outlined steps.

 

Unveiling the Excel Magic

The Need for Converting Coordinates in Excel

When you are locating a place on maps or working with geographical data, the skill to convert coordinates can come in handy. For example, if you have the data in Easting and Northing format, you may need to convert it to a widely accepted format like latitude and longitude. This process will help you in being able to use the data in various platforms and with different users.

Overview of Easting/Northing and Lat/Long Systems

Understanding both Easting/Northing and Latitude/Longitude systems is important for anyone working with maps or geographic information. Easting and Northing coordinates are often utilized in the Universal Transverse Mercator (UTM) system. These are Cartesian coordinates measured in meters, with Easting indicating the distance east from the central meridian of a UTM zone and Northing showing the distance north from the equator.

In contrast, Latitude and Longitude are angular measurements used in the geographic coordinate system. Latitude measures how far north or south a point is from the equator, while Longitude measures how far east or west a point is from the Prime Meridian. These measurements are commonly expressed in degrees, minutes, and seconds.

Recognizing the differences between these systems is important for accurately converting coordinates, as it involves a transformation not only between units but also between coordinate frameworks.

 

Preparing the Data

Collecting Data

The first step should be to export the data from any software or databases in Easting and Northing format. After that, input these data in two different columns – one for Easting and one for Northing. Make sure that each column is properly labelled in Excel.

Formatting the Data

The data should be formatted correctly in order to get accurate results. Select the data and then go to the Home tab and from the dropdown select ‘Number’.

 

Conversion Process

Creating a VBA Function

Since there are no built-in functions for the conversion, you need to create a custom VBA function. Follow the steps below to create a custom VBA function to convert Northing Easting coordinates to Latitude Longitude format.

STEP 1: Go to the Developer tab and select Visual Basic to create a new module.

STEP 2: In the VBA editor, create a custom function to convert values in UTM zone to Lat Long format.

Once that is done, you can save it and close the editor. The new function is now ready to be used!

STEP 3: Enter the following formula for Latitude –

For Longitude –

Then, simply drag the function’s corner down the column to apply it to all the data entries.

Please note that when you are working with a large dataset, the conversion process may take a while.

 

Tips for Assuring Accuracy in Your Conversion

Before we start the conversion process, check the following points:

• It is vital to make sure that the coordinates are accurate
• You are using the correct formula for conversion
• Incorrect input data can lead to significant discrepancies
• Post conversion, check the converted coordinates with a reliable source

Lastly, make sure to review the conversion process and outcome with a GIS professional if you are not confident with the converted coordinates.

FAQs

What are the Common mistakes in converting Easting/Northing to Lat/Long?

The common mistakes that one can face when converting coordinates are:

• Using an incorrect UTM zone
• Not setting the correct hemisphere
• Using mixed units
• Wrong decimal placement

Always make sure to check the data and fix these mistakes before proceeding with the conversion process.

How to convert Easting Northing to Longitude and Latitude format?

You can easily convert coordinates to longitude and latitude by using a custom VBA function. These functions use Easting/Northing values as inputs and convert them into longitude and latitude.

How to check if the converted latitude and longitude are correct?

Simply, enter the values into the Google map, and if the location matches the expected place, the conversion is correct.

Why does the conversion process take more time with a large dataset?

When working with a large dataset, Excel will take more time to provide the result as it will calculate each row one by one. To speed up the process, you can change the automatic calculation option from Automatic to Manual.

Do we need the UTM zone for converting coordinates?

Yes, we need a UTM zone for converting coordinates from Easting/Northing to Lat/Long. Without a correct UTM zone, you may get incorrect result.