How to Calculate Distance Between Two Addresses in Excel (3 Ways) - ExcelDemy (2024)

Here is an overview of how the distance between two addresses can be found in Excel.

How to Calculate the Distance Between Two Addresses in Excel: 3 Ways

In our dataset, we have the Latitude and Longitude for Ohio and Alaska. We will measure the distance between them.

Method 1 – Using the Haversine Formula to Calculate the Distance Between Two Addresses

Although the Haversine formula will give an approximate result, it’s a pretty good approximation. The formula is given below.

S = Distance between two addresses

r = Radius of Earth

φ₁= Latitude of the first place

φ₂= Latitude of the second place

ℽ₁ = Longitude of the first place

ℽ₂ = Latitude of the second place

Steps:

• Insert the following formula in cell C8.

We put the radius of the earth in kilometers as 6,400 km so the formula returns kilometers.

φ₁ = C5

φ₂ = C6

ℽ₁ = D5

ℽ₂ = D6

• Press Enter.
• If you want to measure the distance in miles, use the following formula in cell C8instead:

• There is also Cosine version of this formula which is simpler to use:

Note:The formula uses the assumption of the Earth having a spherical shape, while it’s actually an ellipsoid. The Earth is roughly 22 kilometers “shorter” pole-to-pole than it is “wide,” which results in minor deviations.

Method 2 – Using VBA to Calculate the Distance Between Two Addresses in Excel

The VBA code uses public APIs to source information online and return the result in the table.

Steps:

• Go to Developer and select Visual Basic.

• Select Insert and Module to open the VBA Module.

• Insert the following code in the VBA Module. We are creating an User Defined Function to calculate the distance in miles.

Option ExplicitPublic Function DistanceInMiles(First_Location As String, _Final_Location As String, Target_Value As String)Dim Initial_Point As String, Ending_Point As String, _Distance_Unit As String, Setup_HTTP As Object, Output_Url As StringInitial_Point = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="Ending_Point = "&destinations="Distance_Unit = "&travelMode=driving&o=xml&key=" & Target_Value & "&distanceUnit=mi"Set Setup_HTTP = CreateObject("MSXML2.ServerXMLHTTP")Output_Url = Initial_Point & First_Location & Ending_Point & Final_Location & Distance_UnitSetup_HTTP.Open "GET", Output_Url, FalseSetup_HTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"Setup_HTTP.Send ("")DistanceInMiles = Round(Round(WorksheetFunction.FilterXML(Setup_HTTP.ResponseText, _"//TravelDistance"), 3), 0)End Function

• We made another User Defined Function to find the distance in kilometersby changing the distanceUnit to kilometers.

• You can see the API key in cell C8.

• Use the following formula in cell C8.

• Press the Enter button and you will see the distance between Ohio and Alaska in miles.

• Use the following formula to see the distance in kilometers:

Here is a new APIkey that you can use to find out distances. It might get changed after a while, so check online for newer APIs if needed.

Method 3 – Applying Excel CONCATENATE and SUBSTITUTE Functions for a Google Maps Link to Calculate the Distance Between Two Addresses

Steps:

• Use the following formula in C8.

• Press the Enter button and you will see the Google Map link in C8.

• Use this link in your browser and you will get the distance information about these two addresses.

If the distance or route cannot be determined by Google, you will not get a result. You might also receive distance information for roads or transport.

How to Calculate ZIP Code Distances in Excel

• Add theGeoDataAdd-inand necessary APIkey in Excel from the CDXGeoData Website.
• Select the File tab, go to Options, and select Add-ins.
• Click on the Gobutton beside theManagesection.
• Find the downloaded Add-in and clickOK.

• Insert the APIkey in theSettingswindow under theCDXGeodatatab which is added after installing theAdd-in.

• Insert some valid ZIPcodes and use the formula below to calculate the distance between them.

Note:TheAPIkey will be valid for24hours and the formula can be used100times for the free version.

Practice Section

We’ve provided a practice section and a template so you can experiment with calculating distances.

Download the Practice Workbook

Frequently Asked Questions

Can I calculate distances without using APIs?

It is difficult to compute distances effectively without the use of APIs since you would require access to large geographic datasets and algorithms for calculating distances based on addresses. APIs offer a more convenient and dependable solution.

Are there any Excel add-ins or plugins for calculating distances?

Third-party add-ins or plugins that interact with Excel and provide distance computation features may be available. They can streamline the process and eliminate the need for lengthy coding.

What precautions should I take when using APIs in Excel?

When using APIs, make sure to follow the API provider’s terms of service. Also, keep security in mind, especially when dealing with API keys. Keep your API keys in a secure location and avoid storing them in public files.

<< Go Back to Distance | Formula List | Learn Excel