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

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

Overview of Calculating Distance 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.

Dataset Overview for Calculating Distance


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

φ1 = Latitude of the first place

φ2 = Latitude of the second place

1 = Longitude of the first place

2 = Latitude of the second place

Steps:

  • Insert the following formula in cell C8.
=2*6400*ASIN(SQRT((SIN(RADIANS((C6-C5)/2)))^2+COS(RADIANS(C5))*COS(RADIANS(C6))*(SIN(RADIANS((D6-D5)/2)))^2))

Formula to Measure Distance Between Two Addresses

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

φ1 = C5

φ2 = C6

1 = D5

2 = D6

=2*3959*ASIN(SQRT((SIN(RADIANS((C6-C5)/2)))^2+COS(RADIANS(C5))*COS(RADIANS(C6))*(SIN(RADIANS((D6-D5)/2)))^2))

Formula to Measure Distance Between Two Addresses in Miles

  • There is also Cosine version of this formula which is simpler to use:
=ACOS(SIN(RADIANS(C5)) *SIN(RADIANS(C6)) +COS(RADIANS(C5)) *COS(RADIANS(C6)) *COS(RADIANS(D5-D6))) * EARTH_RADIUS

Cosine Version of Formula to Calculate Distance

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.

calculate distance between two addresses in excel

  • Insert the following code in the VBA Module. We are creating an User Defined Function to calculate the distance in miles.
Option Explicit
Public 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 String
Initial_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_Unit
Setup_HTTP.Open "GET", Output_Url, False
Setup_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

Code Explanation

  • We named our Function as DistanceInMiles. We also inserted 3 parameters: First_Location As String, Final_Location As String and Target_Value As String.
  • We declared Initial_Point, Ending_Point, Distance_Unit and Outout_Url as String; Setup_HTTP as Object.
  • We set Initial_Point as the starting of the Url link, Ending_Point as Destination and Distance_Unit to Miles.
  • We set the necessary parameters to create a relation between our VBA code and the API
  • We established our User Defined Function.
  • We made another User Defined Function to find the distance in kilometers by changing the distanceUnit to kilometers.

calculate distance between two addresses in excel

  • You can see the API key in cell C8.

  • Use the following formula in cell C8.

=DistanceInMiles(E5,E6,C8)

calculate distance between two addresses in excel

  • 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:

=DistanceInKM(E5,E6,C8)

calculate distance between two addresses in excel

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

AvAMQVCQZRHasj75zfHQyX5lHvfgjJKInhHyaY6cXJtXsulSdMC3r71LGZ2uTY3a

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.

=CONCATENATE("http://maps.google.co.uk/maps?f=d&source=s_d&saddr=",SUBSTITUTE(B5," ","+"),"&daddr=",SUBSTITUTE(B6," ","+"))

calculate distance between two addresses in excel

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

calculate distance between two addresses in excel

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 the GeoData Add-in and necessary API key in Excel from the CDXGeoData Website.
  • Select the File tab, go to Options, and select Add-ins.
  • Click on the Go button beside the Manage section.
  • Find the downloaded Add-in and click OK.

Browsing for the Add-in

  • Insert the API key in the Settings window under the CDXGeodata tab which is added after installing the Add-in.

Inserting GeoData API Key

  • Insert some valid ZIP codes and use the formula below to calculate the distance between them.
=GeoDistance(B5,C5)

Calculating Distance Based on ZIP Codes

Note: The API key will be valid for 24 hours and the formula can be used 100 times for the free version.


Practice Section

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

calculate distance between two addresses in excel


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

Get FREE Advanced Excel Exercises with Solutions!
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

8 Comments
  1. Isn’t Haversine Formula required an angle in rad unit?

    • Hello L.C., thank you for reaching out. The angles in the formula are used as input for the Sine and Cosine functions. So the conversion from degree to radian isn’t necessary. Whether I change the angle unit to radian or not, the value of the corresponding Sine or Cosine function will be the same as the angles remain the same.

  2. How can you run this script for 100s of comparison for two locations data in a sheet.

    Sheet 1 has 100+ locations (let’s say A,B,C,D… ) latitude and longitude and Sheet 2 has 100+ locations (say 1, 2, 3, 4 5…) with latitude and longitude.

    I need to find the nearby/identical locations to each other. For example how far is A from 1, 2, 3, 4, … and how far is B from 1,2,3,4, …. and so on … how would you accomplish that ?

    Use case: A sales guy can cover and talk to all customers in an area in one go.

    • Hello AC, thanks for reaching out. Here’s a solution to your problem.

      Here, I have some addresses in two sheets. The addresses in the first sheet are in the left side of the following image. The addresses of the second sheet can be found in the drop down list.

      And here is the formula to calculate the distance.
      =ACOS(SIN(RADIANS(VLOOKUP(F3,Sheet2!A2:C33,2,FALSE))) *SIN(RADIANS(VLOOKUP(E3,A2:C29,2,FALSE))) +COS(RADIANS(VLOOKUP(F3,Sheet2!A2:C33,2,FALSE))) *COS(RADIANS(VLOOKUP(E3,A2:C29,2,FALSE))) *COS(RADIANS(VLOOKUP(F3,Sheet2!A2:C33,3,FALSE)-VLOOKUP(E3,A2:C29,3,FALSE)))) *6371

  3. This is really great, thank you for posting this. Do you know of a way to modify the script to use the address string itself (rather than the Lat/Long coordinates)? Or do you know of an easy way to get Lat/Long coordinates from an address? Still researching this myself, but figured I’d ask! Thanks again.

    -Dan

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Dec 11, 2023 at 2:10 PM

      Hello DAN H

      Thanks for your nice words. Your appreciation means a lot to us. Thanks once again for sharing your requirements with such clarity.

      You wanted to get an Excel VBA Sub-procedures or Excel VBA User-defined functions to calculate the distance between two addresses using Address String (for example, New York, Alaska) instead of Latitudes and Longitudes values.

      I am delighted that I developed some Excel VBA User-defined functions to fulfil your requirements.

      OUTPUT OVERVIEW:

      Follow these steps:

      Step 1: Hover over Insert => Click on Module => paste the following code in the module.

      
      Public Function DistanceInKM(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 String
          Initial_Point = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
          Ending_Point = "&destinations="
          Distance_Unit = "&travelMode=driving&o=xml&key=" & Target_Value & "&distanceUnit=km"
          
          Set Setup_HTTP = CreateObject("MSXML2.ServerXMLHTTP")
          
          Output_Url = Initial_Point & First_Location & Ending_Point & Final_Location & Distance_Unit
          Setup_HTTP.Open "GET", Output_Url, False
          Setup_HTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
          Setup_HTTP.send ("")
          DistanceInKM = Round(Round(WorksheetFunction.FilterXML(Setup_HTTP.responseText, "//TravelDistance"), 3), 0)
      End Function
      
      Public 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 String
          Initial_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_Unit
          
          Setup_HTTP.Open "GET", Output_Url, False
          Setup_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
      
      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
      

      Step 2: Select cell C9 => Insert the following formula.

      =DistanceInMiles(Co_Ordinates(C6),Co_Ordinates(C7),C2)

      Step 3: Hit Enter to see the result in cell C9.

      Step 4: Select cell C10 => Insert the following formula.

      =DistanceInKM(Co_Ordinates(C6),Co_Ordinates(C7),C2)

      Step 5: Hit Enter to see the result in cell C10.

      Things to Keep in Mind: Ensure to check Microsoft XML, v3.0 from the References – VBAProject window.

      I am attaching the solution workbook. Hopefully, these user-defined functions will help you reach your goal. Good luck.

      DOWNLOAD WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

  4. Hello,
    I tried out the VBA & API (Bing) option and combined with a rate/mile finder tool.
    It works perfectly on my PC and in the UK from where I the price list received.
    How can I change cultural settings in VBA so it works also on non-UK clients? Because currently if it’s running on a non-UK client for the distance result it gives #VALUE error. Altough I disabled using system separators and set them manually Thank you!

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Apr 8, 2024 at 2:37 PM

      Hello MEDO

      Thanks for letting us know that the solution works perfectly on your end. However, you want to handle cultural settings in VBA, which also works on non-UK clients.

      Assuming your requirements are related to the decimal separator and thousand separator settings. Different regions worldwide use different conventions to represent decimals and thousands of separators.

      You can use two user-defined functions to replace these separators with periods and commas to overcome the situation. I have developed two VBA user-defined functions and slightly changed the existing sub-procedures.

      Calculating Distance in Miles:

      Option Explicit
      
      Public Function DistanceInMiles(First_Location As String, _
                                        Final_Location As String, _
                                        Target_Value As String) As Double
      
          Dim Initial_Point As String
          Dim Ending_Point As String
          Dim Distance_Unit As String
          Dim Setup_HTTP As Object
          Dim Output_Url As String
      
          Initial_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")
          
          First_Location = AddCommaInBetweenM(ReplaceCharsM(First_Location))
          Final_Location = AddCommaInBetweenM(ReplaceCharsM(Final_Location))
          
          Output_Url = Initial_Point & First_Location & Ending_Point & Final_Location & Distance_Unit
      
          Setup_HTTP.Open "GET", Output_Url, False
          Setup_HTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
          Setup_HTTP.Send ("")
      
          Dim distance As Double
          distance = Round(Round(WorksheetFunction.FilterXML(Setup_HTTP.ResponseText, "//TravelDistance"), 3), 0)
      
          DistanceInMiles = Format(distance, "#,##0.00")
      
      End Function
      
      Function ReplaceCharsM(strInput As String) As String
      
          Dim i As Long
          Dim strOutput As String
          Dim char As String
      
          For i = 1 To Len(strInput)
              char = Mid(strInput, i, 1)
              Select Case char
                  Case Application.International(xlDecimalSeparator)
                      strOutput = strOutput & "."
                  Case Application.International(xlThousandsSeparator)
                      strOutput = strOutput & ","
                  Case Else
                      strOutput = strOutput & char
              End Select
          Next i
      
          ReplaceCharsM = strOutput
      
      End Function
      
      Function AddCommaInBetweenM(inputString As String) As String
          
          Dim resultString As String
          Dim i As Integer
          
          resultString = Left(inputString, 1)
          
          For i = 2 To Len(inputString)
              If Mid(inputString, i, 1) = " " Then
                  resultString = Left(resultString, Len(resultString) - 1) & ","
              Else
                  resultString = resultString & Mid(inputString, i, 1)
              End If
          Next i
          
          AddCommaInBetweenM = resultString
          
      End Function

      Calculating Distance in KM:

      Option Explicit
      
      Public Function DistanceInKM(First_Location As String, Final_Location As String, _
                                   Target_Value As String) As String
      
          Dim Initial_Point As String
          Dim Ending_Point As String
          Dim Distance_Unit As String
          Dim Setup_HTTP As Object
          Dim Output_Url As String
      
          Initial_Point = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
          Ending_Point = "&destinations="
          Distance_Unit = "&travelMode=driving&o=xml&key=" & Target_Value & "&distanceUnit=km"
      
          Set Setup_HTTP = CreateObject("MSXML2.ServerXMLHTTP")
      
          First_Location = AddCommaInBetweenKM(ReplaceCharsKM(First_Location))
          Final_Location = AddCommaInBetweenKM(ReplaceCharsKM(Final_Location))
      
          Output_Url = Initial_Point & First_Location & Ending_Point & Final_Location & Distance_Unit
      
          Setup_HTTP.Open "GET", Output_Url, False
          Setup_HTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
          Setup_HTTP.Send ("")
      
          DistanceInKM = Round(Round(WorksheetFunction.FilterXML(Setup_HTTP.ResponseText, _
                                                                "//TravelDistance"), 3), 0)
      
      End Function
      
      Function ReplaceCharsKM(strInput As String) As String
      
          Dim i As Long
          Dim strOutput As String
          Dim char As String
      
          For i = 1 To Len(strInput)
              char = Mid(strInput, i, 1)
              Select Case char
                  Case Application.International(xlDecimalSeparator)
                      strOutput = strOutput & "."
                  Case Application.International(xlThousandsSeparator)
                      strOutput = strOutput & ","
                  Case Else
                      strOutput = strOutput & char
              End Select
          Next i
      
          ReplaceCharsKM = strOutput
      
      End Function
      
      Function AddCommaInBetweenKM(inputString As String) As String
          
          Dim resultString As String
          Dim i As Integer
          
          resultString = Left(inputString, 1)
          
          For i = 2 To Len(inputString)
              If Mid(inputString, i, 1) = " " Then
                  resultString = Left(resultString, Len(resultString) - 1) & ","
              Else
                  resultString = resultString & Mid(inputString, i, 1)
              End If
          Next i
          
          AddCommaInBetweenKM = resultString
          
      End Function

      Hopefully, the VBA code will help you with your problem; good luck.

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo