# How to Use Geocoding in Excel? (2 Practical Examples)

Get FREE Advanced Excel Exercises with Solutions!

Geocoding is the process of converting addresses or place names into geographic coordinates. Geographic coordinates are typically latitude and longitude.

In this article, we will try to provide an overview of how to use geocoding in Excel.

Latitude denotes the distance of a location from the equator. It determines the position of a location in the North or South direction.

On the other hand, longitude represents how far east or west a location is from the Prime Meridian. It indicates its position in the East-West direction.

There are many dedicated geocoding tools available. Excel offers powerful geocoding capabilities too.

While preparing this article, we used Excel 365 for applying all operations, however, they are also applicable in all Excel versions.

## What Do You Mean by Geocoding in Excel?

Geocoding in Excel is the process of converting an address into geographic coordinates. Geographic coordinates are typically latitude and longitude. These coordinates define a specific point on the Earthâ€™s surface.

Latitude denotes the distance of a location from the equator. It determines the position of a location in the north or south direction.

On the other hand, longitude represents how far east or west a location is from the Prime Meridian. It indicates its position in the east-west direction.

## How Many Ways to Perform Geocoding in Excel?

We can use Excelâ€™s different features to perform geocoding. Excel has a built-in tool that we can use to geocode along with VBA.

### How to Geocode in Excel with Geography Data Type?

We can geocode in Excel very easily if we have geography data. Letâ€™s follow the steps below to get to know how!

• We have the following city names in column B5:B14.
• Select the city names column and go to the Data tab > Data types > Geography.

• After converting the data to geography, type the following formula in cell C5 to get the latitude of the city.
`=B5.Latitude`

• To get longitude, type the following formula in cell D5.
`=B5.Longitude`

• Use the fill handle to get the latitude and longitude for all the cities.

Note: Geography data type was introduced in Excel 365. If you are using an older conversion of Excel, you may not find this feature in Excel.

### How to Use VBA for Geocoding in Excel?

Using VBA code will allow you to geocode in Excel even if you do not use Excel 365. VBA allows for automating different tasks within Excel easily. We have the following dataset to use VBA for geocoding.

Follow the steps below.

• Go to the Developer tab > Visual Basic.

• Then insert a new module by going to Insert > Module.

• Before writing the code we have to select a reference from the tools panel. Go to Tools > References.

• References â€“ VBAProject dialog box pops up >Â Select Microsoft XML, v3.0, and click on OK.

• Type the following code in the module :
``````Function Co_Ordinates(address As String) As String
Â Â Â  Application.Caller.Font.ColorIndex = xlNone
Â Â Â  Dim xDoc As New MSXML2.DOMDocument
Â Â Â  xDoc.async = False
Â Â Â  xDoc.Load ("https://nominatim.openstreetmap.org/search?format=xml&q=" + WorksheetFunction.EncodeURL(address))
Â Â Â  If xDoc.parseError.ErrorCode <> 0 Then
Â Â Â Â Â Â Â  Application.Caller.Font.ColorIndex = vbErr
Â Â Â Â Â Â Â  Co_Ordinates = xDoc.parseError.reason
Â  Â Â Else
Â Â Â Â Â Â Â  xDoc.SetProperty "SelectionLanguage", "XPath"
Â Â Â Â Â Â Â  Dim loc As MSXML2.IXMLDOMElement
Â Â Â Â Â Â Â  Set loc = xDoc.SelectSingleNode("/searchresults/place")
Â Â Â Â Â Â Â  If loc Is Nothing Then
Â Â Â Â Â Â Â Â Â Â Â  Application.Caller.Font.ColorIndex = vbErr
Â Â Â  Â Â Â Â Â Â Â Â NominatimGeocode = xDoc.XML
Â Â Â Â Â Â Â  Else
Â Â Â Â Â Â Â Â Â Â Â  Application.Caller.Font.ColorIndex = vbOK
Â Â Â Â Â Â Â Â Â Â Â  Co_Ordinates = loc.getAttribute("lat") & "," & loc.getAttribute("lon")
Â Â Â Â Â Â Â  End If
Â Â Â  End If
End Function``````

• Save the code and close the module.
• Type the following formula in cell C5. The formula will return the latitude and longitude of the address in cell B5. Copy the formula down by using flash fill.
`=Co_Ordinates(B5)`

Read More: Perform Reverse Geocoding

## What Things You Have to Remember?

• The geography data type is not available before Excel 365. So you may not find this feature useful if you are using an older version.
• Be sure to save your Excel file as XLSM if there are any VBA codes.
• Make sure you tick Microsoft XML, v3.0 before applying the VBA code provided in this article.

## Conclusion

This article has gone over the methods to use geocoding in Excel. With the powerful features provided by Excel, you can easily convert addresses into geographic coordinates and vice versa. We have shown you the methods of geocoding using theÂ GeocodingÂ feature of Excel. We have also shown how you can use VBA to your advantage to geocode in Excel.

Geocoding allows users to turn addresses into geographic coordinates. This makes location data useful and easily understandable.

Feel free to use the comment box below if you have any questions or suggestions regarding the article. Visit ExcelDemy if you want to enrich your knowledge regarding Excel. We have numerous articles on Excel on our site.

## Frequently Asked Questions

1. Are there any functions available in Excel that geocode automatically?

Ans: Unfortunately, there isnâ€™t any formula available yet in Excel for geocoding purposes. But you can always use VBA to create one for yourself.

2. How do I geocode from an address in Excel?

Ans: If you have a city name, you can use Excelâ€™s Geocode feature to easily find latitude and longitude. If you have a street address, it is better to use VBA as Excel doesnâ€™t have a dedicated tool or function for such a purpose.

3. How to add latitude and longitude in Excel?

Ans: You can manually add the latitude and longitude of different places in Excel or use the Geocode feature to extract latitude and longitude from a city address.

## Geocoding in Excel: Knowledge Hub

<< Go Back toÂ Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Maruf Niaz

Md. Maruf Niaz, BSc, Textile Engineering, Bangladesh University of Textiles, has worked with the ExcelDemy project for 11 months. He works as an Excel and VBA Content Developer who provides easy solutions to Excel-related problems and regularly writes fantastic content articles. He has published almost 20 articles in ExcelDemy. As an Excel & VBA Content Developer for ExcelDemy, he offers solutions to complex issues. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF