If you want to find the distance between two points just use this formula and you will get the result in Km, just convert to miles if needed.

Point A: LAT1, LONG1 Point B: LAT2, LONG2

ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-lat2)) *COS(RADIANS(long1-long2)))*6371

Regards

Ensure that your latitude and longitude values are in the correct units (degrees for the second formula or radians for the first one) for the expression you are using, and the procedure should work as expected.

Can you provide sample data that does not work?

Alternatively, you can create a VBA function and then loop through your table.

Add this code to a Module in the VBA editor:

CopyPublic Function DistBetweenCoord(Lat1 As Double, Long1 As Double, Lat2 As Double, Long2 As Double)
    'Cell Formula
    'ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *6371

    With WorksheetFunction
        A = Cos(.Radians(90 - Lat1))
        B = Cos(.Radians(90 - Lat2))
        C = Sin(.Radians(90 - Lat1))
        D = Sin(.Radians(90 - Lat2))
        E = Cos(.Radians(Long1 - Long2))

        DistBetweenCoord = .Acos(A * B + C * D * E) * 6371
    End With
End Function

Now you can access this through code or in cell. Here is an example of in-cell:

Copy=DistBetweenCoord(C1,D1,C2,D2)

Here is how to loop through all possible combinations in another Sub. Output is in immediate window.

CopySub CalcAllDistances()
    With Worksheets("Sheet1")
        For i = 1 To 4
            For j = i To 4
                If i <> j Then
                    Debug.Print .Cells(i, 2) & " to " & .Cells(j, 2) & ": " & DistBetweenCoord(.Cells(i, 3), .Cells(i, 4), .Cells(j, 3), .Cells(j, 4))
                End If
            Next j
        Next i
    End With
End Sub

EDIT - To change output to Sheet2 try the following:

CopySub CalcAllDistances()
    Dim wks_Output As Worksheet
    Set wks_Output = Worksheets("Sheet2")

    Dim OutputRow As Long: OutputRow = 1

    With Worksheets("Sheet1")
        For i = 1 To 4
            For j = i To 4
                If i <> j Then
                    wks_Output.Cells(OutputRow, 1).Value = .Cells(i, 2) & " to " & .Cells(j, 2)
                    wks_Output.Cells(OutputRow, 2).Value = DistBetweenCoord(.Cells(i, 3), .Cells(i, 4), .Cells(j, 3), .Cells(j, 4))
                    OutputRow = OutputRow + 1
                End If
            Next j
        Next i
    End With
End Sub

Given a list of geographic coordinate pairs, you can implement the Haversine formula directly in Excel.

The simplest way to use this (or a more accurate, but I think it's not your case) formula consists into press Alt+F11 to open the VBA Editor, click Insert --> Module and then (copy and) paste e.g. the code kindly suggested by blah238.

Public Function getDistance(latitude1, longitude1, latitude2, longitude2)  
earth_radius = 6371  
Pi = 3.14159265  
deg2rad = Pi / 180  

dLat = deg2rad * (latitude2 - latitude1)  
dLon = deg2rad * (longitude2 - longitude1)  

a = Sin(dLat / 2) * Sin(dLat / 2) + Cos(deg2rad * latitude1) * Cos(deg2rad * latitude2) * Sin(dLon / 2) * Sin(dLon / 2)  
c = 2 * WorksheetFunction.Asin(Sqr(a))  

d = earth_radius * c  

getDistance = d  

End Function

There will be a new custom getDistance function (unit = kilometer) available in your spreadsheet which accepts four parameters, i.e. the two pairs of coordinates, as follow:

getDistance(latitude1, longitude1, latitude2, longitude2)

where latitude1, longitude1, latitude2, longitude2 should be replaced by their relative cell references.

The Google Distance Matrix API provides travel, distance and time for a matrix of origins and destinations. The API returns information based on the recommended route between start and end points, as calculated by the Google Maps API, and consists of rows containing duration and distance values for each pair of points.

Google Maps' set of APIs used to allow several free calls per day but they recently changed their billing structure, so you'll need to set up a billing account. Here are the new prices for the Distance Matrix API (unless you've managed to get a free trial). Basically $5 to $10 USD for every 1,000 calls to the API.

There are several specific examples here of how to call the API via a web browser or programmatically.

Use an Excel worksheet formula to create links

This will generates links to each trip's driving directions page in Google Maps (which includes distance) and will speed up the process (if you have at least basic knowledge of Excel).

1. Enter your sets of coordinates into an Excel worksheet, two coordinates sets per row in cells A to D, starting in row 2, in the order Origin Latitude, Origin Longitude, Destination Latitude, Destination Longitude. See image below.

2. Paste this formula into cell E2:
   =HYPERLINK("https://www.google.ca/maps/dir/'" & A2 & "," &B2&"'/'" & C2 & "," &D2&"'/@"&AVERAGE(A2,C2)&","&AVERAGE(B2,D2) &",12z/")

3. 'Fill down', or copy/paste the formula, so it's in all the rows with coordinates.

4. Click each link to load Google Maps for that trip, and manually make note of the driving distance for each coordinate set.
   (Note that each trip may have multiple options depending on which route you travelled.)

Click images to enlarge.

Result in browser:

With a decent Internet connection, I figure each one to take 10-15 seconds, so you're looking at around an hour total.

(I suggest breaking it up instead of trying to do them all at one. Better yet, pay a kid, or get multiple children and make a game out of it...) 😊

Function using Google Maps API

I remembered a function I had stashed away that appears to still work for free... but based on the new pricing guidelines, I wouldn't be surprised if it only worked for a few calls a day (for "testing purposes").

1. This one take the origin & destination locations in two cells (instead of four), but is more flexible with how you enter the locations. Example:

   

2. In Excel, paste this code into a new (standard) module. (Here's how.)

Public Function GetDistance(start As String, dest As String)
'Returns Google Maps driving distance between two points in kilometres
    Dim url As String, html As String, regEx As Object, matches As Object
    url = "http://maps.googleapis.com/maps/api/distancematrix/json?" & _
        "origins=" & Replace(start, " ", "+") & "&destinations=" & _
        Replace(dest, " ", "+")
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", url, False
        .Send: html = StrConv(.responseBody, vbUnicode)
        Set regEx = CreateObject("VBScript.RegExp")
        regEx.Pattern = """value"".*?([0-9]+)": regEx.Global = False
        Set matches = regEx.Execute(html)
        If matches.Count > 0 Then
            GetDistance = CDbl(Replace(matches(0).SubMatches(0), ".", _
                Application.International(xlListSeparator))) / 1000
        Else: GetDistance = -1: End If 'there was a problem.
    End With
End Function

3. With the example in the above image, you'd enter in cell C2:

   =GetDistance(A2,B2)
   

...to get the driving distance in kilometres.

Haversine Method

For the sake of completeness, I'll include the Haversine Method which calculates a "straight line" distance mathematically (with no API calls).

This is not driving distance. In my test of a relatively straight distance between towns, this method was different by about 15%, however, this is suitable (and faster) for other "non-driving related" purposes.

Excel:

Public Function Distance(lat1 As Double, lon1 As Double, _
        lat2 As Double, lon2 As Double) As Double
'Excel: Returns kilometers distance in a straight line (Haversine)
    On Error GoTo dErr
    Dim dist As Double, theta As Double: theta = lon1 - lon2
    dist = Math.Sin(deg2rad(lat1)) * Math.Sin(deg2rad(lat2)) + _
        Math.Cos(deg2rad(lat1)) * Math.Cos(deg2rad(lat2)) * _
        Math.Cos(deg2rad(theta))
    dist = rad2deg(WorksheetFunction.Acos(dist))
    Distance = dist * 60 * 1.1515 * 1.609344
    Exit Function
dErr:
    Distance = -1
End Function
Function deg2rad(ByVal deg As Double) As Double
    deg2rad = (deg * WorksheetFunction.Pi / 180#)
End Function
Function rad2deg(ByVal rad As Double) As Double
    rad2deg = rad / WorksheetFunction.Pi * 180#
End Function

MS Access:

Access is slightly different due to the lack of Excel's math functions, so I might as well include it:

Const pi = 3.14159265358979
Public Function Distance(lat1 As Double, lon1 As Double, lat2 As Double, lon2 As Double) As Double
'MS Access: Returns kilometres distance in a straight line  Haversine)
    Dim dist As Double, theta As Double: theta = lon1 - lon2
    dist = Sin(deg2rad(lat1)) * Sin(deg2rad(lat2)) + Cos(deg2rad(lat1)) * Cos(deg2rad(lat2)) * Cos(deg2rad(theta))
    Distance = rad2deg(ACos(dist)) * 60 * 1.1515 * 1.609344
End Function
Function ACos(N As Double) As Double: ACos = pi / 2 - ASin(N): End Function
Function deg2rad(ByVal deg As Double) As Double: deg2rad = (deg * pi / 180#): End Function
Function rad2deg(ByVal rad As Double) As Double: rad2deg = rad / pi * 180#: End Function
Public Function ASin(N As Double) As Double: ASin = 2 * Atn(N / (1 + Sqr(1 - (N * N)))): End Function

^{Spherical triangle solved by the Law of Haversines}